Link data from particular sheet based on cell reference

[moily]

Thank you in advance for any help you can offer! Just a quick note that I was
hoping to do this via formula.

Is there a way to bring back data from a particular worksheet depending on
what is in a particular cell?

For example I have several identical ranges on separate tabs, the tabs are
named (Smith, Jones, etc)
Each range is the same on each sheet: b17..d62 which includes Number, Start
Date, End Date, Type

In the input sheet if I put in cell d12 the name Smith I want it to copy
over any cells in the Smith tab, range b17 to d62 that are filled in .

I saw that there was some help earlier for a vlookup similar to this but I
don't have anything to match. I just need to bring back a particular cell
regardless of any criteria.

Many thanks!
Ann

 

[macropod]

Hi moily,

Check out the INDIRECT function.

For example, if you have a sheetname in A1 and a cell reference in B1, you could use:
=INDIRECT(A1&"!"&B1)
to retrieve the value from B1 in the corresponding worksheet.
Alternatively, with just the sheetname in A1 and:
=INDIRECT(A1&"!"&CELL("address"))
you could retrieve the value from whatever the current cell address is in the other sheet.

You can combine this with other functions, like OFFSET etc. to get data a certain number of cells away from the cell in which you
insert the formula.