Home

Name manager dynamic range

%3CLINGO-SUB%20id%3D%22lingo-sub-898606%22%20slang%3D%22en-US%22%3EName%20manager%20dynamic%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898606%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3EI%20would%20like%20to%20setup%20a%20dynamic%20range%20selection%20using%20excel%20Name%20Manager.%3CBR%20%2F%3EI%20found%20this%20function%20that%20almost%20do%20the%20job.%20The%20issue%20i%20have%20is%20that%20i%20want%20to%20select%20several%20columns%20and%20not%20only%20one.%20Any%20idea%3F%3CBR%20%2F%3E%3CBR%20%2F%3EBelow%20the%20found%20that%20is%20working%20with%20on%20column%20%2F%20i%20did%20not%20find%20a%20way%20to%20select%20multiple%20columns%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DOFFSET(%24K%241%2C1%2C0%2CCOUNTA(%24A%3A%24A)-1%2C1)%3CBR%20%2F%3E%3CBR%20%2F%3EWith%20this%20formula%20used%20in%20name%20manager%2C%20I%20will%20dynamically%20s%C3%A9lect%20all%20content%20columns%20K%20based%20on%20data%20of%20column%20A%3CBR%20%2F%3EWhat%20to%20do%20to%20select%20dynamically%20K%20to%20O%20Columns%20dynamically%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-898606%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20name%20offset%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-898672%22%20slang%3D%22en-US%22%3ERe%3A%20Name%20manager%20dynamic%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898672%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421881%22%20target%3D%22_blank%22%3E%40Anthony44%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20attach%20your%20sample%20file%20to%20illustrate%20your%20data%20layout%20and%20desired%20results.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-898723%22%20slang%3D%22en-US%22%3ERe%3A%20Name%20manager%20dynamic%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898723%22%20slang%3D%22en-US%22%3E%3CP%3EFinally%2C%20I%20found%20the%20solution%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DOFFSET(%3CFONT%20color%3D%22%23ff0000%22%3E%3CSTRONG%3ESheet1!%24D%241%3A%24E%241%3C%2FSTRONG%3E%3C%2FFONT%3E%3B1%3B0%3BCOUNTA(Sheet1!%24A%3A%24A)-1%3B%3CSTRONG%3E%3CFONT%20color%3D%22%23ff9900%22%3E2%3C%2FFONT%3E%3C%2FSTRONG%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eto%20select%20multiple%20column%3A%3C%2FP%3E%3CP%3E1.%20select%20column%20range%20(in%20Red)%3C%2FP%3E%3CP%3E2.%20change%20the%20last%20digit%20(in%20orange)%20%3D%20number%20column%20you%20want%20to%20select%20if%202%20columns%20put%202%2C%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-898736%22%20slang%3D%22en-US%22%3ERe%3A%20Name%20manager%20dynamic%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-898736%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421881%22%20target%3D%22_blank%22%3E%40Anthony44%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DOFFSET(%24K%241%2C1%2C0%2CCOUNTA(%24A%3A%24A)-1%2C5)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(%24K%242%3AINDEX(%24O%3A%24O%2CCOUNTA(%24A%3A%24A))%2C0%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eif%20select%20starting%20from%20second%20row%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Anthony44
New Contributor
Hello,
I would like to setup a dynamic range selection using excel Name Manager.
I found this function that almost do the job. The issue i have is that i want to select several columns and not only one. Any idea?

Below the found that is working with on column / i did not find a way to select multiple columns:

=OFFSET($K$1,1,0,COUNTA($A:$A)-1,1)

With this formula used in name manager, I will dynamically sélect all content columns K based on data of column A
What to do to select dynamically K to O Columns dynamically?

Thank you
3 Replies

@Anthony44 

Please attach your sample file to illustrate your data layout and desired results.

Finally, I found the solution:

 

=OFFSET(Sheet1!$D$1:$E$1;1;0;COUNTA(Sheet1!$A:$A)-1;2)

 

to select multiple column:

1. select column range (in Red)

2. change the last digit (in orange) = number column you want to select if 2 columns put 2, etc.

 

Tks

@Anthony44 

Perhaps

=OFFSET($K$1,1,0,COUNTA($A:$A)-1,5)

or

=INDEX($K$2:INDEX($O:$O,COUNTA($A:$A)),0,0)

if select starting from second row 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies