LinkBack URL
About LinkBacks
Hello. I would like to have a named range that automatically updates to the newest data for the column to the right. I've attached a spreadsheet with more details. Thanks.
Hello. I would like to have a named range that automatically updates to the newest data for the column to the right. I've attached a spreadsheet with more details. Thanks.
try this..
- Identify a cell that will determine the column no. you want as the dynamic named range. Lets say this is cell AA1
- Use the following in 'refers to' field of the defined name ('first')
=INDEX(Sheet1!A:Z,,Sheet1!AA1)
The name will update based on column number inputted in Cell AA1
Does this work for you?
Life's a spreadsheet, Excel!
Say thanks, Click *
Hi
Take a look at the attached workbook and see if it does what you want.
I have set up 3 names ranges as follows
Now, if you type First in the Name box and press Enter, you will see the range A1:A20 selected as per your original
Please Login or Register to view this content.
If you type Second and press return you will see rang A1:A22 selected, to reflect the value of 5 that I entered in B22
If you type Third, you will see A21:A22 selected, which is the difference between the First and Second ranges, and reflects the "new range" as you requested.
--
Regards
Roger Govier
Microsoft Excel MVP
To Ace_XL...that doesn't seem to work. I have specific cells in the column and I only want to select those specific cells in the next column. And, in order to select the named range I usually use 'control G' and click on the named range, but when I insert your formula, the named range is no longer in the go to list. Maybe I'm doing it wrong.
Hi Roger. I couldn't open your file. Also, I wanted the new range to be in B (with the same rows of data).
Hi
Then change the formulae to
First =Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(LOOKUP(99^99,Sheet1!$A:$A),Sheet1!$A:$A))
Second =Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(LOOKUP(99^99,Sheet1!$B:$B),Sheet1!$B:$B))
Third =INDEX(Sheet1!$B:$B,MATCH(LOOKUP(99^99,Sheet1!$A:$A),Sheet1!$A:$A)+1):INDEX(Sheet1!$B:$B,MATCH(LOOKUP(99^99,Sheet1!$B:$B),Sheet1!$B:$B))
I have re-attached the file. Hopefully you can read it now.
This doesn't make sense and is very complicated. Thanks though.
Helllooooooooo. UNSOLVED!!!!!
Gee Thanks!!!
Have you tried it?
It works. You don't have any other solutions. Bye.
When you put numbers into column B, will there be any blank rows? Could you supply an example of how you put stuff in Col B?
Also - when you say "NEW" numbers, does that imply only the rows that you've recently added numbers to?
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Sure, there will be blank rows. Just like in column A. New numbers means that the same cells in Column A now have data in column B. I feel like this is pretty easy but I guess I'm not explaining it well. I've attached another spreadsheet. There is data in column A, that was named range "first". Now that I have added data into column B, I would like the named range to update to column B. It's fine if it includes column A as well, but I would prefer it to just include B. And then when I add data into column C, I want it to do the same thing. Keep in mind that there is unrelated data to the right. Thanks
@ Roger Govier. Gee you are welcome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks