Can A Named Range Be A Variable?

  • Thread starter Thread starter Minitman
  • Start date Start date
M

Minitman

Greetings,

I have a formula that is looking at a named range (Sheet1!A1) from
Sheet2. I need to add 19 more sheets (Sheet3 thu Sheet21). Is there
anyway to change which named range that formula is looking at?

TIA

-Minitman
 
Hi
you may use the function INDIRECT. e.g. if you store the sheetname in
cell A1 you can use it as follows:
=INDIRECT("'" & A1 & "'!A1)
(note the multiple apostophes at the beginning and in the middle: " ' "
and " ' !)
 
Hi Minitman,

Frank's example show you how to reference cell A1 in another sheet using
indirect referencing.

You asked about names, and let's assume that you stored the text of the name
in A1, say myRange, and myRange points at Sheet3!A17, then you can access
it, also musing INDIRECT, like so,

=INDIRECT(A1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I like to use CHOOSE wherever possible:

assume you have a formula in Z1 that gives 1 to 19 to determine which named
range you want to use and that your named ranges are named One, two etc then
something like:

=CHOOSE(Z1,One,Two,three, ...)

CHOOSE is generally more efficient than INDIRECT because it is not a
volatile function.
Its disadvantages are that your formula has to resolve to 1 to n, and that
you cannot use too many named ranges before the formula gets too long or you
hit the max 30 argument limit.


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
 
Thanks for the quick response!

I think that i need to clarify what I am trying to do here.

A vbe code on each year sheet (each sheet is labeled as a year: 1994,
1995,,,2004) picks up the contents of the cell in the chosen row when
the user clicks on a cell from a certain column in that row (eg. user
chooses G13 and the vbe code automatically places the contents of B13
into A1, G58 - B58 into A1, etc.). This works. However, in the Data
sheet, a formula to filter a customer list (showing only those
customers that fall within the start - end range) was written for only
one year sheet. As such, it is looking to A1 on THAT sheet.

The question is how can I get that formula to automatically know which
year sheet the user has triggered the vbe code changing the contents
of that sheets A1?

Any ideas?

TIA

-Minitman
 
Hey Charles,

You mentioned a formula in Z1, that is still missing. What I need is
a formula that can monitor cell "A1" on 20 sheets and whichever of
those 20 cells changes, to automatically copy the contents of that
changed cell into say cell "O1" on the Data sheet.

Is this possible
 
Hey Charles,

I do not know to much about VBA Worksheet_Change events.
This workbook that I am working on has one at the sheet level and I
cannot get it to work on the workbook level.

Here is the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 6 And Target.Row >= 3 Then
Range("A1").Value = Target.Offset(0, -4).Value
End If
End Sub

I tried changing the Range("A1") to Range("Customers!O1") and get a
Microsoft Visual Basic message box with this inside:

Run-time error'1004':
Method 'Range' of object '_Worksheet' failed

Any suggestions?

TIA

-Minitman
 
You need to use the Worksheet_Change event not selection_change

put this sub (after you have changed it to do what you want) in each
worksheet that you want to get the value from.
(double click the worksheet in the VBE project explorer)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And Target.Column = 1 Then
Worksheets("Sheet3").Range("b2") = Target.Value
End If
End Sub

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
 
Hey Charles,

That works great - Thanks.

Only one more procedure left to complete this project. But that can
wait for the next post.

Again, thank you very much for your help.

-Minitman
 
Back
Top