Relative references in formulas

[MissAJ]

I'm using Excel 2003, Windows XP. In an 8 row chart that I've created on my spreadsheet, all the IF formulas in the first row refer back to cell A1, the next row to A11, the next to A21, and so on. I want to copy and paste this chart again a few rows below the first one (for printing purposes) and have the formulas in the first row now refer to cell A2, the second to A12, the third row to A22, and so on. Is there any way to accomplish this without manually changing each formula?

 

[Dufus]

MissAJ,

There's more than one way to skin a cat so, here goes.

1) Right click on the sheet tab at the bottom of the worksheet and select "Move or Copy" to create a copy of the sheet in question.
2) In the newly created copy, insert a row above A1. This will force all the references to the IF formulas to increment by one becoming A2 instead of A1 and so on.
3) Select all the rows that have the A2 references that you want to be below your old references on the other sheet.
4) Do Edit>Replace... from the Excel menu and in the "Find What" field put an equals sign = . In the "Replace With" field put an equal sign preceded by an apostrophe '= . This will turn all the selected formulas into text entries.
5) Copy all the rows that have the A2 references that you just turned into text, go to the original sheet and paste them where you want them.
6) Do Edit>Replace... again and this time replace '= with = . This will turn the text back to formulas. You should be done.

Dufus

 

[macajm]

you can accomplish this by using the INDIRECT function to replace the cell reference in your IF ...

eg by setting up a column adjacent to your 8 rows of chart data cells - (say as inserted column B) and in the successive rows of column B enter 1 then =B1+10 and copy the last formula down for the remaining 6 rows so that you get 1,11,21,31,41 etc

Then replace the reference in your IF function from A1 to become INDIRECT("A"&B1) which will translate to A1
You can then copy that formula down and it will effectively become A11, A21 etc

Then you can copy your whole 8 rows and paste somwhere else for the next chart , except that you have to change the first new cell to the value 2 instead of 1, so that the INDIRECT references become A2, A12, A22 etc

 

[MissAJ]

This is an ingenious way around my problem! Thanks so much for your idea. I can't wait to try it. Your screen name is cute, but it certainly doesn't apply.

 

[MissAJ]

My reply was to the answer from "Dufus". Thanks to macajm as well.

 

[Dufus]

It's a pleasure to help a real teacher.

 

[MissAJ]

To Dufus:

I just tried your idea. All worked well until I tried to do the Replace to make the text back into formulas. Even though I could see '= in the front of each line very plainly, when I selected the lines and set the Replace to find '= and replace it with =, it said it couldn't find any '= in those lines.

 

[Dufus]

Sorry, instead of putting an apostrophe in front of the equal sign, put an exclamation point.

Truly,
Dufus

 

[MissAJ]

Dufus,
Thanks for the "improved" idea. I'm sure that will cure the problem.
It's also a pleasure to get real help from a kind stranger.

Sincerely,
MissAJ

Questions Answered at http://www.mwtb.org/pages/QA.php

 

[MissAJ]

Dufus,
The exclamation point worked well. Thanks again.

MissAJ