LinkBack URL
About LinkBacks
Is it possible to include dynamic range as part of a formula for a range name? This is what I am trying to do:Name Range Definition: MasterData!("A3"):Range("A3").End(xlDown).Row
Is it possible to include dynamic range as part of a formula for a range name? This is what I am trying to do:Name Range Definition: MasterData!("A3"):Range("A3").End(xlDown).Row
Are you trying to write a formula for a named range, write some VBA code, or...what? What you have written above is not a valid formula or valid VBA.
It is possible to define a dynamic named range. For example, this formula defines the range of data in column A, assuming no holes in the data:
Formula:
Please Login or Register to view this content.
This works if the table is on Sheet1 but how would you identify the table if the range is on Sheet2
Try:Formula:Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
The =OFFSET(Sheet2!A3,0,0,COUNTA(Sheet2!A:A)) returns Reference isn't valid. I am using the Name Manager for the formula.
Last edited by TeacherWart; 12-11-2022 at 09:02 PM.
Do you have a Sheet2? And is there data in cells A3:A325?
Yes I do. Sheet2 is named "MasterData" and there is data in Column A of that Worksheet. Currently I have 285 rows of data but left more for additional data. Originally what I wanted to do was make the range dynamic so I didn't have to define the range as $A$3:$A$325. Is there a way to programmatically name and define the dynamic range and include in the Worksheet_Open module?
Last edited by TeacherWart; 12-11-2022 at 10:38 PM.
In vba, you would create a variable, not a named range, and set it to the desired range:
If Sheet2 happens to have tabname, say, MasterData, then you could write, e.g.,
Please Login or Register to view this content.
RowCount = Sheets("MasterData").Cells(Rows.Count, "A").End(xlUp).Row
Ben Van Johnson
Named Range:Formula:Please Login or Register to view this content.
The Set MyRange = Sheet2.Range("A1:A" & RowCount) line produces an "Object required" error.
This is confusing.
1. Do you want a formula that delivers a Named Range, or VBA?
2. What is IN Sheet 2 A3, downwards. TEXT or NUMBERS or a MIXTURE?
3. Are they entered there DIRECTLY, or as a result of a FORMULA?
A sample sheet would clear all this confusion up (Yellow banner - top of screen)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks