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
(E-Mail Removed)> wrote in message
newsC32A8D7-871E-4CDC-8449-(E-Mail Removed)...
> 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
> (E-Mail Removed)> wrote in message
> newsC32A8D7-871E-4CDC-8449-(E-Mail Removed)...
> > 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"
<(E-Mail Removed)> wrote in message
newsED8FD18-9D8E-4351-97CB-(E-Mail Removed)...
> 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"
> <(E-Mail Removed)> wrote in message
> newsED8FD18-9D8E-4351-97CB-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed):

> > 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"
<(E-Mail Removed)> wrote in message
news:F04C22CD-298A-4C3D-810E-(E-Mail Removed)...
> 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
>> (E-Mail Removed)> wrote in message
>> newsC32A8D7-871E-4CDC-8449-(E-Mail Removed)...
>> > 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"
> <(E-Mail Removed)> wrote in message
> news:F04C22CD-298A-4C3D-810E-(E-Mail Removed)...
> > 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
> >> (E-Mail Removed)> wrote in message
> >> newsC32A8D7-871E-4CDC-8449-(E-Mail Removed)...
> >> > 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?
> >>
> >>
> >>