From what I can tell, the lookup formula will return the last number in
column C regardless of whether it is positive or negative (am assuming there
is no other data in column C below your table). Is that what you are
wanting? Perhaps post a sample of your data and what the result should be.

"Looking for an easier way" wrote:

That totally works with only one exception. If there is a credit and the
daily number is less than the previous one, then there's a hole. But that
doesn't happen very often and works pretty darn well. Especially as i really
know nothing about VBA really

"JMB" wrote:

A possible non-VBA solution - try replacing C9 with:

LOOKUP(MAX(C:C)+1,C:C)


"Looking for an easier way" wrote:

Every day I add my daily sales numbers into a table as a row. I want to
maintain the trends & I use this formula

= ((C9/L1)*B7)-F2

C9 is the row that changes everyday. I have to change C9 to the next line on
several fomulas (C10 the next day; C11 the day after that and so on). I
manually have to go and delete the 9 and make it 10 every day. I want to
elimanate that step to make it easier so as I add the next row the next day
this formula changes with it.

C9 is the daily number; L1 is an input; B7 is a fixed number based on how
many days are in that month and F2 is a input that is updated based on L1.


"Alok" wrote:

I am not quite sure of your actual situation as you have not provided details
but one way of freezing a starting cell refrence is by using Indirect as in
the following:

=SUM(INDIRECT("C7"):C17)

Here the starting cell will remain C7 even if you insert row 7. C17 will of
course change to C18.

Alok

"Looking for an easier way" wrote:

I'm working on a worksheet that I update daily with new numbers. A new row
daily.
Everytime I add a row I have to go back and update all my trends(formulas)
with correct reference numbers for the new line (such as the reference C7
becomes C8; then next day I fill out the row 9 and C8 becomes C9 and so on).
I know there's a way to program it to auto calculate, but I have no idea how
to do it.

Suggestions?