Formula not adjusting to correct cell

  • Thread starter Thread starter Mike Fenton
  • Start date Start date
M

Mike Fenton

I have a 2 sheet workbook that is killing me slowly here.
The first sheet is a
summary of community service and restitution, the second
is a detailed description of restitution payments and
community service work. For each row on the first sheet,
there are 30 or so detailed columns on the second sheet.
The 2 sheets reference each other. When I copy a new
detail section on the second sheet, instead of selecting
the next row down on the first sheet, it selects the row
that corresponds with the row number on the second sheet.
Basically, it is displaying A30 from the first sheet on
row A30 of the second sheet when it should be displaying
A2 from the first sheet on A30 on the second sheet. How
can I get the formula to adjust correctly? Fill and copy-
paste have not worked
 
=Overview!E4-SUM('Detailed Rest. Account'!I3:I31)
this is the formula, I don't know if it will help or not,
this is one of the 4 that will not update correctly on
the paste. All formulas that won't paste are almost the
same.
 
You are a life saver!!! Thanks so much!
-----Original Message-----


There's no 'built-in'way of doing this. When you copy a formula and paste it
30 rows down, the relative row references will adjust by 30. 'They' don't
know that you only want them to adjust by 1. So, you have to write the
formula to calculate the reference as you want it.

As an example, suppose in Sheet2!A1 you had the formula =Sheet1!A1. When you
copy this and paste into Sheet2!A31 it becomes =Sheet1! A31, whereas you want
it to be =Sheet1!A2.
So, instead, in Sheet2!A1 you could put the formula
=OFFSET(Sheet1!$A$1,(ROW()-1)/30,0). Now when you copy this and paste into
Sheet2!A31, it references Sheet1!A2 as required.


.
Click to expand...
 
Back
Top