software

I want to design a worksheet that will allow the end user to input a value
into a cell, and have the formula add it to the value that was already there.
For instance on 1mar our vendor handled 60 calls on 2mar our vendor handled
85 calls. Under the column heading of quot;calls handledquot; I would like to be able
to keep a running total, without having to use multiple cells. I thnk it
will end up being some type of concatenate function but am not sure.At a minimum you will need to use 2 cells and a worksheet change macro.
However, you lose detail and your audit trail by doing this.

You can use an Excel data entry form on a properly structured worksheet and
Excel will automatically add the data at the end of an existing range. Done
properly, you'd have each day's date and call total for a nice little
database.

If you go with a worksheet change macro and somebody incorrectly keys in 800
calls instead of 80, you now have a single value that is WRONG and the error
cannot be repaired or recovered.

So, is that what you really want to do?
quot;rneaulquot; wrote:

gt; I want to design a worksheet that will allow the end user to input a value
gt; into a cell, and have the formula add it to the value that was already there.
gt; For instance on 1mar our vendor handled 60 calls on 2mar our vendor handled
gt; 85 calls. Under the column heading of quot;calls handledquot; I would like to be able
gt; to keep a running total, without having to use multiple cells. I thnk it
gt; will end up being some type of concatenate function but am not sure.
gt;

I have never heard of an Excel data entry form. I used to work with a
company that had a form similar to what I described below, but I am not sure
how they did it with just one cell.

Thanks

quot;Duke Careyquot; wrote:

gt; At a minimum you will need to use 2 cells and a worksheet change macro.
gt; However, you lose detail and your audit trail by doing this.
gt;
gt; You can use an Excel data entry form on a properly structured worksheet and
gt; Excel will automatically add the data at the end of an existing range. Done
gt; properly, you'd have each day's date and call total for a nice little
gt; database.
gt;
gt; If you go with a worksheet change macro and somebody incorrectly keys in 800
gt; calls instead of 80, you now have a single value that is WRONG and the error
gt; cannot be repaired or recovered.
gt;
gt; So, is that what you really want to do?
gt;
gt;
gt;
gt; quot;rneaulquot; wrote:
gt;
gt; gt; I want to design a worksheet that will allow the end user to input a value
gt; gt; into a cell, and have the formula add it to the value that was already there.
gt; gt; For instance on 1mar our vendor handled 60 calls on 2mar our vendor handled
gt; gt; 85 calls. Under the column heading of quot;calls handledquot; I would like to be able
gt; gt; to keep a running total, without having to use multiple cells. I thnk it
gt; gt; will end up being some type of concatenate function but am not sure.
gt; gt;

Put the words 'Date' and 'Calls' in cells A1 amp; B1, format column A to be
dates, then select both cells and use the Data menu to choose Form...

You may get a message that Excel asking if the first row is column headings,
or something like that. Excel creates the form and, as you add new data,
places it in the spreadsheet for you

quot;rneaulquot; wrote:

gt; I have never heard of an Excel data entry form. I used to work with a
gt; company that had a form similar to what I described below, but I am not sure
gt; how they did it with just one cell.
gt;
gt; Thanks
gt;
gt; quot;Duke Careyquot; wrote:
gt;
gt; gt; At a minimum you will need to use 2 cells and a worksheet change macro.
gt; gt; However, you lose detail and your audit trail by doing this.
gt; gt;
gt; gt; You can use an Excel data entry form on a properly structured worksheet and
gt; gt; Excel will automatically add the data at the end of an existing range. Done
gt; gt; properly, you'd have each day's date and call total for a nice little
gt; gt; database.
gt; gt;
gt; gt; If you go with a worksheet change macro and somebody incorrectly keys in 800
gt; gt; calls instead of 80, you now have a single value that is WRONG and the error
gt; gt; cannot be repaired or recovered.
gt; gt;
gt; gt; So, is that what you really want to do?
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;rneaulquot; wrote:
gt; gt;
gt; gt; gt; I want to design a worksheet that will allow the end user to input a value
gt; gt; gt; into a cell, and have the formula add it to the value that was already there.
gt; gt; gt; For instance on 1mar our vendor handled 60 calls on 2mar our vendor handled
gt; gt; gt; 85 calls. Under the column heading of quot;calls handledquot; I would like to be able
gt; gt; gt; to keep a running total, without having to use multiple cells. I thnk it
gt; gt; gt; will end up being some type of concatenate function but am not sure.
gt; gt; gt;

