Absolute vs. Relative cell names
Posted by Brian on December 14, 2001 8:03 AM
I've been writing a spreadsheet that contains named cell references. I want to use these names in formulas, but I want them to be relative references, not absolute. All named ranges or cells are absolute by default, meaning that when I copy a formula with a name in it, it copies it verbatim (copy the formula "=SALES" to an adjacent cell and it reproduces the formula "=SALES"). I want it to reference the adjacent cell.
For instance, let's say I create a name for cell A1 and call it "PROFIT". IF I go to cell A2 and type "=PROFIT", it gives me the value found in A1, right? Now, copy the formula in A2 and paste it to B2. What I want that formula to do is give me the value of cell B1. Instead it just repeats the value found in A1.
How can I change the name references to relative from absolute? Any ideas?