If formula copied down

**pauldaddyadams** · 12-17-2009, 06:05 PM

I have the attached spreadsheet and in column L in contains a very simple If formula. I have copied it down but is there a way I can use VBA to put the formula in automatically. I wouldn’t want to have to press a button for it to then enter the formulas, I would want excel to do it automatically as soon as data is put in column C

The code would need to find the last cell that contains data.

**teylyn** · 12-17-2009, 06:47 PM

I've just entered some new data in row 15 of your sheet and Excel automatically filled in the formula in column L.

See if your setup has the following tick box checked:

Tools - options - Edit tab - Enable AutoComplete for cell values

As an alternative, in XL2003 you can make your data table into an Excel List, (click somewhere in the table, then go Data - Lists - Create List
This will put a blue border around the data table and when you enter a new row at the bottom or insert a row in between, formulas and formatting will be applied as in the rest of the list. In Excel 2007 you can insert a Table.

Your profile does not state what version of Excel you're using. You may want to provide that information if you need more help.

**Leith Ross** · 12-17-2009, 06:50 PM

Hello pauldaddyadams,

The macro below will add the "If" formula to the row when the cell in column "C" changes. This has been added to the attached workbook.

Please Login or Register  to view this content.

**JBeaucaire** · 12-17-2009, 06:51 PM

I vote for the "List" feature as well. Very, very handy function once you start using it.

**pauldaddyadams** · 12-18-2009, 05:03 AM

I would rather not use a list as the spreadsheet will be creating new sheets automatically and each list range will vary.

Regarding the VB Code - i couldnt get it to work?

Also looking at the formula I believe it will place the wrong formula, I need

Please Login or Register  to view this content.

**teylyn** · 12-18-2009, 05:06 AM

I would rather not use a list as the spreadsheet will be creating new sheets automatically and each list range will vary.

Are you sure you understand the Excel 2003 List concept? You may want to consult the Excel Help files to see that each sheet can have its own list and that it's quite dynamic.

**royUK** · 12-18-2009, 05:12 AM

Also, in the Tools ->Options->Edit tab make sure that "Extend data range formats & formulas" is checked. This should do what you want in all tables. No point in using VBA when Excel has inbuilt functions to achieve the result

**pauldaddyadams** · 12-18-2009, 05:32 AM

I got the list feature to work thanks, Ill look into weather I can use it on my spreadsheet or not.

Is there a way I can have a macro so that the user form can be brought up ready for new entries? I tried:

Please Login or Register  to view this content.

But this didnt work.

Also, I have two date fields. Is there any way I can have a pop up calendar rather than for the user to input manually? I have a pop up calendar on a different part of the spreadsheet so the form is already created, its called frmcalendar