Auto calculate formulas when new data is entered
 

I have created a data set that is updated every month with new prices. The
prices are entered into a cell, say A1, then the next 3 cells (B1:D1) to the
right use different formulas with that data to give me information I need. I
was wondering if there was a way, so that when I enter new data in A2 for the
next month, the 3 cells to the right of A2 will automatically apply the
formulas from B1:D1 (into B2:D2). Under ToolsOptionsEdit the 'Enable
AutoComplete for Cell Values' and 'Extend Data Rage Formulas and Format' are
checked off. Also, under the ToolsOptionsCalculations tab the 'Automatic'
is also checked off.

Any help on this would be great appreciated, as I am great confused!

Thanks!

Auto calculate formulas when new data is entered
 

From Excel HELP:

Note In order to be extended to new rows in the range, the formats and formulas must appear in at least three of the five
preceding rows.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Cam1234" wrote in message ...
|I have created a data set that is updated every month with new prices. The
| prices are entered into a cell, say A1, then the next 3 cells (B1:D1) to the
| right use different formulas with that data to give me information I need. I
| was wondering if there was a way, so that when I enter new data in A2 for the
| next month, the 3 cells to the right of A2 will automatically apply the
| formulas from B1:D1 (into B2:D2). Under ToolsOptionsEdit the 'Enable
| AutoComplete for Cell Values' and 'Extend Data Rage Formulas and Format' are
| checked off. Also, under the ToolsOptionsCalculations tab the 'Automatic'
| is also checked off.
|
| Any help on this would be great appreciated, as I am great confused!
|
| Thanks!

Auto calculate formulas when new data is entered
 

In B1 enter =IF(A1="","",yourformula)

Same for C1 and D1

Select B1:D1 and drag/copy down as far as you wish.

If column A cell is empty, B1:C1 will look blank.

As column A gets data, these formulas will show theit results.


Gord Dibben MS Excel MVP

On Wed, 14 Feb 2007 11:58:20 -0800, Cam1234
wrote:

I have created a data set that is updated every month with new prices. The
prices are entered into a cell, say A1, then the next 3 cells (B1:D1) to the
right use different formulas with that data to give me information I need. I
was wondering if there was a way, so that when I enter new data in A2 for the
next month, the 3 cells to the right of A2 will automatically apply the
formulas from B1:D1 (into B2:D2). Under ToolsOptionsEdit the 'Enable
AutoComplete for Cell Values' and 'Extend Data Rage Formulas and Format' are
checked off. Also, under the ToolsOptionsCalculations tab the 'Automatic'
is also checked off.

Any help on this would be great appreciated, as I am great confused!

Thanks!

Auto calculate formulas when new data is entered
 

I already have 50+ entries in the data. Is there another way besides using a
function to tell excel, "when data is entered in cell 'A50', automatically do
to B:D50 what you did to B:D49?" I already have an IF function in all of
these cells, with other formulas nested within it. I would rather not have
to nest all of these functions I have already into another IF function. I
realize this is possible, but I'm just being lazy, as it would mean I would
have to change a bunch of stuff, cause I'm using this template for a bunch of
other data too!

Thanks for the quick replies!

"Gord Dibben" wrote:

In B1 enter =IF(A1="","",yourformula)

Same for C1 and D1

Select B1:D1 and drag/copy down as far as you wish.

If column A cell is empty, B1:C1 will look blank.

As column A gets data, these formulas will show theit results.


Gord Dibben MS Excel MVP

On Wed, 14 Feb 2007 11:58:20 -0800, Cam1234
wrote:

I have created a data set that is updated every month with new prices. The
prices are entered into a cell, say A1, then the next 3 cells (B1:D1) to the
right use different formulas with that data to give me information I need. I
was wondering if there was a way, so that when I enter new data in A2 for the
next month, the 3 cells to the right of A2 will automatically apply the
formulas from B1:D1 (into B2:D2). Under ToolsOptionsEdit the 'Enable
AutoComplete for Cell Values' and 'Extend Data Rage Formulas and Format' are
checked off. Also, under the ToolsOptionsCalculations tab the 'Automatic'
is also checked off.

Any help on this would be great appreciated, as I am great confused!

Thanks!

Auto calculate formulas when new data is entered
 

Post the formulas you have in B1:D1

There may be an easy way to change them to add the "" if Ax is blank.


Gord

On Wed, 14 Feb 2007 13:04:03 -0800, Cam1234
wrote:

I already have 50+ entries in the data. Is there another way besides using a
function to tell excel, "when data is entered in cell 'A50', automatically do
to B:D50 what you did to B:D49?" I already have an IF function in all of
these cells, with other formulas nested within it. I would rather not have
to nest all of these functions I have already into another IF function. I
realize this is possible, but I'm just being lazy, as it would mean I would
have to change a bunch of stuff, cause I'm using this template for a bunch of
other data too!

Thanks for the quick replies!

"Gord Dibben" wrote:

In B1 enter =IF(A1="","",yourformula)

Same for C1 and D1

Select B1:D1 and drag/copy down as far as you wish.

If column A cell is empty, B1:C1 will look blank.

As column A gets data, these formulas will show theit results.


Gord Dibben MS Excel MVP

On Wed, 14 Feb 2007 11:58:20 -0800, Cam1234
wrote:

I have created a data set that is updated every month with new prices. The
prices are entered into a cell, say A1, then the next 3 cells (B1:D1) to the
right use different formulas with that data to give me information I need. I
was wondering if there was a way, so that when I enter new data in A2 for the
next month, the 3 cells to the right of A2 will automatically apply the
formulas from B1:D1 (into B2:D2). Under ToolsOptionsEdit the 'Enable
AutoComplete for Cell Values' and 'Extend Data Rage Formulas and Format' are
checked off. Also, under the ToolsOptionsCalculations tab the 'Automatic'
is also checked off.

