Retaining Formula Pairs

**coachlink** · 08-06-2009, 02:16 PM

I have a spreadsheet where each month we show a set of formulas for the three most current months. To add a new month, I hide the oldest (e.g., April), and insert a column in between the remaining two (May and June). This carries over mappings from some other graphs. I then copy/paste (June) into the new inserted column and change the other one to the current month (July).

When I do this, there is a conversion of the formula mappings for (June). For example,

Cell O5:
=(+'Data'!T6+'Data'!U6)/('Data'!T14+'Data'!U14)

after relocating becomes

Cell N5:
=(+'Data'!S6+'Data'!T6)/('Data'!S14+'Data'!T14)

but I want to keep
=(+'Data'!T6+'Data'!U6)/('Data'!T14+'Data'!U14)

It looks like Excel always assumes that you just want the next thing in sequence in the new cell. But in this case we want unique formulaic pairs, QR then ST then UV. What Excel gives me is QR then RS then TU. There's always a plus or minus one to all the references, even if I update the "Data" sheet first. The only method I've found to fix this is to manually update each set of formulas, which requires a good deal of care across some thirty cells.

Any advice is appreciated!

**NBVC** · 08-06-2009, 02:27 PM

Kind of difficult to visualize what you are doing... but does this work?

=('Data'!$T6+'Data'!$U6)/('Data'!$T14+'Data'!$U14)

**coachlink** · 08-06-2009, 02:54 PM

Yep, that's the formula I want to keep for that one line. But how do I keep it via a copy/paste? I don't want to have to retype it for every instance.

It is hard to picture. Maybe if you just try it...insert a column in between two that have forumulas and then move the rightmost item to the middle. You'll see it change. Excel is thinking it's being helpful, and in most cases that would be great.

I've played around with "Paste Special" too...maybe the key is in there? Or the Auto-Fill tool. But everything increments the formulas rather than keeping them as-is.

**NBVC** · 08-06-2009, 03:01 PM

You can move the formula instead of copying it.. it will retains it references.

Just hover over the edge of the cell or ranges until you get a black cross with 4 arrowheads, click and drag over.

**coachlink** · 08-06-2009, 03:32 PM

Cool - but I think that puts me in the same boat in a different place. I then need to copy and paste the formula to the cell I moved from, updating it with the next logical sequential pair. So if I move the TU references, I need to get VW references in the now-empty cell.

I tried this just to be sure. And if I copy from the moved TU, and paste it to the source, I get QR in the old cell. But what I want is VW. By the by, I'm using Excel 2003.

**NBVC** · 08-06-2009, 03:45 PM

Hmmm.. if you are not overlapping cells, the only the thing I can think of is to insert another blank column temporarily and copy the newly moved cell across to that new column (i.e. 2 columns over), then move (per my previous instruction back to the column you want... then delete the temporary column...