Formula not adjusting to correct cells

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

Mike

I have a workbook with 2 sheets
The first page is a summary page and the second is a
detailed information page. For each column on the main
(first) page, there are approx 30 cells on the detail
(second) page. However, to avoid editing every formula on
the detail page, I tried to copy down the formulas. The
formulai copy correctly copy when they reference the detail
page, however, when they reference the main page, they
advance the number of cells in the detail page rather than
advancing 1 cell down on the main page. How can I get the
formula to paste down correctly. I have tried both a fill
and paste, neither worked.
 
Hi Mike-
Not sure since I can't see your formula (I'm a visual
person} but it sounds like you need to anchor your range
with a $ like:

=vlookup(A1,'Sheet2'!$A$1:$D$100,1,0)

When you lookup into another workbook, excel automatically
does this, but not when the range is in the same workbook.
 
Maybe I can clarify it for you since the formula is just
a simple formula that adopts the wrong cell number when I
copy that section within the sheet. 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.
 
Back
Top