That's cool... Although the form doesn't come up each time the user opens the
form... how would I handle the macro?

quot;Duke Careyquot; wrote:

gt; Put the words 'Date' and 'Calls' in cells A1 amp; B1, format column A to be
gt; dates, then select both cells and use the Data menu to choose Form...
gt;
gt; You may get a message that Excel asking if the first row is column headings,
gt; or something like that. Excel creates the form and, as you add new data,
gt; places it in the spreadsheet for you
gt;
gt; quot;rneaulquot; wrote:
gt;
gt; gt; I have never heard of an Excel data entry form. I used to work with a
gt; gt; company that had a form similar to what I described below, but I am not sure
gt; gt; how they did it with just one cell.
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; quot;Duke Careyquot; wrote:
gt; gt;
gt; gt; gt; At a minimum you will need to use 2 cells and a worksheet change macro.
gt; gt; gt; However, you lose detail and your audit trail by doing this.
gt; gt; gt;
gt; gt; gt; You can use an Excel data entry form on a properly structured worksheet and
gt; gt; gt; Excel will automatically add the data at the end of an existing range. Done
gt; gt; gt; properly, you'd have each day's date and call total for a nice little
gt; gt; gt; database.
gt; gt; gt;
gt; gt; gt; If you go with a worksheet change macro and somebody incorrectly keys in 800
gt; gt; gt; calls instead of 80, you now have a single value that is WRONG and the error
gt; gt; gt; cannot be repaired or recovered.
gt; gt; gt;
gt; gt; gt; So, is that what you really want to do?
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;rneaulquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I want to design a worksheet that will allow the end user to input a value
gt; gt; gt; gt; into a cell, and have the formula add it to the value that was already there.
gt; gt; gt; gt; For instance on 1mar our vendor handled 60 calls on 2mar our vendor handled
gt; gt; gt; gt; 85 calls. Under the column heading of quot;calls handledquot; I would like to be able
gt; gt; gt; gt; to keep a running total, without having to use multiple cells. I thnk it
gt; gt; gt; gt; will end up being some type of concatenate function but am not sure.
gt; gt; gt; gt;

Duke Carey wrote...
gt;At a minimum you will need to use 2 cells and a worksheet change macro.
gt;However, you lose detail and your audit trail by doing this.
....

Picky technical response. I agree about the audit trail, but this
repsonse is limited to the technical issue of using a cell as an
accumulator.

It doesn't require two cells. It only requires trickery. To treat cell
B2 as an accumulator all it takes is something like this.Private Sub Worksheet_Change(ByVal Target As Range)
Dim t As Variant
If Intersect(Target, Range(quot;B2quot;)).Cells.Count = 1 Then
Set Target = Range(quot;B2quot;)
Else
Exit Sub
End If
On Error Resume Next
Application.EnableEvents = False
t = Target.Value
Application.Undo
Target.Value = Target.Value t
If Err.Number lt;gt; 0 Then Target.Value = t
Err.Clear
Application.EnableEvents = True
End SubRight click the sheet's tab and select View Code to bring up the VB editor
In the top left corner is the Project Explorer - if you don't see it choose
it from the View menu
Find the name of the file that contains your data and dbl-click on the
ThisWorkbook node to bring up the code module for the workbook
Above the code window you'll see two dropdowns. In the left dropdown choose
Workbook.
If the right dropdown doesn't change to 'Open', then select Open from the
right dropdown
Excel will insert the structure of a workbook open event
Between the two lines that are inserted for you type

