help with spreadsheet calculations
 

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 "calls handled" 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.

help with spreadsheet calculations
 

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?



"rneaul" wrote:

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 "calls handled" 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.

help with spreadsheet calculations
 

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

"Duke Carey" wrote:

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?



"rneaul" wrote:

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 "calls handled" 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.

help with spreadsheet calculations
 

Put the words 'Date' and 'Calls' in cells A1 & 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

"rneaul" wrote:

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

"Duke Carey" wrote:

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?



"rneaul" wrote:

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 "calls handled" 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.

help with spreadsheet calculations
 

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

"Duke Carey" wrote:

Put the words 'Date' and 'Calls' in cells A1 & 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

"rneaul" wrote:

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

"Duke Carey" wrote:

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?



"rneaul" wrote:

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 "calls handled" 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.

help with spreadsheet calculations
 

Duke Carey wrote...
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.
....

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("B2")).Cells.Count = 1 Then
Set Target = Range("B2")
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 < 0 Then Target.Value = t
Err.Clear
Application.EnableEvents = True
End Sub

help with spreadsheet calculations
 

Right 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("Sheet1").activate
ActiveSheet.ShowDataForm

Change the "Sheet1" to the actual name of your tabbed sheet





"rneaul" wrote:

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

"Duke Carey" wrote:

Put the words 'Date' and 'Calls' in cells A1 & 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

"rneaul" wrote:

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

"Duke Carey" wrote:

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?



"rneaul" wrote:

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 "calls handled" 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.

help with spreadsheet calculations
 

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

"Harlan Grove" wrote:

Duke Carey wrote...
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.
....

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("B2")).Cells.Count = 1 Then
Set Target = Range("B2")
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 < 0 Then Target.Value = t
Err.Clear
Application.EnableEvents = True
End Sub