Cell contents to be used as worksheet reference

[StanSz]

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

 

[mikerickson]

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

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)"

 

[JonXL]

Say your dropdown is cell A1,

Dim wsReference As String

wsReference = [A1].Value

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

 

[StanSz]

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

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

 

[StanSz]

Say your dropdown is cell A1,

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

 

[JonXL]

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)

 

[StanSz]

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

 

[StanSz]

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