MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Updating sales figures.


Posted by Kirk Hornocker on February 14, 2002 4:33 PM

I am an excel newbie so bear with me!!
I have a worksheet of sales figures that I update by inserting a row, then using the data form, add my new information. The problem is that my cell range names( and their values don't reflect the new, larger cell range) i.e. B2:C25 doesnt change to B2:C26. Does this make any sense???
Should I be using a pivot table???


Posted by Dreamboat on February 14, 2002 7:19 PM

Pivot tables are horrible. I hate them. LOL.

Try this instead. Let's say you've got your sales figures in column C. In C1 put "Total", in C2 put =sum(C6:C65536), where C5 is your first sales figure. Then, you can put "Sales" in C4 and the first amount in C5 and no matter how far down you go, your totals will always work. Better yet, do your sums on a sheet you call "Summary". Then you could have "Total Sales" in A1 and in A2, put:

=sum(sheet2!C:C)

Hope this helps!

Posted by Greg on February 14, 2002 7:21 PM

The DATA FORM will not adjust your named range. In order to include your new data within the named range you will probably need to insert a new row between rows 2 to 25 (using your example) and then type the data in to the new row. This may require you to sort the data so that the new row ends up at the bottom of your data if that is required.