Creating auto functioning worksheets

[Guest]

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?

 

[Zone]

Are you wanting to automatically update formulas on the worksheet? Or have
VBA code that will always use the last line? James

"Looking for an easier way" <Looking for an easier
(e-mail address removed)> wrote in message
news[email protected]...

 

[Guest]

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.

 

[Guest]

Have a VBA code that will always use the last line! Perfect!

 

[Mike Fogleman]

The formula itself can be re-written to the cell with VBA or you can have
the code evaluate the formula and put the results in the cell. There would
be no formula on the spreadsheet that way. In either case, where is the
formula? Or are there several formulas?

Mike F
"Looking for an easier way"

 

[Guest]

I can send out the spreadsheet to you and you can have a look if that would
help. I've never used VBA, and am rarely creating this type of spreadsheet.
I bascially am a sales rep looking to make my life easier and keep up with my
daily numbers. I need a history and an updated trend. I know it's probably
an easy fix for someone who knows what they're doing. I can email it to you
directly if you like.

 

[Guest]

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

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

 

[JMay]

Say Column A is where you start with new row of data-entry:
First Insert a NamedRange -- Lastrow and in the refers to box paste
in:

=COUNTA(Sheet1!$A:$A)-1 << assumes 1 row = header

Then you can use:

=INDIRECT("A"&LastRow+1)

It will always be current;
Is this what you mean?
HTH

 

[Zone]

JMB's solution appears to be very elegant. Nice, JMB. Following JMB's
advice, I replaced your formula with this:
=((LOOKUP(MAX(C:C)+1,C:C)/L1)*B7)-F2
Seems to work perfectly.
HTH,
James
"Looking for an easier way"

 

[Zone]

Easier,
Since you seem interested in a VBA solution.... What you want here
is for the formula to always be current up to the last row entered.
This would require user-defined function code in VBA. I played with
creating a UDF but could not come up with anything nearly as elegant
and automatic as JMB's solution. James

 

[Guest]

THanks so much for your help. It does seem that this solution that JMB
created is working. I've tested it a few different ways quickly and seems to
work. It will be easier to see on as the weeks progress. Thanks thanks and
thanks again

 

[Guest]

Something I learned from T. Valko (aka "Biff"). <g>

 

[Guest]

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.

 

[Guest]

Totally worked. I assumed (call me an ass that the max function was
taking the highest in the column. I was wrong and it worked beautifully. I
will thank you everyday I work with those numbers. That will be at least 252
times in 2007. THANK YOU! THANK YOU! THANK YOU! for making my life easier!

 

[Guest]

No, you are correct, the max function does take the highest in the column,
but it adds 1 to it and feeds it to the lookup function. Since the value it
is trying to look up is greater than all of the values in the column, it ends
up returning the last value in the column.

 

[Zone]

JMB, if I might ask one more question. Suppose I want to INDEX to show the
value of the last cell in column C. What is the syntax for this? TIA,
James

 

[Zone]

JMB, got my replies mixed up, pardon. Please ignore my question. James