TAB finder formula

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

I am trying to consolidate 20 similar spreadsheets
together, I would like to be able to extract either each
individual speadsheet with a formula that would get the
appropriate TAB information. I would be able to re-work
the information I am storing in my individual spreadsheet
in more readable manner as presented now. I can't find a
formula that would do that, I know how to use Lookup, and
creat pivot table but this functions only works on cells
and Tables.

Does anyone have find a way so I can find
 
Nick,

Enter the tab name that you want to pull your information from in cell A1.

Then you can use the formula:

=INDIRECT("'" & $A$1 & "'!A1)

to pull information from cell A1 of the sheet whose name is in cell A1 of
the current sheet.

If you want to pull a lot of data into the same cells(B1 into B1, G4 into
G4), use the formula:
=INDIRECT("'" & $A$1 & "'!" & ADDRESS(ROW(),COLUMN()))

Note that the tab name needs to be entered exactly.

HTH,
Bernie
MS Excel MVP
 
Back
Top