Cell contents to be used as worksheet reference

StanSz

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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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)"
 
Upvote 0
Say your dropdown is cell A1,

Code:
Dim wsReference As String

wsReference = [A1].Value

Range("N38").Formula = "=Offset('" & wsReference & "'!AL10, 4, 2, 1, 1)"
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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
Back
Top