1

Let's say in A1 I have "=ROW()". The cell shows '1' since it's the first row in the sheet.

Now in A2, I put "=A1". Unfortunately, this references the VALUE of A1, rather than the formula. If it referenced the formula, then A2 would instead give a value of '2' instead (which is what I want).

If I then go on to change the formula in A1 afterwards, then A2's value will also change, since it relies on the formula in A1.

How do I go about this?

6
  • Copy A1 and paste in A2. That copies the formula into A2. If the formula contains cell references, they will automatically adjust unless you anchor the row and/or column with $. – fixer1234 Aug 4 '15 at 2:16
  • @fixer1234: Simply copying like this doesn't work as if you then change A1, then A2 won't also change. I've edited my Q to clarify. – Dan W Aug 4 '15 at 2:40
  • It sounds like you want to say that A1 contains the ROW() function, so you want to reference it in a way from the next row that will be interpreted as ROW(A1)+1. A2 needs to contain its own, independent formula that accomplishes what you want. There is no way to incorporate the A1 formula into what A2 does. If you describe how you want to use it, we can suggest a solution. – fixer1234 Aug 4 '15 at 3:21
  • @fixer1234: I've rejigged it a bit, and hopefully clarified, though I thought it was perfectly clear before. Can you undo your close vote, or otherwise recommend how I can make it clearer still if you really think that's possible? Btw, I don't want anything like "ROW(A1)+1" in A2. It's just that being the second row, it automatically takes the ROW() function, and applies it to that cell, giving a value of 2. – Dan W Aug 4 '15 at 23:04
  • It soulds like you're describing a formula for A2 that is equivalent to: =Use_A1's_formula_adjusted_for_here. Excel doesn't have a mechanism to dynamically use another cell's formula by referencing that cell. Each cell needs its own formula (which can reference the result of another cell). You can copy/paste another cell's formula, and you can auto-update that if the formula changes (a VBA solution like Scarlet Manuka's). If you have a recent version of Excel, FORMULATEXT will give you the text of the formula in another cell, although you can't execute that like a formula. – fixer1234 Aug 5 '15 at 0:24
1

There's no way to do this natively with Excel worksheet functions. If you need this functionality, you'll have to use VBA.

You can use the Worksheet_Change() event. This will fire when the cell contents are changed, but not on a simple recalculation. It has a parameter which indicates the cells that have been changed; you can use this to see if you need to do anything. This code needs to go in the code area for the worksheet you're using. (Open VBA with Alt+F11; in the left panel, double click on the worksheet. You should see something like "Microsoft Visual Basic - filename - [sheetname (Code)]" in the titlebar.)

Obviously, update the sheet name and ranges as required in the below code.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim test As Range, src As Range, dest As Range

    Set src = Worksheets("Sheet1").Range("A1")
    Set dest = Worksheets("Sheet1").Range("A2")
    Set test = Intersect(Target, src)
    If Not (test Is Nothing) Then
        dest.Formula = src.Formula
    End If
End Sub

The above code will copy the formula exactly - if you refer to cell F3 in cell A1, you'll have a reference to cell F3 in cell A2 as well. If you don't want this to happen, change the line inside the If statement to dest.FormulaR1C1 = src.FormulaR1C1. This will alter any relative references in your formula appropriately.

You can change dest to a larger range if you want to set a bunch of cells at once based on the formula in cell A1.

One note is that this won't automatically restore the formulas in the cells if you change those cells - i.e. if you change the formula in A2, this won't automatically put it back, because we're only doing the update when cell A1 changes. If you want to protect against this, change the line

Set test = Intersect(Target, src)

to

Set test = Intersect(Target, Union(src, dest))
0

You can achieve it with tables (from Excel 2007: select your range then: Insert - Table; first row need to contain titles).
Any time you change a formula it'll be updated in the whole column (or you'll be asked whether you want it to be updated if formula in the column wasn't the same before change).

Update

Here you find detailed description how to create calculated column in a table.
Now if you change the formula in any of the cells the whole column will be updated.(Office support)
If that's not the case for you, make sure that "fill formulas in tables to create calculated columns" is checked in File - Options - Proofing - "AutoFormat as You Type" tab.

1
  • Can you clarify further? What contents do I have in cells A1, and A2? – Dan W Aug 4 '15 at 23:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.