Using Cell Data in a forumla

[DataJunkie]

Hi Guys,

I'm trying to use cell data as a reference in a formula. I want to be able to type the name of a tab into a cell and use that to reference the data on that tab, in a forumla.

EG:

CELL A1: TEST

TAB NAME: TEST

so far i have found using INDIRECT might help, but cant find the correct formatting for the forumla. I have tried many variants on the below

=INDIRECT(["&a&1"]!a1)

to try to get it to just print the value in A1 but it keeps returning an error.

Help,


Thanks

DJ

[Richard Buttrey]

Hi,

Try

Please Login or Register  to view this content.

Which will return the value 'Test' if the word test is in A1
More typically I expect you'd want to access the value of some other cell on the sheet test, in which case

Please Login or Register  to view this content.

[DataJunkie]

That's awesome Richard. Thanks works a treat.

I've tried to add it into this formula, but am getting an error... Do you have time to look at this for me?

=IF(ISNA(VLOOKUP(A3,(INDIRECT("'"&c3&"'!"))$A$6:$F$2560,6,FALSE))=TRUE,"Employee not found",(VLOOKUP(A3,(INDIRECT("'"&c3&"'!"))$A$6:$F$2560,6,FALSE)))


The formula VLOOKUP's a list of names against an array of data on the appropriately named sheet (C3), and returns column 6 in the lookup array....


Thanks muchly

[Richard Buttrey]

Hi,

Try

Please Login or Register  to view this content.