find cell with matching text, then reference cell in same row

[nt8jbwu02]

I have a main sheet and many additional sheets with peoples
names' (each sheet is named for a person - including a command and
space). The main sheet is named 'Master Inventory'

On the main sheet, there is a row for each person. Column A has the
names of all the named sheets.

Each person named sheet has a cell, A6, with it's sheet name.

I would like a specific cell in each named sheet to reference the cell
in 'Master Inventory'!<row with the name in cell A6>, <column AK>

example:

Master Inventory cell A12 has the name 'Smith, Joe'. I would like a
cell in sheet 'Smith, Joe' to be the contents of master inventory cell
AK12. I would like to use the same formula on each sheet.

How can I do this?

Thanks

 

[Debra Dalgleish]

On each person's sheet use a formula that looks for their name on the
Master Inventory sheet, and offsets from that cell:

=OFFSET('Master Inventory'!$A$1,
MATCH($A$6,'Master Inventory'!$A$1:$A$200,0)-1,36)

 

[Pete_UK]

If you had another cell in each of your subsidiary sheets (eg A1)
which contained the name of the sheet, then you could us MATCH to find
the row that the sheet name appears on in your Master Inventory sheet,
and use that in conjunction with an INDIRECT function. Both are
described in Excel Help (it's getting late here!)

Hope this helps.

Pete