Automatically add rows

[mjhopler]

I have the attached file. This will be one sheet in a large template. Range B10:B16 has formulas, and people will be entering data in range C10:I16. The problem with this, is some times, the template may only need a few rows, other times it may need 100 rows. Since formulas are in the table, I don't want people just inserting rows and coping down the formula, but rather have a button they push that automatically inserts a set number of rows (say 5) that copies down any formulas.

For example, I would like to push a button to add 5 rows to the table on the attached to change the table range from B8:I16 to B8:I21 with all formatting and formulas automatically filling in.

Can someone help me with this. Note, the technology I learn here will be used on multiple tables throughout the entire template.

[leaning]

mjhopler,

I am hoping you get an answer to your post. I just posted one extremely similiar:

http://www.excelforum.com/excel-gene...-in-excel.html

so, the experts may get two birds with this one.



V/R

Lost

[modytrane]

see attached file. first you have to enter data in C12:C16 and then as you enter datat in C17 and below, excel will add formula in B column.
one way to do this is to format your data as a table.
then excel will automatically add formulas when you enter data in cells C17 and below.
modytrane.

[leaning]

Modytrane,

1. How is it adding rows like that? I can't see any VBA behind the sheet or workbook....?

2. When you clear all the data in a row, can you get it to delete that row of empty cells?

Good stuff!

V/R

Lost (not the OP)

[jgomez]

Insert table, select data for table... and there you go.

[jgomez]

and to answer your 2nd question... Nope.

[leaning]

Found the answer to #1. Modytrane created an "Excel List". Data>List>Create List.

If you need more data rows, it automatically inserts them within the list table (like a continuous subform)

Here's info on it: http://office.microsoft.com/en-us/ex...001031716.aspx for Excel 2003.

I just can't find a way to delete rows if they aren't needed. I am thinking of just having a button to delete blank rows within the list table range.

??

V/R,

Lost

[mjhopler]

How can you do this without a table? I have worked in a file before that had a button when pushed, it would add rows with the formulas. This really would be more ideal in my case. I am trying to build this for others to use and need it as user friendly as possible. They too have use this button, so it would not be new to them. However, I cannot locate the old file with the macro language to repeat.

thanks,