Any help on this would be great appreciated, as I am great confused!

Thanks!

Auto calculate formulas when new data is entered
 

Hey Gord,

Here's the functions:

In C9: =IF($B9="NP","NP",$B9-AH9) ; in D9:
=IF($B9="np","NP",(C9-$C$9)*2.20465).

These formulas compute off of info from B9. This setup continues all the
way down to row 56.

Another problem might be that I am just designing this, and someone else
will ultimately end up using it. I want it to be simple to use, so that all
the person has to do is enter the new data, and the rest is done for them
(then hopefully they wont have to go through what I am!). If I was to use a
formula to solve my problem, would I not have to copy this formula into a
bunch of rows below so that every month the person did not have to re-add the
formulas? I was hoping that all they would have to do is right click on the
bottom row, insert a row, then add the data.

Thanks for all your help Gord... greatly appreciated!

Cam

"Gord Dibben" wrote:

Post the formulas you have in B1:D1

There may be an easy way to change them to add the "" if Ax is blank.


Gord

On Wed, 14 Feb 2007 13:04:03 -0800, Cam1234
wrote:

I already have 50+ entries in the data. Is there another way besides using a
function to tell excel, "when data is entered in cell 'A50', automatically do
to B:D50 what you did to B:D49?" I already have an IF function in all of
these cells, with other formulas nested within it. I would rather not have
to nest all of these functions I have already into another IF function. I
realize this is possible, but I'm just being lazy, as it would mean I would
have to change a bunch of stuff, cause I'm using this template for a bunch of
other data too!

Thanks for the quick replies!

"Gord Dibben" wrote:

In B1 enter =IF(A1="","",yourformula)

Same for C1 and D1

Select B1:D1 and drag/copy down as far as you wish.

If column A cell is empty, B1:C1 will look blank.

As column A gets data, these formulas will show theit results.


Gord Dibben MS Excel MVP

On Wed, 14 Feb 2007 11:58:20 -0800, Cam1234
wrote:

I have created a data set that is updated every month with new prices. The
prices are entered into a cell, say A1, then the next 3 cells (B1:D1) to the
right use different formulas with that data to give me information I need. I
was wondering if there was a way, so that when I enter new data in A2 for the
next month, the 3 cells to the right of A2 will automatically apply the
formulas from B1:D1 (into B2:D2). Under ToolsOptionsEdit the 'Enable
AutoComplete for Cell Values' and 'Extend Data Rage Formulas and Format' are
checked off. Also, under the ToolsOptionsCalculations tab the 'Automatic'
is also checked off.

Any help on this would be great appreciated, as I am great confused!

Thanks!

Auto calculate formulas when new data is entered
 

Cam

If you can teach your users to double-click on a cell where they want to insert
a row, David McRitchie has sheet event code that inserts a row with formulas.

http://www.mvps.org/dmcritchie/excel/insrtrow.htm

Also see here for how Excel.....not so well IMO........extends formulas and
formats

http://support.microsoft.com/default...;en-us;Q231002


Gord

On Thu, 15 Feb 2007 07:32:21 -0800, Cam1234
wrote:

Hey Gord,

Here's the functions:

In C9: =IF($B9="NP","NP",$B9-AH9) ; in D9:
=IF($B9="np","NP",(C9-$C$9)*2.20465).

These formulas compute off of info from B9. This setup continues all the
way down to row 56.

Another problem might be that I am just designing this, and someone else
will ultimately end up using it. I want it to be simple to use, so that all
the person has to do is enter the new data, and the rest is done for them
(then hopefully they wont have to go through what I am!). If I was to use a
formula to solve my problem, would I not have to copy this formula into a
bunch of rows below so that every month the person did not have to re-add the
formulas? I was hoping that all they would have to do is right click on the
bottom row, insert a row, then add the data.

Thanks for all your help Gord... greatly appreciated!

Cam

"Gord Dibben" wrote:

Post the formulas you have in B1:D1

There may be an easy way to change them to add the "" if Ax is blank.


Gord

On Wed, 14 Feb 2007 13:04:03 -0800, Cam1234
wrote:

I already have 50+ entries in the data. Is there another way besides using a
function to tell excel, "when data is entered in cell 'A50', automatically do
to B:D50 what you did to B:D49?" I already have an IF function in all of
these cells, with other formulas nested within it. I would rather not have
to nest all of these functions I have already into another IF function. I
realize this is possible, but I'm just being lazy, as it would mean I would
have to change a bunch of stuff, cause I'm using this template for a bunch of
other data too!

Thanks for the quick replies!

"Gord Dibben" wrote:

In B1 enter =IF(A1="","",yourformula)

Same for C1 and D1

Select B1:D1 and drag/copy down as far as you wish.

If column A cell is empty, B1:C1 will look blank.

As column A gets data, these formulas will show theit results.


Gord Dibben MS Excel MVP

On Wed, 14 Feb 2007 11:58:20 -0800, Cam1234
wrote:

I have created a data set that is updated every month with new prices. The
prices are entered into a cell, say A1, then the next 3 cells (B1:D1) to the
right use different formulas with that data to give me information I need. I
was wondering if there was a way, so that when I enter new data in A2 for the
next month, the 3 cells to the right of A2 will automatically apply the
formulas from B1:D1 (into B2:D2). Under ToolsOptionsEdit the 'Enable
AutoComplete for Cell Values' and 'Extend Data Rage Formulas and Format' are
checked off. Also, under the ToolsOptionsCalculations tab the 'Automatic'
is also checked off.

Any help on this would be great appreciated, as I am great confused!

Thanks!