Ryan has attended:
Excel Advanced course
Excel Formula's
Is there a way to copy a formula from another cell to save having to retype/update the same formula in multiple cells or 'drag' the formula from the updated cell changing any formatting.
E.g.
Type a formula in A1 such as =A2+A3
In B1 I want =B2+B3
If I then update A1 to =A2+A3+A4
I want B1 to auto update to =B2+B3+B4
So in simple terms, I want it to say =copy the formula in cell A1 but relate to the relevant row
RE: Excel Formula's
Hi Ryan,
Thank you for the forum question.
Yes, you can achieve this in Excel using a combination of named ranges and the INDIRECT function. Here's how you can set it up:
Define Named Ranges:
Select cell A2 and name it "Row2".
Select cell A3 and name it "Row3".
Select cell A4 and name it "Row4".
Use INDIRECT Function:
In cell A1, enter the formula: =INDIRECT("Row2")+INDIRECT("Row3")+INDIRECT("Row4").
Copy the Formula:
Copy the formula from A1 to B1.
In cell B1, update the named ranges to correspond to the B column:
Select cell B2 and name it "Row2_B".
Select cell B3 and name it "Row3_B".
Select cell B4 and name it "Row4_B".
Update the Formula in B1:
In cell B1, enter the formula: =INDIRECT("Row2_B")+INDIRECT("Row3_B")+INDIRECT("Row4_B").
Now, whenever you update the formula in A1, you can easily update the named ranges in B1 to reflect the changes without having to manually adjust each cell reference
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
