Indirect Dynamic Range problem

[coatsy35]

I have read, after discovering that a formula I am trying to use for a project wouldn't work, that INDIRECT does not function with dynamic ranges. Is there a way around this?

My formula is below, it works absolutely perfect when a normal named range is defined. It just doesn't work when I use a dynamic range, which I need to use due to my project's combo boxes being full of empty lines if I don't make the range dynamic. (Or perhaps someone has a another way to remove blank lines form a ActiveX combobox when using a named range as the ListFillRange!)

Please Login or Register  to view this content.

B4 being the linked cell of an ActiveX combobox. The choices in the combobox determine which named range the vlookup uses to search for data.

Any help greatly recieved.

[Tony Valko]

How many dynamic ranges are there?

You can use a combination of CHOOSE/MATCH.

[coatsy35]

How many dynamic ranges are there?

You can use a combination of CHOOSE/MATCH.

About 12 dynamic ranges in total in the workbook, one is listed in the combobox, depending on what another combobox's value is. How would i write my formula without using INDIRECT?


Regards

[Tony Valko]

Something like this...

List the range names in a range of cells, say A1:A12.

Then the formula would be:

=VLOOKUP(A32,CHOOSE(MATCH(B4,A1:A12,0),Rng1,Rng2,Rng3,Rng4,...Rng12),2,0)

Fill in all 12 range names. The range names in A1:A12 must be in the same order as they are coded into the formula Rng1,Rng2,Rng3,Rng4, etc.