Cell contents to be used as worksheet reference

StanSz

Board Regular
Joined
Sep 18, 2004
Messages
195
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,051
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)"
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
479
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Say your dropdown is cell A1,

Code:
Dim wsReference As String

wsReference = [A1].Value

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

StanSz

Board Regular
Joined
Sep 18, 2004
Messages
195
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
 

StanSz

Board Regular
Joined
Sep 18, 2004
Messages
195

ADVERTISEMENT

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
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
479
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Board Regular
Joined
Sep 18, 2004
Messages
195
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

Board Regular
Joined
Sep 18, 2004
Messages
195
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
 

Forum statistics

Threads
1,136,442
Messages
5,675,892
Members
419,591
Latest member
mersanko

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top