MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Is there any function eqivalent to "use the above cell"

Posted by Trish mangan on January 07, 2002 9:01 AM

I am creating a simple check register for 2002. I cannot predict the number of items the person will enter for each day. The user wants me to create 8 rows for each day and wants to remove any blank rows when she does the entry.

Here is a mock up of the model so far

Starting balance: 500

Date Desc cat1 cat2 total
1/2/2002 Desc 150 200 300 =startbal+sum(items)
1/2/2002 Desc 100 0 200 =cell above+sum(items)

The problem is if she deletes the extraneous row, the #ref error displays - this makes my unsophisticated user nervous and she tends to enter hard numbers rather than recopy the forumulas.

Basically - I want a way to say "always use the cell above" - currently I use a relative cell reference, but that doesn't work when the rows are removed.

Any suggestions?


Posted by Mark W. on January 07, 2002 9:17 AM

If =OFFSET(A2,-1,) is entered into cell A2 (and
copied down as needed) it will always refer to
the cell above regardless of row deletions.