Indirect & V/Hlookup

[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

 

[Jim Rech]

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
|

 

[Herbert Seidenberg]

Use Pivot Table.
No formulas needed:
http://www.savefile.com/files/1592318