Home

EXTENDING A NAMED RANGE

%3CLINGO-SUB%20id%3D%22lingo-sub-805201%22%20slang%3D%22en-US%22%3EEXTENDING%20A%20NAMED%20RANGE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805201%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20range%20of%20cells%20in%20a%20sheet%20that%20is%20named%2C%20and%20formulas%20in%20a%20different%20sheet%20refer%20to%20that%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20inserted%20a%20row%20into%20the%20middle%20of%20that%20range%20(had%20an%20entry%20to%20add)%2C%20and%20now%20one%20row%20at%20the%20bottom%20is%20outside%20the%20named%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eis%20there%20an%20easy%20way%20to%20extend%20the%20range%20to%20include%20the%20now%20excluded%20row%2C%20or%20do%20I%20need%20to%20simply%20redefine%20the%20named%20range%20over%20again%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-805201%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-805260%22%20slang%3D%22en-US%22%3ERe%3A%20EXTENDING%20A%20NAMED%20RANGE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805260%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F370732%22%20target%3D%22_blank%22%3E%40janaki223%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tested%20that%20by%20naming%20a%20range%20and%20add%20a%20row%20in%20the%20middle%20of%20that%20range%2C%20but%20the%20range%20is%20automatically%20expanded!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EThe%20case%20that%20the%20range%20is%20not%20automatically%20expanded%20is%20if%20you%20add%20the%20row%20at%20the%20top%20or%20bottom.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EPlease%20check%20the%20range%20again.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20if%20that%20somehow%20happens%20to%20you%2C%20you%20don't%20have%20to%20redefine%20the%20range%20again%2C%20just%20click%20Ctrl%2BF3%20to%20access%20the%20Name%20Manager%2C%20from%20there%20you%20can%20edit%20the%20defined%20names.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-805301%22%20slang%3D%22en-US%22%3ERe%3A%20EXTENDING%20A%20NAMED%20RANGE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805301%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20what%20happened%20was%20that%20I%20added%20the%20row%20at%20the%20bottom%2C%20and%20then%20resorted%20based%20on%20alphabet%20order%20of%20the%20name%20in%20the%20first%20column.%20%26nbsp%3Bso%2C%20I%20would%20have%20been%20better%20off%2C%20I%20guess%2C%20inserting%20the%20data%20into%20the%20middle.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%20for%20the%20tip%20on%20using%20Ctrl%2BF3.%20%26nbsp%3Bthat%20sounds%20like%20exactly%20what%20I%20need%20(why%20isn't%20that%20easier%20to%20find%3F%3F%20%26nbsp%3BI%20didn't%20see%20it%20anywhere%20in%20the%20help%20pages...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-805313%22%20slang%3D%22en-US%22%3ERe%3A%20EXTENDING%20A%20NAMED%20RANGE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805313%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F370732%22%20target%3D%22_blank%22%3E%40janaki223%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20even%20find%20it%20under%20the%20Formulas%20tab%20as%20the%20defined%20names%20have%20a%20complete%20group%20in%20the%20tab%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127011i80906482E318C292%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Defined%20Names.png%22%20title%3D%22Defined%20Names.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-805403%22%20slang%3D%22en-US%22%3ERe%3A%20EXTENDING%20A%20NAMED%20RANGE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805403%22%20slang%3D%22en-US%22%3EAs%20a%20warning%2C%20please%20always%20press%20F2%20(to%20shift%20to%20Edit%20mode)%20before%20you%20edit%20the%20range%20of%20your%20defined%20name!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-805459%22%20slang%3D%22en-US%22%3ERe%3A%20EXTENDING%20A%20NAMED%20RANGE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805459%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F370732%22%20target%3D%22_blank%22%3E%40janaki223%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EIf%20you%20will%20be%20continuously%20adding%20or%20removing%20rows%20of%20transactions%2C%20the%2C%20instead%20of%20reediting%20the%20named%20range%20with%20each%20change%2C%20convert%20the%20range%20as%20a%20Table%20(CTRL%20%2BT)%20%26gt%3B%26gt%3B%20Name%20the%20Table%20%26gt%3B%26gt%3Band%20whenever%20you%20add%20or%20delete%20rows%20the%20table%20auto-expands%20to%20accommodate%20the%20change.%3C%2FP%3E%3CP%3EHope%20that%20Helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-805524%22%20slang%3D%22en-US%22%3ERe%3A%20EXTENDING%20A%20NAMED%20RANGE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805524%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20I%20convert%20the%20range%20to%20a%20table%2C%20can%20I%20use%20that%20table%20name%20in%20functions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-805548%22%20slang%3D%22en-US%22%3ERe%3A%20EXTENDING%20A%20NAMED%20RANGE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805548%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F370732%22%20target%3D%22_blank%22%3E%40janaki223%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDefinitely%20you%20can%20use%20the%20table%20name%20in%20formulas%3C%2FP%3E%3CP%3EWe%20call%20it%20%22%3CSTRONG%3EStructured%20Table%20Reference%3C%2FSTRONG%3E%22%3C%2FP%3E%3CP%3EExample%3A%20If%20you%20convert%20a%20list%20into%20a%20Table%20%26gt%3B%26gt%3B%20Then%20name%20it%20%22Source%22.%20Automatically%20the%20columns%20are%20named%20by%20their%20Column%20Headers.%3C%2FP%3E%3CP%3EIf%20Among%20the%20columns%20you%20have%20%3A%20Date%2C%20Manager%2C%20Region%2C%20Amount%20(these%20are%20the%20column%20Headers)%3C%2FP%3E%3CP%3EYou%20can%20create%20a%20%3CSTRONG%3ESUM%3C%2FSTRONG%3Efunction%20to%20Sum%20the%20Amount%20as%20follows%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E%3DSUM(Source%5BAmount%5D)%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3ESource%3C%2FSTRONG%3Ebeing%20the%20Table%20Name%3C%2FP%3E%3CP%3E%3CSTRONG%3EAmount%3C%2FSTRONG%3Ebeing%20the%20Field%20name%3C%2FP%3E%3CP%3ENot%20only%20Excel%20recognizes%20the%20Table%20name%20(Source)%20but%20also%20the%20Column%20Names.%20You%20must%20include%20the%20column%20name%20in%20square%20brackets.%20It%20also%20appears%20in%20the%20intellisesnse%20list%20of%20Excel%3C%2FP%3E%3CP%3EHope%20that%20Helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-805584%22%20slang%3D%22en-US%22%3ERe%3A%20EXTENDING%20A%20NAMED%20RANGE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805584%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewould%20this%20work%20in%20a%20vlookup%20formula%3F%20%26nbsp%3Bthat's%20the%20most%20common%20reason%20I%20create%20ranges.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eso%2C%20using%20your%20example%2C%20if%20I%20want%20to%20lookup%20an%20amount%20on%20a%20given%20date%2C%20could%20I%20use%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3Dvlookup(A2%2CSource%2C4%2CFALSE)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3F%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-805618%22%20slang%3D%22en-US%22%3ERe%3A%20EXTENDING%20A%20NAMED%20RANGE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-805618%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F370732%22%20target%3D%22_blank%22%3E%40janaki223%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESure%20it%20will%20work%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E
janaki223
Occasional Contributor

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

@janaki223

 

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

@Haytham Amairah 

 

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...

@janaki223

 

You can even find it under the Formulas tab as the defined names have a complete group in the tab:

Defined Names.png

As a warning, please always press F2 (to shift to Edit mode) before you edit the range of your defined name!

@janaki223 

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

@nabilmourad 

 

if I convert the range to a table, can I use that table name in functions?

@janaki223 

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

@nabilmourad 

 

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)

??

@janaki223 

Sure it will work

Nabil Mourad

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies