• 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.

Return named data range in a cell

U

ultros1234

New Member
Hi folks --


I'm trying my hand at making a simple dashboard, but I've run up against a frustrating problem. I'll tell you the bottom line first, and then the details.


I want to get a formula to return a reference to a named data range in a cell, but the formula constantly returns a digit rather than a data range. I have tried changing the number format on the cell, but nothing seems to help.


For example, the formula I want to make is something like this:

Cell A8 is the output cell for a combo box, which selects one of my four data sets. It returns a value of 1, 2, 3, or 4.

Cell A9 has a series of nested if statements to return the four data ranges:

=IF(A8=1,Appointment_data,IF(A8=2,SHIP_data,IF(A8=3,Advice_data,Specialty_data)))

But cell a9 just returns the number 3. The problem isn't with the formula. I've tested with something simple like

=Appointment_data

and still all I get is a digit. Interestingly, if I do this simple formula in successive cells in the same column, I get an increasing series of digits (i.e., it returns 3, then 4, then 5...).


Can I not have a formula return the name of a data range? If not, what's the best solution for having a combo box control which data that populates my graphs and tables? I'm sure there's a more efficient way for me to do this, but I would like to understand what's going on.


Thanks!
 
Hi ultros1234. Welcome to the forum. I'm newish here too, so I don't know how much my welcome means - but that's what the Excel ninjas do!


I don't think there is a way to return the name of a range, but I don't think you need that. What you want to do is have a datarange that is dynamic based on your dropdown list. So instead of using the IF formula, you would use INDEX or VLOOKUP. Then you would point your chart series to this dataset.


I'm looking for a good tutorial on this now. I'll post back in about a minute.
 
This:

http://blog.contextures.com/archives/2009/05/03/select-excel-chart-dates-from-a-drop-down-list/


and this:

http://www.squidoo.com/create-a-dynamic-chart-using-a-combo-box
 
Thanks Jordan! Funny that you can't keep a data range name in a cell, but the links you sent did the trick. Also, the "choose" function is much less clunky than a series of nested if statements.


You rock.
 
Hi, ultros1234!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Return%20named%20data%20range%20in%20a%20cell%20%28for%20ultros1234%20at%20chandoo.org%29.xlsm


I tried to reproduce your case, named range in column C is for combo box ActiveX control and named ranges in columns D:G are for your data ranges. A8 cell is the linked cell of combo box and A9 is your formula... but as an array formula, so please enter if with Ctrl-Shift-Enter instead of Enter. Cells A11:A14 represent your same simple example for the four ranges, but as array formulas too.


You'd be saying, but I still retrieve one (the first value)!


Well, not exactly.


Select any cell A9, A11:A14, go to the edit formula bar, press F2, select the whole formula and press F9 to evaluate the selected expression (the method is valid for any portion of a cell formula which represent a value).


Please note that in every case of this 1+4 array formulas, you get the whole array in one single cell.


Just advise if any issue.


Regards!
 
Hi ,


Your combobox and IF statement can be used , provided you define a named range , say Chart_Series , and in the Refers To box , enter the formula :


=INDIRECT(Sheet1!$A$9)


where A9 has the formula :


=IF(A8="1","Appointment_data",IF(A8="2","SHIP_data",IF(A8="3","Advice_data","Speciality_data")))


In your chart , use the name Chart_Series.


Narayan
 
You must log in or register to reply here.
Back
Top