Need to use text from field in a formula (Excel) 1

[asanchez4]

This would save me a ton of time -- please help.

I have a spreadsheet with several worksheets. The name of each work sheet is in a cell in the primary worksheet. For example

A1 has the text value "sheet1" (with no quotes). B1 has the value "sheet2" and so on.

In cell A2 I need to reference a field in sheet1. In B2 I need to reference a field in sheet2 and so on.

So, in cells A2, B2, C2, .... etc, I would like to write a formula that references the correct sheet without hardcoding it. For example, in A2 I would like the equivalent of sheet1!A1 but without having to type out "sheet1", rather look at the name of the worksheet in A1.

I have tried a ton of things but the only thing that I get is actually Sheet1!A1 showing up in the cell.

Any suggestions?

 

[AnotherHiggins]

Check out the INDIRECT function.

There's a problem with your example data: You say that Sheet1, cell A1 will say "Sheet1" in it. Then why would you want a formula to return the text in A1, it would just read 'Sheet1'.

Instead, let's say that row 3 contains your sheet names (A3 = Sheet1; B3 = Sheet2; C3 = Sheet3), and you want to return the values from cell A1 of each of the sheets. Then your formula in A2 would look like this:
[COLOR=blue white]=INDIRECT(A3&"!A1")[/color]
In B2:
[COLOR=blue white]=INDIRECT(B3&"!A1")[/color]
In C2:
[COLOR=blue white]=INDIRECT(C3&"!A1")[/color]

[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[asanchez4]

That was exactly what I was looking for -- Thanks.

 

[AnotherHiggins]

Glad to help.


[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.