Excel - Geting a flexible Range in formula!

[aloittelija]

If I have a formula =VLOOKUP(B7,'Base Data - Apr'!$B$27:$F$45,2,FALSE) and would like to replace the range ‘Base Data – Apr’! for example with CONCATENATE("'Base Data -",A1,"'!&quot which would give me a possibility to change the tab into which the range is looking. This does not seem to work though. Any ideas how I would manage the same result without making a macro?

 

[SkipVought]

Hi,

Seems to me that you would want to use the INDIRECT function. Skip,

 

[DaleWatson123321]

aloittelija,

I've developed a simple example for you, using the INDIRECT function, and the use of range names.

You would simply assign a Range Name to each of your tables on each separate sheet. In the example you used, you would assign the range name "Apr" to B27:F45 on the BaseData - Apr sheet. Then repeat this for each of the other sheets.

Then, with the following formula, all you need to do, is type in the range name ("Apr" for example) into cell A1.

=VLOOKUP(B7,INDIRECT(A1),2,FALSE)

Hope this helps.

If you would like, I can email you the example file. Just email me and I'll send the file via return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca