Excel Name Definition using "indirect" formula within its Range target area

%3CLINGO-SUB%20id%3D%22lingo-sub-1739298%22%20slang%3D%22en-US%22%3EExcel%20Name%20Definition%20using%20%22indirect%22%20formula%20within%20its%20Range%20target%20area%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1739298%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Name-Definition_1091002-2034.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F223681iF222B8976AB3D9D6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Name-Definition_1091002-2034.jpg%22%20alt%3D%22Name-Definition_1091002-2034.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1739298%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1739672%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Name%20Definition%20using%20%22indirect%22%20formula%20within%20its%20Range%20target%20area%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1739672%22%20slang%3D%22en-US%22%3EI%20don't%20think%20the%20INDIRECT%20argument%20currently%20evaluates%20to%20a%20valid%20cell%20reference.%20Try%20this%3A%3CBR%20%2F%3E-%20Copy%20the%20current%20formula%20from%20the%20name%20and%20paste%20it%20into%20any%20cell.%3CBR%20%2F%3E-%20Press%20F2%20on%20that%20cell%2C%20highlight%20everything%20between%20the%20brackets%20()%20and%20press%20the%20F9%20key%20once.%20Copy%20the%20resulting%20string%3CBR%20%2F%3E-%20press%20escape%3CBR%20%2F%3E-%20select%20another%20cell%20and%20press%20the%20%3D%20sign%20and%20paste.%20Now%20try%20to%20enter.%20If%20the%20address%20was%20valid%20you%20should%20not%20receive%20an%20error.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1741364%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Name%20Definition%20using%20%22indirect%22%20formula%20within%20its%20Range%20target%20area%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1741364%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDear%20Mr%2FMs.%20Pieterse%3C%2FP%3E%3CP%3EThanks%2C%20as%20told%20in%20my%20first%20posting%2C%20there%20us%20no%20error%20message%20%26amp%3B%20the%20same%20formula%20works%3C%2FP%3E%3CP%3Ewith%20Excel%20%22Data%20Validation%22%20Function%20as%20shown%20within%20the%20file%20linked%20here%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdrive.google.com%2Ffile%2Fd%2F1l7WaxCdNSw6cTtqWqeJEMya8pl3VRxrl%2Fview%3Fusp%3Dsharing%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdrive.google.com%2Ffile%2Fd%2F1l7WaxCdNSw6cTtqWqeJEMya8pl3VRxrl%2Fview%3Fusp%3Dsharing%3C%2FA%3E%3C%2FP%3E%3CH3%20id%3D%22toc-hId-1256366025%22%20id%3D%22toc-hId-1256365351%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fexcel-name-definition-using-quot-indirect-quot-formula-within%2Fm-p%2F1739672%23M76740%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3ERe%3A%20Excel%20Name%20Definition%20using%20%22indirect%22%20formula%20within%20its%20Range%20target%20area%3C%2FA%3E%3C%2FH3%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20think%20the%20INDIRECT%20argument%20currently%20evaluates%20to%20a%20valid%20cell%20reference.%20Try%20this%3A%3CBR%20%2F%3E-%20Copy%20the%20current%20formula%20from%20the%20name%20and%20paste%20it%20into%20any%20cell.%3CBR%20%2F%3E-%20Press%20F2%20on%20that%20cell%2C%20highlight%20everything%20between%20the%20brackets%20()%20and%20press%20the%20F9%20key%20once.%20Copy%20the%20resulting%20string%3CBR%20%2F%3E-%20press%20escape%3CBR%20%2F%3E-%20select%20another%20cell%20and%20press%20the%20%3D%20sign%20and%20paste.%20Now%20try%20to%20enter.%20If%20the%20address%20was%20valid%20you%20should%20not%20receive%20an%20error.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor
4 Replies
Highlighted
I don't think the INDIRECT argument currently evaluates to a valid cell reference. Try this:
- Copy the current formula from the name and paste it into any cell.
- Press F2 on that cell, highlight everything between the brackets () and press the F9 key once. Copy the resulting string
- press escape
- select another cell and press the = sign and paste. Now try to enter. If the address was valid you should not receive an error.
Highlighted

@Jan Karel Pieterse 

Dear Mr/Ms. Pieterse

Thanks, as told in my first posting, there us no error message & the same formula works

with Excel "Data Validation" Function as shown within the file linked here:

https://drive.google.com/file/d/1l7WaxCdNSw6cTtqWqeJEMya8pl3VRxrl/view?usp=sharing

Re: Excel Name Definition using "indirect" formula within its Range target area

 

I don't think the INDIRECT argument currently evaluates to a valid cell reference. Try this:
- Copy the current formula from the name and paste it into any cell.
- Press F2 on that cell, highlight everything between the brackets () and press the F9 key once. Copy the resulting string
- press escape
- select another cell and press the = sign and paste. Now try to enter. If the address was valid you should not receive an error.

Highlighted
I'm not sure whay this doesn't work, but I suspect it has to do with the name also containing an INDIRECT
Highlighted

@Jan Karel Pieterse 

Dear Jan, 

Thanks for your patients, Hopeful you've found out my purpose within the sample excel file I've provided earlier. That is:  to have the function of :

"Changing target area without opening the "Name Definition" Editing Dialog Box" .

In other words, I would like only to key in only the starting Row number & the ending row number (of the target area) in any assigned 2 cells.

 

Thanking you in advance.