Forum Discussion
EXTENDING A NAMED RANGE
I have a range of cells in a sheet that is named, and formulas in a different sheet refer to that range.
I inserted a row into the middle of that range (had an entry to add), and now one row at the bottom is outside the named range.
is there an easy way to extend the range to include the now excluded row, or do I need to simply redefine the named range over again?
9 Replies
Definitely you can use the table name in formulas
We call it "Structured Table Reference"
Example: If you convert a list into a Table >> Then name it "Source". Automatically the columns are named by their Column Headers.
If Among the columns you have : Date, Manager, Region, Amount (these are the column Headers)
You can create a SUM function to Sum the Amount as follows
=SUM(Source[Amount])
Source being the Table Name
Amount being the Field name
Not only Excel recognizes the Table name (Source) but also the Column Names. You must include the column name in square brackets. It also appears in the intellisesnse list of Excel
Hope that Helps
Nabil Mourad
would this work in a vlookup formula? that's the most common reason I create ranges.
so, using your example, if I want to lookup an amount on a given date, could I use this:
=vlookup(A2,Source,4,FALSE)
??
Hi
If you will be continuously adding or removing rows of transactions, the, instead of reediting the named range with each change, convert the range as a Table (CTRL +T) >> Name the Table >>and whenever you add or delete rows the table auto-expands to accommodate the change.
Hope that Helps
Nabil Mourad
Hi,
I've tested that by naming a range and add a row in the middle of that range, but the range is automatically expanded!
The case that the range is not automatically expanded is if you add the row at the top or bottom.
Please check the range again.
However, if that somehow happens to you, you don't have to redefine the range again, just click Ctrl+F3 to access the Name Manager, from there you can edit the defined names.
Regards
I think what happened was that I added the row at the bottom, and then resorted based on alphabet order of the name in the first column. so, I would have been better off, I guess, inserting the data into the middle.
thanks for the tip on using Ctrl+F3. that sounds like exactly what I need (why isn't that easier to find?? I didn't see it anywhere in the help pages...
- As a warning, please always press F2 (to shift to Edit mode) before you edit the range of your defined name!
