lookups and ranges

[Dillonstar]

I have a page of vlookups that look at data on other worksheets.

At the moment, if I want to change the worksheet that contains the
necessary range, I have to replace the name of the worksheet.

eg

=IF(ISERROR(HLOOKUP($B$1,'Business
Multi'!$D$1:$EP$3000,HZ16,FALSE)),"",(HLOOKUP($B$1,'Business
Multi'!$D$1:$EP$3000,HZ16,FALSE)))

So if I want to look at the worksheet Business NonMulti, I use the
find/replace wizard.

Ideally, I'd like to set up named ranges within each worksheet and use
a drop-down list (validation) to choose which range the formula looks
at, but have had no success sofar.

The reference to HZ16 is basically the row ID of the required data.

Thanks

Steve

 

[Dillonstar]

forget that - I've used the INDIRECT function, looking at the drop-down
cell, which works.

Thanks anyway