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?

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
wrote in message
...
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?

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?

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

"Zone" wrote:

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
wrote in message
...
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?

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"
wrote in message
...
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?

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.

"Mike Fogleman" wrote:

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"
wrote in message
...
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?

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?

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" wrote in message
:

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?

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"
wrote in message
...
Have a VBA code that will always use the last line! Perfect!

"Zone" wrote:

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
wrote in message
...
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?

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

Zone wrote:
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"
wrote in message
...
Have a VBA code that will always use the last line! Perfect!

"Zone" wrote:

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
wrote in message
...
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?

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

"Zone" wrote:

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

Zone wrote:
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"
wrote in message
...
Have a VBA code that will always use the last line! Perfect!

"Zone" wrote:

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
wrote in message
...
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?

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

"Zone" wrote:

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"
wrote in message
...
Have a VBA code that will always use the last line! Perfect!

"Zone" wrote:

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
wrote in message
...
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?

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?

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!

"JMB" wrote:

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?

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.



"Looking for an easier way" wrote:

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!

"JMB" wrote:

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?

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
"JMB" wrote in message
...
Something I learned from T. Valko (aka "Biff"). <g

"Zone" wrote:

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"
wrote in message
...
Have a VBA code that will always use the last line! Perfect!

"Zone" wrote:

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
wrote in message
...
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?

JMB, got my replies mixed up, pardon. Please ignore my question. James
"JMB" wrote in message
...
Something I learned from T. Valko (aka "Biff"). <g

"Zone" wrote:

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"
wrote in message
...
Have a VBA code that will always use the last line! Perfect!

"Zone" wrote:

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
wrote in message
...
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?