0

Even pressing F4 multiple times, I cannot use relative address in a formula when referring to a cell which has been named. Does this issue have a solution? For example, Think about a case in which I have some data in row1 and row2 and I want to use sum function in row 3 such that each cell of row 3 show the sum of its corresponding values of row 1 and row 2; meaning C1=A1+B1, C2=A2+B2 and …I want to perform this by dragging C1 formula to other cells of row3, but if I have already named A1 to something like “sales” and when writhing C1 formula type sales instead of A1, by dragging C1 formula, C2 becomes C2=”sales”+B2 rather than C2=A2+B2.( This example was an ordinary one in which one can easily type A1 instead of sales, but think a case this issue occurs when you are dealing with a workbook with multiple sheets and you want to refer to a cell that you have already named for convenience and use relative addressing in your formula, this would be very uncomfortable).

4
  • Can you please add more information (screenshots, sample data, your formula, expected output). Naming a cell (e.g. A1) to whatever, and referencing =A1 would simply work for me.
    – JvdV
    Jul 11, 2019 at 7:36
  • F4 is a keyboard shortcut, repeats command or action. However, the shortcut does not always work !! Jul 11, 2019 at 8:28
  • 1
    Reference to named ranges is always absolute, I don't think making it relative (therefore changing name when you copy formula) would be a significant need which Microsoft would consider to address. Maybe if you share a bit more background we might suggest a workaround. Jul 11, 2019 at 9:02
  • You can get row/column number of named cell. It is absolute. You can get the same for the cell, in which you enter the formula, by value. It is absolute. You can get the same for the cell, in which you enter the formula, by formula. It is relative. Then first-second+third will give you relative row/column numbers while copy or drag-n-drop. Having row and column numbers you can build the reference to the cell which will be relative.
    – Akina
    Jul 11, 2019 at 9:50

2 Answers 2

1

One option I can think about (which would be volatile) and I don't really see the practicality myself:

enter image description here

Cell A1 is a named cell 'Test'. Formula in C1:

=OFFSET(Test,ROW(1:1)-1,0)+B1

Drag down.

0

The one thing you CANNOT do is have Excel natively change "sales" back to a cell reference.

So if the idea is to change it in the pre-fill formula, I cannot see how finding something to click on or buried in the Ribbon would be easier than just typing the address over the NR name. And one cell? Even 10x easier isn't anything much. If the cell referred to is actually pretty hard to accurately type, click, etc., maybe even in another spreadsheet that you can't even open and click on... all you need do is open the Name Manager, edit the NR to show the cell reference, highlight, copy, close it all, then paste in the formula replacing the name. So easy to be accurate, no matter how daunting the written reference would be to type.

Moving on... if you want to retain it in the first cell (C1 here), undo it as above, fill down, then reapply Names to fix C1 back up.

If that doesn't matter, set the formula up in C2 after doing C1, the name won't appear, and fill down with C2's formula.

If there is nothing daunting about the cell address, then just type it ("A1"). Excel does not automatically convert typed addresses into NR's that are identical. You have to tell it to do so. Then fill.

You can use a whole different approach than the one you insist upon, as suggested by JvdV. That formula works nicely, but then so did typing "A1" instead of "sales" so...

If I had to make a bet, I'd say:

1) You really have a complicated thing to type without the NR name and want to avoid that. The Name Manager-Copy-Paste approach solves that.

2) Have a bet and are deperately hoping someone can tell you a rare secret. There is none though so...

3) Are pretty sure you need to write a macro to do things, or buy an Add-In. Well, yes, unless you can do the little steps above.

4) Have a controlled situation and someone can do this, so they must be violating the rules and you have to have proof of it being impossible in ordinary use.

5) Heck, who knows?

Finally, if you are faced with something dauntingly complex to type, much less get right again and again, don't use the Name Manager like this. Create a sheet that you move with you when jumping sheets. On that "crib" sheet, keep all the longform cell references you need from time to time and use copy and paste from that to put them in. So, hardest case, really:

1) Enter your formula using bogus names in places you will fill in. Do a real formula as you go so you get the benefit of colored parentheses and so on, and when ready to hit Enter, press Ctrl-Home instead, type a Space, and hit Enter. Go back and forth, replacing stand-in bogosity with the appropriate references from the crib sheet, then press Ctrl-Home again and remove the space at the start. Hit Enter and see how you did.

If you can buy an Add-In instead of rolling your own VBA, Kutools offers one that specifically claims to do what Excel does not do natively and give you cell addresses for the Named Ranges in your formulas:

https://www.extendoffice.com/product/kutools-for-excel/replace-range-names-in-formulas.html

You know: and other stuff to. If memory serves, it's pricey though. One can look into others too.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .