Adding new rows including formula

E

Edward Robson

I want to create a spreadsheet to allow some resource scheduling. The
cells in the rows will contain a number of formula such as lookups
etc. I want to allow the user to insert a new row but for the row to
include the formula etc. I'm sure this must be fairly easy but I just
can't see it. Hope someon can point me in the right direction.
 
D

David McRitchie

Hi Edward,
I don't know about easy, I had help after obtaining the
closest solution from the archives and then from getting the missing
pieces from the newsgroup.

Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

There are several different macros depending on what you
really want/need.
 
R

Ron de Bruin

Another option maybe is

John's DataForm
http://j-walk.com/ss/dataform/


--
Regards Ron de Bruin
http://www.rondebruin.nl


David McRitchie said:
Hi Edward,
I don't know about easy, I had help after obtaining the
closest solution from the archives and then from getting the missing
pieces from the newsgroup.

Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

There are several different macros depending on what you
really want/need.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Edward Robson said:
I want to create a spreadsheet to allow some resource scheduling. The
cells in the rows will contain a number of formula such as lookups
etc. I want to allow the user to insert a new row but for the row to
include the formula etc. I'm sure this must be fairly easy but I just
can't see it. Hope someon can point me in the right direction.
Click to expand...
Click to expand...
 
D

DDM

Edward, if you have Excel 2000 or later, you can use the "Extend list
formats and formulas" feature. Tools > Options > Edit. Check the Extend list
formats and formulas checkbox. The idea is that if a user adds a row to the
bottom of a list Excel will automatically copy the formulas and formats
down, provided those formulas appear in at least three of the last five
rows.

You should also encourage users to use Excel's built-in data form (Data >
Form) for data entry. This ensures that the data is added to the bottom of
the list and that the formulas will be copied.

Finally, you may want to investigate a VBA solution. Here is a good place to
start: http://www.mvps.org/dmcritchie/excel/insrtrow.htm.
 
E

Edward Robson

DDM said:
Edward, if you have Excel 2000 or later, you can use the "Extend list
formats and formulas" feature. Tools > Options > Edit. Check the Extend list
formats and formulas checkbox. The idea is that if a user adds a row to the
bottom of a list Excel will automatically copy the formulas and formats
down, provided those formulas appear in at least three of the last five
rows.

You should also encourage users to use Excel's built-in data form (Data >
Form) for data entry. This ensures that the data is added to the bottom of
the list and that the formulas will be copied.

Finally, you may want to investigate a VBA solution. Here is a good place to
start: http://www.mvps.org/dmcritchie/excel/insrtrow.htm.
Click to expand...

These options look promising. The built in option of having Excel do
the work is the first thing I'll try. The assistance is much
appreciated
 
Top