Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Problem with formulas changing cell reference
I have a workbook with 2 spreadsheets. The first spreadsheet has a
cell which contains the last day of the month. The 2nd spreadsheet has some heading rows, a row of formulas across the top (Row 5), a blank row and then monthly table of data values starting in row 7 with the last day of the month in the first column. The most recent month is in row 7. The formulas across the top are VLOOKUPs which go out and pickup the last day of the month from the first spreadsheet using an absolute cell reference $A$1. The range of the VLOOKUP has mixed cell references (e.g., $A$7:$Z50). It returns the corresponding cell for the requested date. When I add a new month in 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 are changing from $A$7:$Z50 to $A$8:$Z51. I thought absolute cells weren't supposed to change. I expect and want the ending row number to change but not the starting one. What am I doing wrong and why is the cell reference changing? |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return cell reference of lookup value | Excel Worksheet Functions | |||
changing the formula's row reference | Excel Discussion (Misc queries) | |||
HOW DO I COPY FORMULA WITHOUT CHANGING CELL REFERENCE | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions | |||
name of another worksheet in cell for reference | Excel Worksheet Functions |