VLOOKUP type function to pull data from multiple sheets

  • Thread starter Thread starter Dayan
  • Start date Start date
D

Dayan

Want to know if there is a function that will let me pull
data seletively from from several differencet work sheets,
much the same way as VLOOKUP / HLOOKUP work on a single
spreadsheet.

Any help will be much appreciated.

To illustrate

- I have a work book comprised of 10 worksheets that have
been formatted identicaly.

- I am looking for formula that will let me pull the
contents of cell A4 on any given sheet I choose

- Then have the functionality that will let me pull the
contents from cell A4 in a different sheet

In others words - I am try to do the same thing that
VLOOKUP does on a single sheet over multiple sheets
 
Want to know if there is a function that will let me pull
data seletively from from several differencet work sheets,
much the same way as VLOOKUP / HLOOKUP work on a single
spreadsheet.

Any help will be much appreciated.

To illustrate

- I have a work book comprised of 10 worksheets that have
been formatted identicaly.

- I am looking for formula that will let me pull the
contents of cell A4 on any given sheet I choose

- Then have the functionality that will let me pull the
contents from cell A4 in a different sheet

In others words - I am try to do the same thing that
VLOOKUP does on a single sheet over multiple sheets
Click to expand...

Looks like you could use INDIERCT. If the worksheet name corresponded to the
lookup value, then try

=INDIRECT("'"&WorhsheetNameEntry&"'!A4")

or

=INDIRECT("'"&WorhsheetNameEntry&"'!"&CELL("Address",A4))
 
One more possibility, if the sheet name has apostrophes they'll have to be
doubled up, so this modification should work for all situations:

=INDIRECT("'"&SUBSTITUTE(WorkhsheetNameEntry,"'","''")&"'!A1")

Tim
 
Back
Top