Using named range without auto spill

New Contributor


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)

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



Perfect. Thanks so much @Riny_van_Eekelen.