Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Using Worksheet reference with worksheet name from another c

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,026
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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.

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    North West
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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:
    Code:
    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.
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •