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?

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'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?

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?

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?