Auto calculate formulas when new data is entered

[Guest]

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 (B11) 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 B11 (into B22). Under Tools>Options>Edit the 'Enable
AutoComplete for Cell Values' and 'Extend Data Rage Formulas and Format' are
checked off. Also, under the Tools>Options>Calculations tab the 'Automatic'
is also checked off.

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

Thanks!

 

[Niek Otten]

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

|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 (B11) 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 B11 (into B22). Under Tools>Options>Edit the 'Enable
| AutoComplete for Cell Values' and 'Extend Data Rage Formulas and Format' are
| checked off. Also, under the Tools>Options>Calculations tab the 'Automatic'
| is also checked off.
|
| Any help on this would be great appreciated, as I am great confused!
|
| Thanks!

 

[Gord Dibben]

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

Same for C1 and D1

Select B11 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

 

[Guest]

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 B50 what you did to B49?" 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]

Post the formulas you have in B11

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


Gord

 

[Guest]

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]

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.aspx?scid=kb;en-us;Q231002


Gord