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?
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).
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)
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).