Using Worksheet reference with worksheet name from another c

[dngidotcom]

Hi. Hopefully this is simple, just not simple for me to figure out:

3 Worksheets: ws1, ws2, ws3 (acutually hundreds, but for simplicity...)

ws1 has a table that pulls values from the various other worksheets

ws1:
A1="ws2" B1=ws2!A1 < gets first cell value from ws2
A2="ws3" B2=ws3!A1 < same from worksheet ws3
...
A100="ws100" B100=ws100!A1

Basically I just need a formula in column B of ws1 that gets the worksheet names from the text in column A, the "wsX!" part, not the cell reference. The part after the "!" is all relative and copies fine. It is just the part before the "!", that tells the worksheet name.

I tried things like:
CONCATENATE(B1,"!","A1")
but that just returns text.

[Aladin Akyurek]

On 2002-02-17 10:43, dngidotcom wrote:

ws1:
A1="ws2" B1=ws2!A1 < gets first cell value from ws2
A2="ws3" B2=ws3!A1 < same from worksheet ws3
...
A100="ws100" B100=ws100!A1

Basically I just need a formula in column B of ws1 that gets the worksheet names from the text in column A, the "wsX!" part, not the cell reference. The part after the "!" is all relative and copies fine. It is just the part before the "!", that tells the worksheet name.

I tried things like:
CONCATENATE(B1,"!","A1")
but that just returns text.

Try:

=INDIRECT(A1&"!A1")

Drag down as far as needed.

[athertm]

OK here goes. If your sheet name is in column A (starting at A1,A2 etc..) and the set of data you are after was in cell b3 on each of these sheets this is how its done.

1) =INDIRECT(ADDRESS(3,2,1,1,A1),TRUE) "the ADDRESS bit returns the full cell reference at row 3 column 2 (b3) using the sheet name at cell A1. INDIRECT uses this reference to get the value from the sheet"

Hope it works.

[dngidotcom]

Followup:

Thanks for the help above. These are the equations I used:

=INDIRECT("'["&filename!$C$2&"(H).xls]Sheet2'!$A$1:$X$33")
=INDIRECT("'["&filename!$C$2&"(G).xls]Sheet2'!$A$1:$X$33")
...
=INDIRECT("'["&filename!$C$2&"(B).xls]Sheet2'!$A$1:$X$33")
=INDIRECT("'["&filename!$C$2&"(A).xls]Sheet2'!$A$1:$X$33")

and

='[PE-75-1(H).xls]Sheet2'!$A$1:$X$33
='[PE-75-1(G).xls]Sheet2'!$A$1:$X$33
...
='[PE-75-1(B).xls]Sheet2'!$A$1:$X$33
='[PE-75-1(A).xls]Sheet2'!$A$1:$X$33

You'll notice several fils with A-H being the difference in the file names. However, A-H are also the worksheet names where these equations are used. For example, on Worksheet A, the equation is:
='[PE-75-1(A).xls]Sheet2'!$A$1:$X$33

Is there any way to get that "A", the name of that Worksheet, into that equation with some reference equation such as "Sheetname" instead of manually entering A, B, C... etc into the equation on the different Worksheets, A-H?

Also, in those equations you'll see "Sheet2", is there anyway to use a reference for the 2nd sheet in the workbook, because the worksheets don't always have that name "Sheet2", so in those instances I need to go in and manually rename the sheets.

Thank you.

Paul

[Barrie Davidson]

On 2002-02-19 16:30, dngidotcom wrote:
Followup:

Thanks for the help above. These are the equations I used:

=INDIRECT("'["&filename!$C$2&"(H).xls]Sheet2'!$A$1:$X$33")
=INDIRECT("'["&filename!$C$2&"(G).xls]Sheet2'!$A$1:$X$33")
...
=INDIRECT("'["&filename!$C$2&"(B).xls]Sheet2'!$A$1:$X$33")
=INDIRECT("'["&filename!$C$2&"(A).xls]Sheet2'!$A$1:$X$33")

and

='[PE-75-1(H).xls]Sheet2'!$A$1:$X$33
='[PE-75-1(G).xls]Sheet2'!$A$1:$X$33
...
='[PE-75-1(B).xls]Sheet2'!$A$1:$X$33
='[PE-75-1(A).xls]Sheet2'!$A$1:$X$33

You'll notice several fils with A-H being the difference in the file names. However, A-H are also the worksheet names where these equations are used. For example, on Worksheet A, the equation is:
='[PE-75-1(A).xls]Sheet2'!$A$1:$X$33

Is there any way to get that "A", the name of that Worksheet, into that equation with some reference equation such as "Sheetname" instead of manually entering A, B, C... etc into the equation on the different Worksheets, A-H?

Also, in those equations you'll see "Sheet2", is there anyway to use a reference for the 2nd sheet in the workbook, because the worksheets don't always have that name "Sheet2", so in those instances I need to go in and manually rename the sheets.

Thank you.

Paul

Paul, there is not way to reference the 2nd sheet with Excel's functions. You can, however, create a user defined function (UDF) that will return the second sheet's name. Paste this code in a module in your workbook:

Public Function Secondsheet() As String
' Written by Barrie Davidson
    Application.Volatile
    Secondsheet = Sheets(2).Name
End Function

and change your formula's to something like:
=INDIRECT("'["&filename!$C$2&"(H).xls]"& Secondsheet()&"'!$A$1:$X$33")

If you need any further instructions just let me know.