Concatenating Cells to give a Sheet Reference to a formula..

[Puma]

Hello all.

Is it possible to write a formula which retrieves a value from one of a number of avaliable sheets based on the values of different cells???

Ie. This formula:

='sheet1'!AC38

Becomes something like:

='B2 & " " & C2'!AC38

I have two variables, A and B plotted horizontally and vertically and Bassically, I need to populate a grid with values from a whole host of sheets in the same workbook.

I could reference them by hand but, it would take ages and i may need to change some of the B titles (and the results sheets) at some time in the future so, if i can write it into a formula that would be better.

Any thoughts gratefully recieved.

Cheers.

Dave

 

[bigbob]

Re: Concatenating Cells to give a Sheet Reference to a formu

Hi
Look at the ADDRESS funnction in HELP and see if it is what you want
Bob

 

[ken2step]

Re: Concatenating Cells to give a Sheet Reference to a formu

Cell A1=G3
Cell A2= :
Cell A3=G5
Cell G3=5
Cell G4=5
Cell G5=5

Cell A4=sum(indirect(A1&A2&A3))
Formula result in Cell A4=15

In your sample above:

=indirect("'"&B2&C2"'!"&AC38
note there is NO space in sheet1

 

[Puma]

Re: Concatenating Cells to give a Sheet Reference to a formu

Cheers.

That's what i was after. I might need to use a newer version of Excel though as the formula didn't work (excel 97). Looks like what i was after though so, thanks.

Dave

 

[ken2step]

Re: Concatenating Cells to give a Sheet Reference to a formu

Are you saying the Sum example did not work or Example for your data did not work?

 

[ken2step]

Are you saying the Sum example did not work or Example for your data did not work?

Test reply

 

[Puma]

Re: Concatenating Cells to give a Sheet Reference to a formu

The '=indirect("'"&B2&C2"'!"&AC38 ' bit causes excel to moan but, i haven't definitly got the first section working yet so, it's probably just a teathing problem...

If i can't get it working relativly soon, i'll shout for help )

Thanks for your help.

Dave