I have a range of cells from A5 to A10. I named the range income. I know I can go to D5 and type =income and I will get what is in A5.
How do I make it so that when I go to D1 and type =income it will reference what is in A5?
Yor formula =income used in any cell (except column A) of rows 5 through 10. works in “intersection mode”: It selects the cell of the named range found in the same row.
=income in column D of row 1 doesn’t find an intersection with income and will therefore report an error.
A possible solution is to use =INDEX(income; 1) which references the first element (by row) of the array range named income. The disadvantage of such a formula is that you can’t fill it down or otherwise copy/paste it with the effect that you would expect. Possible workarounds are too complicated to get actual advantages of naming a range.
Well, that’s simply one of the shortcomings of named ranges which probably can’t be eliminated without creating even weightier issues.
My conclusion from experiences with named ranges made me omit them in my sheets (mostly).
2 Likes
Tried to do the =index(income;1) before and it didn’t work. It gave me a #name? error and changed the ; with a ,
I know you can do this in Excel, but I don’t want to pay 149 for it.
Which means that you use non-English UI, and don’t tell that (i.e., you don’t provide the data from Help->About, which doesn’t require 149). In your setup, the INDEX function is localized to something else.
I didn’t use (or have access to) any Excel for decades now.
However, since the error message you reported is English, I don’t feel sure about @MikeKagansk’s conclusion. For further investigation I would need your example file (as an .ods !) attached. In addition you should tell us your actual settings under >Tools>Options>Language Settings>Languages concerning UI and locale. In an unexpected situation I may need to post another question.
When I post formulas I always use the semicolon as the delimiter in function calls. It is still accepted by all locales, but unfortunately is then replaced in the FormulaLocal which is displayed in the formula bar by a comma if the locale does not use it as the decimal separator - and the user didn’t set a different (and better) choice. The usage of the comma in too many roles is a serious problem in international data exchange.
Concerning your specific questions you may study the attached example:
disask86034usageOfNamedRanges.ods (17.4 KB)
1 Like
There also is the topic
to which I like to link on occasions because the matter still isn’t sufficiently considered, imo.
Yes, I know. There are many deficiencies of this kind. Most of them have historical reasons, and we seem to have given up on working through old mistakes in all relevant fields.
Maybe defining the named range with an absolute address, non-relative.
$A$5:$A$10, so where ever you go, ‘income’ doesn’t change their address.
That does not reference A5 though if used as =income anywhere but on row 5. It would do the usual implicit intersection with the formula cell position and the range. Lupp’s =INDEX(income;1) combined with your absolute references used in the definition of income is a proper solution (as with relative references the actual range income is pointing to varies with the formula cell position).