advancing formulas copying new

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Advancing formulas when copying to new cell | Excel forum

Advancing formulas when copying to new cell | Excel forum

resolvedResolved · Medium Priority · Version 2016

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:

Kind regards
Marius Barnard

RE: Advancing formulas when copying to new cell

Hi Mike,

I accidentally gave you the incorrect email address. The correct address is


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.

Tue 9 Oct 2018: Automatically marked as resolved.


Excel tip:

Finding cells that have data restrictions

Click anywhere on the worksheet.
On the Edit menu, click Go To.
Click Special.
Click Data validation.
Click All.

View all Excel hints and tips

Server loaded in 0.08 secs.