Lookup name changes from a drop down?

**DButtgen** · 05-15-2009, 06:20 PM

I have named six different sets of data with six different names. I have a drop down box that lists these six different names. When I select one of the names from the drop down box, I would like the lookup I have off of this to change to what ever set of data the drop down selection is referring to. Example: I have my first data set named "Data_A", next "Data_B", etc... When my drop down selects "Data_E", i would like my Hlookup equation to change to HLookUp(a1,Data_E,3,false). Is this possible, or am I doing something wrong?

**JBeaucaire** · 05-15-2009, 06:27 PM

That would requires an INDIRECT() reference to the cell you are making that selection in. If the lookup value is in A1 and the Data range choice (drop box) is in A2, the formula would be:

=HLOOKUP(A1,INDIRECT(A2),3,FALSE)

**DButtgen** · 05-15-2009, 06:43 PM

Thank you! Now, another question... If the metrics from the drop down change from a percentage on one, to a dollar on another, is there anyway to carry the formatting over?

**JBeaucaire** · 05-15-2009, 06:48 PM

Formulas take on the formatting result of the cell they are in, which is usually a good thing. You're formulas are about to balloon a LOT if you want to build conditional formatting INTO the formula itself.

**leto2** · 05-15-2009, 06:53 PM

If you only have 2 or three formatting options, you could conditionally format the cells based on what is in the dropdown (by using "Formula Is" in Conditional Formatting). The cells wouldn't really take on the formatting of the source cells, but you would format them identically based on what is selected. It would have the same effect.