Problem with formulas changing cell reference

[janicesweet]

I have a workbook with 2 spreadsheets. The first spreadsheet has
cell which contains the last day of the month. The 2nd spreadshee
has some heading rows, a row of formulas across the top (Row 5),
blank row and then monthly table of data values starting in row
with the last day of the month in the first column. The most recen
month is in row 7. The formulas across the top are VLOOKUPs whic
go out and pickup the last day of the month from the firs
spreadsheet using an absolute cell reference $A$1. The range of th
VLOOKUP has mixed cell references (e.g., $A$7:$Z50). It returns th
corresponding cell for the requested date. When I add a new month i
the 7th row, I'm selecting the 7th row and then inserting a new row.
When I do this, my absolute cell references in the formulas ar
changing from $A$7:$Z50 to $A$8:$Z51. I thought absolute cell
weren't supposed to change. I expect and want the ending row numbe
to change but not the starting one. What am I doing wrong and wh
is the cell reference changing

 

[Karthik]

Hi Janice

When you select row 7 and insert a new row, what you are doing is
moving all the cells below, i.e you are moving your range $A$7:$Z$50
too.

Now coming to your problem. Do this : keep your row 7 blank and have
data from row 8 to 51( your vlookup array should be($A$7:$Z51) . Next
time you want to add a new month, select row 8 instead of 7 and inset a
new row. And you have what you wanted. The new range in your formula
has changed from $A$7:$Z51 to $A$7:$Z52.

What we have just done is insearted a row which is within the range of
rows 7 to 51 and therby our range automatically expands.

Thanks
Karthik Bhat
Bangalore