Automatically filling additional cells with formula

[Sapper]

Hi, I am using excel 2003

I have created a database for personal records to be used by others.
All the necessary formula is in place.

I want the user to be able to insert anew record by adding an additional row
in the approppriate alphabetical position. Unfortuneately the new rows do
not pick up the formula from the cells above or below. I have tried using a
macro but when playing it duplicates my actions exactly which means it just
adds the same row again and again.

Is there any way to automate the inclusion of formula in additional cells
without performing drag and drop manually (which I want to avoid the user
doing)?

Thank you

 

[Luke M]

Instead of inserting a row (which can screw up some formulas occasionally),
can you have the user input information at end of table, and then have them
(or automate) a sort operation to re-alphabetize the list?

Or, instead of having user insert a row themselves, perhaps have them push a
button/run this macro after selecting where they want to insert?

Sub CopyFormulas()
ActiveCell.Offset(-1, 0).EntireRow.Copy
ActiveCell.EntireRow.Insert Shift:=xlDown
ActiveCell.EntireRow.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

 

[MyVeryOwnSelf]

Hi, I am using excel 2003

I have created a database for personal records to be used by others.
All the necessary formula is in place.

I want the user to be able to insert anew record by adding an
additional row in the approppriate alphabetical position.
Unfortuneately the new rows do not pick up the formula from the cells
above or below. I have tried using a macro but when playing it
duplicates my actions exactly which means it just adds the same row
again and again.

Is there any way to automate the inclusion of formula in additional
cells without performing drag and drop manually (which I want to avoid
the user doing)?

Check out "About lists" and "Create a list" in Excel's built-in Help. When
I use "Lists," formulas do get extended to a new row when inserting or
appending a row.

Lists didn't always behave the way I would've liked, but they may work for
your application.

 

[Sapper]

Hi Mark

thanks for providing macro that is way above my head. I can just about for
the logic of formula, but VB!

Mark, the macro works, but it also drags down non-formula ie name of last
person on the list. comments etc. Is there a way round this? Also can I
assign a shortcut key?
If not, I will live with what you've given me

Thanks again

Sapper