Indirect & V/Hlookup

  • Thread starter Thread starter DeanH
  • Start date Start date
D

DeanH

Excel 2003 on XP.
I have a workbook containing over 200 worksheets. Each worksheet is a
country and for each country I have data which is a list of speeds (one per
row) and along side (next column) I have a proce per speed.
Obviously each country can have a diferent range of speeds.

I am attempting to consolidate these worksheets on to one sheet, with the
Countrys going down and the speeds going across (this range of speeds is a
complete range that covers all speeds included in the country sheets).

I am attempting to use Indirect to change worksheet as per country (column
A) and lookup for the speed from column B to AD (row 6) within each country
and extract the price for that relevant speed in that country.

Make sense?

Many thanks in advance
DeanH
 
An HLookup where the value to lookup is in cell B1 and the lookup sheet's
name is in A1 might look something like this:

=HLOOKUP(B1,INDIRECT(A1&"!B6:AD20"),2)

I'm not sure this is what you're looking for but maybe it'll help.

--
Jim
| Excel 2003 on XP.
| I have a workbook containing over 200 worksheets. Each worksheet is a
| country and for each country I have data which is a list of speeds (one
per
| row) and along side (next column) I have a proce per speed.
| Obviously each country can have a diferent range of speeds.
|
| I am attempting to consolidate these worksheets on to one sheet, with the
| Countrys going down and the speeds going across (this range of speeds is a
| complete range that covers all speeds included in the country sheets).
|
| I am attempting to use Indirect to change worksheet as per country (column
| A) and lookup for the speed from column B to AD (row 6) within each
country
| and extract the price for that relevant speed in that country.
|
| Make sense?
|
| Many thanks in advance
| DeanH
|
 
Back
Top