Relative formula not updating when copying

Copying formulas in Excel is one of the easiest tasks that is usually done in a mouse click.

I say "usually" because there can be very specific cases that require special tricks, like copying a range of formulas without changing cell references or entering the same formula in multiple non-adjacent cells.

You can find a version of this tip for the older menu interface of Excel here: Relative Worksheet References when Copying.

Allen Wyatt With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. Automate virtually any routine task and save yourself hours, days, maybe even weeks.

The copy worksheet function appends all cell formulas with WBA, making it "absolute" and I do not want [email protected]: But you have said that your workbook has buttons, dropdowns and such; and named ranges. Copy of Workbook and select 'move or copy' and then move these sheets to Workbook

This technique won’t copy them (or mundane things like formatting) — how/why do you consider it acceptable?

For instance, assume that cell B7 contains the following formula: When you are copying formulas from one worksheet to another, and the formula contains a reference to a previous worksheet, Excel doesn't do this type of formula updating—at least not on the worksheet names.This is done because the month names alone (January, February, etc.) could easily occur in other places in the worksheet without being part of a formula.You don't want to change these instances, so the extra characters are included to help narrow down the search.Excel treats "values" as calculated values rather than the actual formulas in the cell.If I choose paste formula, it still gives absolute references.