SOLVED

Using named range without auto spill

%3CLINGO-SUB%20id%3D%22lingo-sub-2264923%22%20slang%3D%22en-US%22%3EUsing%20named%20range%20without%20auto%20spill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2264923%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EIn%20the%20attached%20spreadsheet%20I%20have%20defined%20'Data'%20as%20a%20range%20name%20for%20three%20numbers%20in%20a%20column%20A.%20I%20want%20to%20put%20the%20content%20of%20cell%20A2%20into%20B2%20using%20%3DData%20(rather%20than%20%3DA2).%20However%2C%20the%20formula%20returns%20all%20the%20values%20of%20the%20range%20name%20Data%20instead%20of%20the%20value%20of%20A2%20alone.%20How%20can%20I%20turn%20off%20the%20auto%20spill%20and%20just%20get%20the%20value%20that%20corresponds%20to%20row%202%3F%3C%2FP%3E%3CP%3EThanks%20Alistair%26nbsp%3B%3C%2FP%3E%3CP%3EPS%26nbsp%3BI'm%20using%20Microsoft%20365%20Excel%2016.47.1%20on%20Mac%20OS%2011.2.3%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2264923%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2264951%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20named%20range%20without%20auto%20spill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2264951%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1022193%22%20target%3D%22_blank%22%3E%40AlistairD%3C%2FA%3E%26nbsp%3BNot%20sure%20why%20you%20would%20want%20to%20do%20that%20but%20try%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%40Data%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

In the attached spreadsheet I have defined 'Data' as a range name for three numbers in a column A. I want to put the content of cell A2 into B2 using =Data (rather than =A2). However, the formula returns all the values of the range name Data instead of the value of A2 alone. How can I turn off the auto spill and just get the value that corresponds to row 2?

Thanks Alistair 

PS I'm using Microsoft 365 Excel 16.47.1 on Mac OS 11.2.3

2 Replies
best response confirmed by AlistairD (New Contributor)
Solution

@AlistairD Not sure why you would want to do that but try this:

=@Data

 

Perfect. Thanks so much @Riny_van_Eekelen.