Relative references in formulas

MissAJ

Board Regular
Joined
Sep 22, 2006
Messages
86
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?
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
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

New Member
Joined
Oct 21, 2005
Messages
32
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

Board Regular
Joined
Sep 22, 2006
Messages
86
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

Board Regular
Joined
Sep 22, 2006
Messages
86

ADVERTISEMENT

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

MissAJ

Board Regular
Joined
Sep 22, 2006
Messages
86

ADVERTISEMENT

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

Board Regular
Joined
Aug 19, 2006
Messages
176
Sorry, instead of putting an apostrophe in front of the equal sign, put an exclamation point.

Truly,
Dufus
 

MissAJ

Board Regular
Joined
Sep 22, 2006
Messages
86
Dufus,
The exclamation point worked well. Thanks again.

MissAJ
 

Forum statistics

Threads
1,144,369
Messages
5,723,950
Members
422,527
Latest member
TotalBeginner201

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top