Indirect Match or INdex match

[Dan88]

Hi all, i need some pointers on a formula im trying to use. I have a workbook with multiple tabs and a summary tab. on the summary tab from A2 - A10, i have the corresponding sheet names.

on B2 - I 2 i am using =INDIRECT("'"&$A2&"'!"&"O3") to pull data from the referenced sheet name in A2.

My issue is if i moved the data around in the tabs, i have to readjust the O3 range in =INDIRECT("'"&$A2&"'!"&"O3") manually to match the new columns. Is there a way to have add a match header to this Indirect formula so whenever i move columns in the tabs, i dont have to readjust the summary sheet? I can't seem to string a workable formula to get this to achieve what i need.

ANy help or pointers will be greatly appreciated.
thx

 

[Dan88]

Sorry... *bump****

so sorry

 

[pgc01]

Hi

Let's say that the value is in row 3 in the column with header "APR North".
The header will be somewhere in row 1 in columns A:Z

Try:

=INDEX(INDIRECT("'"&$A2&"'!"&"A:Z"),3,MATCH("APR North",INDIRECT("'"&$A2&"'!"&"A1:Z1"),0))

 

[Dan88]

PGC, this worked. Thanks very much.

 

[pgc01]

You're welcome. Thanks for the feedback.