Mike has attended:
Excel Intermediate course
Excel Advanced course
Advancing formulas when copying to new cell
I am trying to set up formulas for a simple calculation between two worksheets but having issues working out how to control the advancement when copying them.
Eg: In Worksheet 1, cell A1 should display the results of:- =(Sheet2!C2-Sheet!C3)
This works fine. However, I have other columns in Sheet 1 that need to be ignored therefor the next calculated cell should be:
Sheet1!A3 displays:- =(sheet2!c4-sheet!c5)
The problem is that because Sheet1 has columns and rows that need to be ignored, when I copy the formula it advances the reference by the number of cells on sheet 1 instead of on sheet 2.
I can lock the formula to either the row or column of sheet 2 but since they both change eventually it forces me to manually edit one or the other introducing the risk of human error.
I need function like $ that tells the formula to use the structure of sheet 2 (e.g. advance 1 column only) no matter how far it moves on sheet 1 when copied.
RE: Advancing formulas when copying to new cell
Hello Mike,
Thank you for your question. For us to get the full context of your data, would it be possible to email us with some sample Excel data and formulas and perhaps a comment or two to clarify exactly what should happen?
Please email us at:
forum@stl-training.co.uk
Kind regards
Marius Barnard
STL
RE: Advancing formulas when copying to new cell
Hi Mike,
I accidentally gave you the incorrect email address. The correct address is info@stl-training.co.uk
Apologies!
Marius Barnard
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
