Prev: Normalize/concatenate
Next: using indirect to return a named range?
From: Pradhan on 9 Mar 2010 16:40
Hi, and thank you for your help.
I have run across this need many times...is there any way to use to contents
of one cell, to construct a formula in another cell?

Here is my particular requirement. I have worksheets named January,
February, March...etc.

On a separate Summary Worksheet, I reference the same cells on each of those
sheets.

For example, I may have in 3 adjacent cells, =January!A1 =February!A1
=March!A1 and then in the next row:
=January!A7 =February!A7 =March!A7 etc.

It would, obviously, be much easier to "build a formula" by creating a row
with the months name and then reference that row in another formula. Is this
possible? Or is there another way to accomplish this?

Thanks for your help.
Pradhan
From: Max on 9 Mar 2010 17:54
The key is to use INDIRECT
In your summary sheet,
List the sheetnames in B1 across, eg: January, ...
List the cell references in A2 down, eg: A1, A7, etc
Put in B2: =INDIRECT("'"&B$1&"'!"&$A2)
Copy across/fill down to populate
voila? hit the YES below
--
Max
Singapore
---
"Pradhan" wrote:
> Hi, and thank you for your help.
> I have run across this need many times...is there any way to use to contents
> of one cell, to construct a formula in another cell?
>
> Here is my particular requirement. I have worksheets named January,
> February, March...etc.
>
> On a separate Summary Worksheet, I reference the same cells on each of those
> sheets.
>
> For example, I may have in 3 adjacent cells, =January!A1 =February!A1
> =March!A1 and then in the next row:
> =January!A7 =February!A7 =March!A7 etc.
>
> It would, obviously, be much easier to "build a formula" by creating a row
> with the months name and then reference that row in another formula. Is this
> possible? Or is there another way to accomplish this?
>
> Thanks for your help.
> Pradhan
 | 
Pages: 1
Prev: Normalize/concatenate
Next: using indirect to return a named range?