Worksheets(quot;Sheet1quot;).activate
ActiveSheet.ShowDataForm

Change the quot;Sheet1quot; to the actual name of your tabbed sheetquot;rneaulquot; wrote:

gt; That's cool... Although the form doesn't come up each time the user opens the
gt; form... how would I handle the macro?
gt;
gt; quot;Duke Careyquot; wrote:
gt;
gt; gt; Put the words 'Date' and 'Calls' in cells A1 amp; B1, format column A to be
gt; gt; dates, then select both cells and use the Data menu to choose Form...
gt; gt;
gt; gt; You may get a message that Excel asking if the first row is column headings,
gt; gt; or something like that. Excel creates the form and, as you add new data,
gt; gt; places it in the spreadsheet for you
gt; gt;
gt; gt; quot;rneaulquot; wrote:
gt; gt;
gt; gt; gt; I have never heard of an Excel data entry form. I used to work with a
gt; gt; gt; company that had a form similar to what I described below, but I am not sure
gt; gt; gt; how they did it with just one cell.
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt;
gt; gt; gt; quot;Duke Careyquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; At a minimum you will need to use 2 cells and a worksheet change macro.
gt; gt; gt; gt; However, you lose detail and your audit trail by doing this.
gt; gt; gt; gt;
gt; gt; gt; gt; You can use an Excel data entry form on a properly structured worksheet and
gt; gt; gt; gt; Excel will automatically add the data at the end of an existing range. Done
gt; gt; gt; gt; properly, you'd have each day's date and call total for a nice little
gt; gt; gt; gt; database.
gt; gt; gt; gt;
gt; gt; gt; gt; If you go with a worksheet change macro and somebody incorrectly keys in 800
gt; gt; gt; gt; calls instead of 80, you now have a single value that is WRONG and the error
gt; gt; gt; gt; cannot be repaired or recovered.
gt; gt; gt; gt;
gt; gt; gt; gt; So, is that what you really want to do?
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;rneaulquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; I want to design a worksheet that will allow the end user to input a value
gt; gt; gt; gt; gt; into a cell, and have the formula add it to the value that was already there.
gt; gt; gt; gt; gt; For instance on 1mar our vendor handled 60 calls on 2mar our vendor handled
gt; gt; gt; gt; gt; 85 calls. Under the column heading of quot;calls handledquot; I would like to be able
gt; gt; gt; gt; gt; to keep a running total, without having to use multiple cells. I thnk it
gt; gt; gt; gt; gt; will end up being some type of concatenate function but am not sure.
gt; gt; gt; gt; gt;

Harlan - point well taken. However, your code throws an error when trying to
change any cell BUT B2
Instead of
If Intersect(Target, Range(quot;B2quot;)).Cells.Count = 1 Then
maybe
If Intersect(Target, Range(quot;B2quot;)) Is Nothing Then Exit Sub

quot;Harlan Grovequot; wrote:

gt; Duke Carey wrote...
gt; gt;At a minimum you will need to use 2 cells and a worksheet change macro.
gt; gt;However, you lose detail and your audit trail by doing this.
gt; ....
gt;
gt; Picky technical response. I agree about the audit trail, but this
gt; repsonse is limited to the technical issue of using a cell as an
gt; accumulator.
gt;
gt; It doesn't require two cells. It only requires trickery. To treat cell
gt; B2 as an accumulator all it takes is something like this.
gt;
gt;
gt; Private Sub Worksheet_Change(ByVal Target As Range)
gt; Dim t As Variant
gt; If Intersect(Target, Range(quot;B2quot;)).Cells.Count = 1 Then
gt; Set Target = Range(quot;B2quot;)
gt; Else
gt; Exit Sub
gt; End If
gt; On Error Resume Next
gt; Application.EnableEvents = False
gt; t = Target.Value
gt; Application.Undo
gt; Target.Value = Target.Value t
gt; If Err.Number lt;gt; 0 Then Target.Value = t
gt; Err.Clear
gt; Application.EnableEvents = True
gt; End Sub
gt;
gt;