• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Range with DropDown lists and Array Functions

M

Mahir Yagimli

Member
HI Everyone,

Recently I tried to learn how to create dynamic ranges with data verifying.

Somehow it did not work and it is too far outside of my skill range.

Here is what I tried to do:


- Create a dynamic range from a given list of customers. Only the unique names shall be shown in a drop down list. This Dropdown list should have been in cell A3.

- After the Customer is selected the dropdown list in B3 shall only show the Orders that belongs to the selected customer.

- In D3 and E3 I would use a simple “SUMIFS” Function.

Now what I did:

- I found a clip in youtube (excel is fun) explaining these excel tricks and I tried to apply it. On the Datasheet, which has all the customer names I used the “Frequency” Function to count the unique Customer Names. Then I was trying to list them Name by Name with an array function.

I know that I can do the required actions via pivot table easily, but I was trying to learn how to use formulas, so that I only have to update the “Data” sheet and everything else will be updated.

Can anyone help me? I am lost in Excel.

For you Reference I have uploaded the table.

I hope you can help me and also give me some tipps, how I can improve and where to look to learn more about Dynamic Ranges and Array Functions.

I know I wont be an Excel-expert in this life, but since I know Chandoo's website I have much more respect for all of you experts.

Have a great day!
 

Attachments

  • Dynamic range test.xlsx
    30.6 KB · Views: 8
Take a look at the INDEX formulas used here.

EDIT: Bah! SM beat me again. :p
 

Attachments

  • Order List.xlsx
    18.5 KB · Views: 15
@Somendra Misra: Actually I thought you might write something, cause you seem to be very fit in regards of array formulas. Thank you. Your version works fantastic.

@Luke M: Thank you Luke. Your version works fantastic, too.

Would it be too much to ask how you've done that and is there a way that I can learn this too?
 
I'll explain mine, at least. :)

First formula:
=IFERROR(INDEX(Data!A:A,(1/MIN(IF(ISNA(MATCH(Data!$A$2:$A$100,G$1:G1,0)),ROW(Data!$A$2:$A$100))))^-1),0)
First we need to build the customer list. To build this list, we need to know what values in our data range have not already been listed. To check for that, we use the MATCH function. If a value isn't found via MATCH, a #N/A error is thrown. Since we don't want a MATCH, this is what we want. We then have the IF function give us the ROW number for each value that wasn't listed in our list.
So, in the first cell, when no names are listed, we get an array of all the numbers.
In the 2nd cell, after Customer A is listed, we get an array of all the numbers except for Customer A. This repeats for other customers.
Now, we can only return one name at a time in the cell, so we plug our array of numbers into MIN, which gives us the smallest number.
Next, need to setup an error trap. If all customers are listed, then no numbers go into the array. In that case, our MIN function would return 0. But, if you tell INDEX to look at row 0, it uses the first row by default (annoying, and wrong in this case). So, how to catch this? We perform a math operation of (1/x)^-1. This math is the same as X, but with one neat trick. IF x = 0, this will cause the DIV/0 error, and we can trap it!
With that resolved, we take our number, if there is one, feed it into the INDEX, which lets us choose which row from col A to get data from. If an error was thrown (the DIV/0 error), create a 0.
We define the dynamic range with:
=$G$2:INDEX($G:$G,COUNTA($G:$G)-COUNT($G:$G))
The COUNTA - COUNT bit lets us find text only cells. COUNTA gives all the cells with contents, and COUNT gives only numbers. Since we caused all our error cells to produce 0's, we've now limited the range correctly.

For the 2nd formula, see this article I wrote:
http://chandoo.org/wp/2011/11/18/formula-forensics-003/
which describes how to create a non-blank filled list of items for specified criteria.

To define the Order List, similar formula as before, but now we only need to worry about COUNT, since all the Orders are number. We then add 1 since we are starting at row 2.
=$H$2:INDEX($H:$H,COUNT($H:$H)+1)
 
You must log in or register to reply here.
Back
Top