I need to +1 my formulas e.g. change ='Jan 2013'!BD3 into ='Jan 2013'!BD4

[norani]

So I have a big excel file. Sheets 1-5 are titled Jan-March to reflect the months of the year that I take skinfold measurements of the athletes. They are full of raw data like athletes' names, weights, skinfold measurements, girths etc.

The I have sheets X1-X32. Each of these sheets generates an individual profile for the athletes with their name, dob, average skinfolds, girths, sum of 7 skinfold, and some pretty graphs to show their weight going up and their skinfolds coming down.

So my sheet X1 has all of the formulas in it e.g.
='Jan 2013'!AS2
='Feb 2013'!AS2
='Mar 2013'!AS2

But on sheet X2 I need the source of all of the formulae to go down a row so they would be:
='Jan 2013'!AS3
='Feb 2013'!AS3
='Mar 2013'!AS3
Other than doing this manually which would take forever with 32 sheets is there a macro or something I can do to change all of the formulae on the sheet?

 

[RoryA]

Hi, and welcome to the forum.

It's hard to be sure without knowing the layout of the sheets, but I suspect there's a better formula, such as using INDEX and MATCH to locate the athlete name on each month's sheet. Otherwise you could always use Find/Replace to replace the AS3 with AS4 on the first sheet and repeat for the others, which shouldn't take too long.

 

[norani]

Hi, and welcome to the forum.

It's hard to be sure without knowing the layout of the sheets, but I suspect there's a better formula, such as using INDEX and MATCH to locate the athlete name on each month's sheet. Otherwise you could always use Find/Replace to replace the AS3 with AS4 on the first sheet and repeat for the others, which shouldn't take too long.

Hi Rory, Thanks a millon for your reply. When I click find: 3 replace: 4 it doesn't find anything because the formulae are represented as #DIV/0! on the sheet not as their formula.
I want to change the formulae not the values. Any other suggestions?

 

[RoryA]

Replace will change formulas (it couldn't replace their results). You need to make sure that you either have only one cell selected, or all the cells in which you want to do the replacing. Also check the options to make sure it's not trying to match entire cell contents.

 

[shyy]

If you don;t mind making your sheets look different and just hiding the top blank rows, you can use the INDIRECT and ROW function to return the Row # and make that cell an absolute value.

maybe something like =INDIRECT('Jan 2013'!AS"&ROW($B$2)) for ='Jan 2013'!AS2
=INDIRECT('Jan 2013'!AS"&ROW($B$3)) for ='Jan 2013'!AS3