Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Cell contents to be used as worksheet reference

  1. #1
    Board Regular
    Join Date
    Sep 2004
    Posts
    194
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Cell contents to be used as worksheet reference

    Using VBA I'm trying to read a cell (with a dropdown list of symbols) -- like "QQQ," and then use this symbol to refer to a worksheet in a formula such as:

    Range("N38").Formula = "=Offset(QQQ!AL10, 4, 2, 1, 1)"

    so that I can use whatever symbol is in the dropdown and have it refer to to a different worksheet.

    Thus if QQQ is in the dropdown cell I'll get data from the QQQ worksheet, but if

    SPY is in the dropdown cell I'll get data from the SPY worksheet instead.

    Thanks

    Stan

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,876
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Cell contents to be used as worksheet reference

    Since you are working across sheets, its good to specify all the sheets being used

    Code:
    Dim sheetNameCell as Range
    Dim otherSheet as Worksheet
    
    Set sheetNameCell = ThisWorkbook.Sheets("Sheet1").Range("A1"): Rem adjust
    
    ActiveSheet.Range("N38").Formula = "=OFFSET" & otherSheetCell.Value & "!AL10, 4, 2, 1, 1)"

  3. #3
    Board Regular JonXL's Avatar
    Join Date
    Feb 2018
    Posts
    169
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cell contents to be used as worksheet reference

    Say your dropdown is cell A1,

    Code:
    Dim wsReference As String
    
    wsReference = [A1].Value
    
    Range("N38").Formula = "=Offset('" & wsReference & "'!AL10, 4, 2, 1, 1)"

  4. #4
    Board Regular
    Join Date
    Sep 2004
    Posts
    194
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cell contents to be used as worksheet reference

    Quote Originally Posted by mikerickson View Post
    Since you are working across sheets, its good to specify all the sheets being used

    Code:
    Dim sheetNameCell as Range
    Dim otherSheet as Worksheet
    
    Set sheetNameCell = ThisWorkbook.Sheets("Sheet1").Range("A1"): Rem adjust
    
    ActiveSheet.Range("N38").Formula = "=OFFSET" & otherSheetCell.Value & "!AL10, 4, 2, 1, 1)"


    Thank you for the reply, but I'm not getting this to work -- I obviously didn't understand something. Nothing happens to cell N38

    I'm using this as a Worksheet change by Target routine with the code in the Sheet1 module.
    Here's the whole code:

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim sheetNameCell As Range
    Dim otherSheet As Worksheet

    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Target.Address <> "$S$4" Then Exit Sub

    Range("L38").Value = Range("L38").Value + 10: Rem test to see it work - it doesn't

    Set sheetNameCell = ThisWorkbook.Sheets("Sheet1").Range("S4"): Rem adjust

    ActiveSheet.Range("N38").Formula = "=OFFSET" & otherSheetCell.Value & "!AL10, 4, 2, 1, 1)

    End Sub

    Whatever is the value from S4 (the dropdown cell) is the name of the worksheet from which I want to get data.

    Thanks again

  5. #5
    Board Regular
    Join Date
    Sep 2004
    Posts
    194
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cell contents to be used as worksheet reference

    Quote Originally Posted by JonXL View Post
    Say your dropdown is cell A1,

    Code:
    Dim wsReference As String
    
    wsReference = [A1].Value
    
    Range("N38").Formula = "=Offset('" & wsReference & "'!AL10, 4, 2, 1, 1)"

    Thank you for the reply but I'm not getting this to work -- something I'm not understanding I'm sure. Nothing happens to Cell N38

    I'm using this as a Worksheet change by Target routine with the code in the Sheet1 module.
    Here's the whole code:

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim wsReference As String

    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Target.Address <> "S4" Then Exit Sub

    ActiveSheet.Range("L38").Value = ActiveSheet.Range("L38").Value + 10: Rem test to see it work - it doesn't

    wsReference = [S4].Value

    Range("N38").Formula = "=Offset('" & wsReference & "'!AL10, 4, 2, 1, 1)"

    End Sub

    Thank you again

  6. #6
    Board Regular JonXL's Avatar
    Join Date
    Feb 2018
    Posts
    169
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cell contents to be used as worksheet reference

    Is this the only thing your using this VBA for?

    If so, and if you're open to a non-VBA solution, you might find the INDIRECT function of use.

    I can't test this now, but I think if you put this formula in N38, it will do what you want:

    =OFFSET(INDIRECT("'"&S4&"'!AL10"), 4, 2, 1, 1)

  7. #7
    Board Regular
    Join Date
    Sep 2004
    Posts
    194
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cell contents to be used as worksheet reference

    Quote Originally Posted by JonXL View Post
    Is this the only thing your using this VBA for?

    If so, and if you're open to a non-VBA solution, you might find the INDIRECT function of use.

    I can't test this now, but I think if you put this formula in N38, it will do what you want:

    =OFFSET(INDIRECT("'"&S4&"'!AL10"), 4, 2, 1, 1)
    This VBA is an attempt to get around the INDIRECT command's inability to work with charts.

    Once I can get this 'drop-down to worksheet name' routine to work in a formula I'll be wanting to write the new 'assembled' formula to the formulas in chart series. That wouldn't work with INDIRECT, but if I can get a formula to work then I think I should be able to write chart formulas using the different symbols from the dropdown. This will enable me to have ONE chart being able to display the data from different symbols -- since the structure of all the symbols worksheets are identical, i.e. the same # of rows and columns, and everything in the same place, just from a different stock

    Thank you

  8. #8
    Board Regular
    Join Date
    Sep 2004
    Posts
    194
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cell contents to be used as worksheet reference

    Quote Originally Posted by StanSz View Post
    This VBA is an attempt to get around the INDIRECT command's inability to work with charts.

    Once I can get this 'drop-down to worksheet name' routine to work in a formula I'll be wanting to write the new 'assembled' formula to the formulas in chart series. That wouldn't work with INDIRECT, but if I can get a formula to work then I think I should be able to write chart formulas using the different symbols from the dropdown. This will enable me to have ONE chart being able to display the data from different symbols -- since the structure of all the symbols worksheets are identical, i.e. the same # of rows and columns, and everything in the same place, just from a different stock

    Thank you
    Dear Jon,

    I was able to get the code to work. I just played around with the formula line.

    This line works:
    Range("N38").Formula = "=Offset('" & wsReference & "'!AL10, 4, 2, 1, 1)"

    This line -- without the ' -- also works.
    Range("T38").Formula = "=Offset(" & wsReference & "!AL10, 4, 2, 1, 1)"

    Thank you for your help. Now on to the next task, writing to the chart series.

    Stan

Some videos you may like

User Tag List

Tags for this Thread

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
  •