Recurring formula for new records

**m.cain** · 02-21-2008, 06:36 PM

Hello,

I have an Excel table. Each row is a separate record. The user types in new records at the end of the table. One of the columns in the table contains a formula. Ideally the formula should apply for each cell in the entire column. Since I don't know how long the table will end up being, I simply ask that the user drag the formula down from the cell above. I do not want the user to accidentally change the formula though. At first I thought a Custom validation with a secret word that the user is unlikely to type in, would keep the user from changing the formula already in the cell. However, I can't stop the user from deleting the formula, which doesn't help. I then thought maybe locking (protecting with password) the column would do the trick. However, now the user can't drag down the formula from the cell above. I would therefor like help with one of 2 options:

1- have the formula apply to the cell automatically as a new record is created (ideal)
2- find a way to protect the cell so that the user can copy the formula down, but not change or delete it

Any brain waves?

I did find the following code online, which appears to go some ways to answer my first option, though I can't figure out why it only works if the formula is in column A (i.e. I have formulas in columns A, B, K, L, and AF to DL).

Please Login or Register  to view this content.

**stevekirk** · 02-21-2008, 07:41 PM

hi,

what i did to overcome this was insert rows using a button
protect your worksheet except for the cells that need input

if yor formulars are in row 30 then in iv 29 type "input rows"
then change the code that says "iv 15" to iv 29

note: my password is rainbows you need to change it

code for inserting rows is

Please Login or Register  to view this content.

hope this helps
steve

**Leith Ross** · 02-21-2008, 07:46 PM

Hello M.Cain,

Add this macro to the worksheet whose formula is to be copied to the next record. Change the column letter, shown in blue in the code, to match the one you will be using.

Please Login or Register  to view this content.

How to Save a Worksheet Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Change the column letter now.
6. Save the macro in your Workbook using CTRL+S

Sincerely,
Leith Ross

**m.cain** · 02-21-2008, 08:16 PM

Thank you stevekirk and Leith Ross.

Before I attempt what appears to be the more complicated method (stevekirk), I have tried Leith's method, but nothing seems to happen at all.

Perhaps I should clarify further that the first 7 rows are header rows, row 8 contains formulas (where applicable) and columns, A, B, K, L and AF to DL contain columns with pre-set formulas which I want copied down whenever a user enters data into a new row (i.e. in any one of the other columns).

Once again, thanks for your help.

**stevekirk** · 02-21-2008, 08:19 PM

hi,

can you attach a sample

steve

**stevekirk** · 02-21-2008, 08:30 PM

hi,

insert 5 rows after row 7 put all your formulars in row 13
in row 12 in cell iv 12 put the words insert row.

protect your workbook and change my macro to suit your password . this is in 2 places.

put data in to rows,8,9,10 for example then run the marco. it will ask you how many new rows do you want to insert . if you say 5

it will insert that many rows and your formular will move down 5 rows

steve

**m.cain** · 02-21-2008, 08:34 PM

Ok, I apologise. Leith's code does appear to work (I simply forgot to change the 2 occurrences of the letter D) though it is a bit trigger happy. I need only click on a cell (not necessarily enter anything in) and the formula will be copied down.

Secondly, how would I then apply it for each all of my other columns with formulas? would I actually need to recopy the same code X number of times for X columns with formulas?

I'm attaching my file as per stevekirk's request.

**Leith Ross** · 02-21-2008, 08:45 PM

Hello M.Cain,

I revised the macro to fill down for the all the columns you mentioned and made it so it isn't so trigger happy. Install this the same way as before, and then delete the previous macro code before saving the changes.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**stevekirk** · 02-21-2008, 09:12 PM

hi,

Leith Ross way is better as i thought it was not quite like it is

but have a look anyway

steve

**stevekirk** · 02-21-2008, 09:38 PM

hi leith,

i could not get your macro to work

is there an "end with" missing ?

steve

**m.cain** · 02-22-2008, 10:52 AM

Hello Leith,

The code does seem to work a bit better now. I followed steve's advice and added the "end with" statement.

One quirk I found, however, was that once a row has been added, I can not remove it. The automatic fill down appears to be permanent. Is there an easy way to remedy this?

I really appreciate all your great help.

**baberb** · 10-05-2012, 10:16 AM

I was able to change my cell range to a 'Table', you know where it automatically highlights the rows alternating colors of blue - adding a new line automatically propagates any formulas from the line above!