Formula automation

Hi all,

I want to create a formula automation so when a user enter a data
through the form that I've created, the data will go straight to my
Excel database and Excel should automatically calculate them using the
formula from the previous row. I have tried to go to
"Tools-options-edit-extend formula" and ticked the box but it won't do
anything.

Does anyone has any idea on how to do this automation thing?
Thanks a lot!

Rgds,
Marcella

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins &
Software!http://www.ozgrid.com/Services/excel-software-categories.htm **
0
5/19/2004 12:22:32 PM
excel 39879 articles. 2 followers. Follow

4 Replies
373 Views

Similar Articles

[PageSpeed] 17

Marcella, why reinvent the wheel? What you are trying to do is exactly what
the Form command on the Data menu is designed to do. So long as your data is
organized by columns and rows in a contiguous set of cells with column
labels at the top, all your users have to do is click in the data and Data >
Form. Use the form to add data, then click Close. Excel adds the data to the
bottom of the list and copies any formulas down, just as you want.

-- 
DDM
"DDM's Microsoft Office Tips and Tricks"
Visit us at www.ddmcomputing.com


"Marcella" <smileyface@mymelody.com> wrote in message
news:40ab5188$0$202$75868355@news.frii.net...
> Hi all,
>
> I want to create a formula automation so when a user enter a data
> through the form that I've created, the data will go straight to my
> Excel database and Excel should automatically calculate them using the
> formula from the previous row. I have tried to go to
> "Tools-options-edit-extend formula" and ticked the box but it won't do
> anything.
>
> Does anyone has any idea on how to do this automation thing?
> Thanks a lot!
>
> Rgds,
> Marcella
>
> ** Posted via: http://www.ozgrid.com
> Excel Templates, Training, Add-ins &
> Software!http://www.ozgrid.com/Services/excel-software-categories.htm **


0
DDM
5/19/2004 2:27:37 PM
Hi Marcella,

You can use a macro to copy the previous row, and remove the
constants.     For an example of a stand alone macro that you
would only use parts of see
   Insert a Row using a Macro to maintain formulas
   http://www.mvps.org/dmcritchie/excel/insrtrow.htm

The following event macro will insert a new row below and copy formulas.
Rework the code for your macro by changing Target.   to your own range.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Cancel = True   'Eliminate Edit status due to doubleclick
    Target.Offset(1).EntireRow.Insert
    Target.EntireRow.Copy ActiveCell.Offset(1).EntireRow
    Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
End Sub

---
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

"Marcella" <smileyface@mymelody.com> wrote in message news:40ab5188$0$202$75868355@news.frii.net...
> Hi all,
>
> I want to create a formula automation so when a user enter a data
> through the form that I've created, the data will go straight to my
> Excel database and Excel should automatically calculate them using the
> formula from the previous row. I have tried to go to
> "Tools-options-edit-extend formula" and ticked the box but it won't do
> anything.
>
> Does anyone has any idea on how to do this automation thing?
> Thanks a lot!
>
> Rgds,
> Marcella
>
> ** Posted via: http://www.ozgrid.com
> Excel Templates, Training, Add-ins &
> Software!http://www.ozgrid.com/Services/excel-software-categories.htm **


0
dmcritchie (2586)
5/19/2004 3:21:10 PM
Hi
as addition you may also have a look at:
http://j-walk.com/ss/dataform/index.htm

--
Regards
Frank Kabel
Frankfurt, Germany


DDM wrote:
> Marcella, why reinvent the wheel? What you are trying to do is
> exactly what the Form command on the Data menu is designed to do. So
> long as your data is organized by columns and rows in a contiguous
> set of cells with column labels at the top, all your users have to do
> is click in the data and Data > Form. Use the form to add data, then
> click Close. Excel adds the data to the bottom of the list and copies
> any formulas down, just as you want.
>
>
> "Marcella" <smileyface@mymelody.com> wrote in message
> news:40ab5188$0$202$75868355@news.frii.net...
>> Hi all,
>>
>> I want to create a formula automation so when a user enter a data
>> through the form that I've created, the data will go straight to my
>> Excel database and Excel should automatically calculate them using
>> the formula from the previous row. I have tried to go to
>> "Tools-options-edit-extend formula" and ticked the box but it won't
>> do anything.
>>
>> Does anyone has any idea on how to do this automation thing?
>> Thanks a lot!
>>
>> Rgds,
>> Marcella
>>
>> ** Posted via: http://www.ozgrid.com
>> Excel Templates, Training, Add-ins &
>>
Software!http://www.ozgrid.com/Services/excel-software-categories.htm
>> **

0
frank.kabel (11126)
5/19/2004 3:25:10 PM
Hi Marcella,
I had both target and activecell in the example I supplied, change the
activecell to target  to correct the example.    An explanation of corrected
code can be found at:
   Insert a row and copy formulas  (single row)
   http://www.mvps.org/dmcritchie/excel/insrtrow.htm#dclick
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm


0
dmcritchie (2586)
5/19/2004 4:33:11 PM
Reply:

Similar Artilces: