Searching for a sheet and using data from the matching sheet.

[meacho]

hello everyone, the problem i have is that i want to use data from specific work sheets in another generic sheet, i have a list of names in the generic sheet and a lot of sheets titled by peoples names, i want excel to search for a sheet whose name corrisponds to the name in a cell on the generic sheet and copy from that individuals sheet a specific cell and put it in the generic sheet.


it sounds a bit wordyand i've probably explained it badly,

can anyone help?

Matt

[NBVC]

The Indirect() function combined with a Lookup function should do that for you....

You need to provide a sample or give details if you want help with a formula.

[meacho]

how can the lookup function search sheet names? i've done some googling and haven't turned much up.

[meacho]

if i use =indirect(A1) and in cell A1 put B1 this puts what ever is in B1, which in this case is the name of another work sheet, in that cell. Now i want to use that indirect reference to call a value from this sheet i ahve tried

='(indirect(A1)!)'B5

but this doen't work

any ideas?

[NBVC]

Try:

=INDIRECT("'"&A1&"'!B5")

P.S. I thought your were looking up tables in those sheets, that's why I mentioned Lookups.

[meacho]

no luck it comes up with a #ref error.

if i just put = indirect(A1) the i get the name of the sheet i want to get data from why can't it find the sheet the names are the same?

[NBVC]

It should work...

In order to get just the sheetname returned I think you need =INDIRECT("A1") not INDIRECT(A1)

What is the name of the sheet you're referencing...is there something irregular about the name?

[svaiskau]

If INDIRECT("'" & A1 & "'!B5") does not return the value of B5 from the sheetname contained in A1, then there are only a couple possibilities:

1. The value in A1 does not exactly match the sheet's name.
2. The INDIRECT call is incorrect. Please notice that there both double quotes and single quotes in the call: INDIRECT("'" & A1 & "'!B5")

[meacho]

yep it was a problem with the name "M O'callaghan" doest work it doen't like the ' oh well


i have another question, i've written the formula

=INDIRECT(ROW(A3)&"'Utilisation'!11")

to get data from cell c3 in the utilisation sheet then when i drag the formula over the column it should get data from C3 D3 E3 F3 etc its not working at the moment, why?

i copied the layout of a previous formula which did the same but copied data from a column to a row so i used COLUMN(A1) instead of row.

Thanks for all the help!

MAtt

[NBVC]

=INDIRECT(ROW(A3)&"'Utilisation'!11")

I am not sure what you're after... what is the Row(A3) and the 11 supposed to achieve?

[meacho]

all the values i want are in row 11, i was trying to do a transpose trype thing but by linking the cells so if i make changes in the future the numbers will match

[NBVC]

I think it might be better if you actually submit an example sheet showing what you have and what you desire.