Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
janicesweet
 
Posts: n/a
Default 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   Report Post  
Karthik
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Return cell reference of lookup value bobm Excel Worksheet Functions 3 July 7th 05 08:49 AM
changing the formula's row reference redb Excel Discussion (Misc queries) 5 April 26th 05 08:37 PM
HOW DO I COPY FORMULA WITHOUT CHANGING CELL REFERENCE anantth Excel Discussion (Misc queries) 4 February 6th 05 01:25 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM
name of another worksheet in cell for reference Tom A Johnson Excel Worksheet Functions 2 November 12th 04 12:28 AM


All times are GMT +1. The time now is 03:39 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"