Automatic update of Financial Spreadsheets
 

I have a series of financial spreadsheets for my company. Each job has its
own workbook with several spreadsheets in it; ex. Analysis, Expense
Breakdown, Expense paid. I have designed the spreadsheets so I can simply
type in the information needed for the Expense Breakdown and it updates the
Analysis with totals. What I am looking for is a button, or check box placed
next too the Breakdown which when pushed or checked will automatically update
the Expense Paid spreadsheet in the appropriate fields. It can be a series
of buttons or checkboxes, as long as it updates the expense paid sheet.
--
Kevin J Matson
Office Manager
Roberts Commercial Drywall Inc.

Automatic update of Financial Spreadsheets
 

formulas don't work?

If you want a macro, your question lacks any detail that would support
suggesting one.

Private Sub CommandButton1_click()
worksheets("Analysis").Range("B9").Value = _
application.Sum(worksheets("Expense Breakdown"). _
Range("F10:F50")
End Sub

would be a sample of code associated with a commandbutton added from the
control toolbox toolbar, that when clicked sums F10:F50 on a worksheet
"Expense Breakdown" and puts the results in B9 of the sheet analysis.

--
Regards,
Tom Ogilvy

"Kevin" wrote in message
...
I have a series of financial spreadsheets for my company. Each job has its
own workbook with several spreadsheets in it; ex. Analysis, Expense
Breakdown, Expense paid. I have designed the spreadsheets so I can simply
type in the information needed for the Expense Breakdown and it updates
the
Analysis with totals. What I am looking for is a button, or check box
placed
next too the Breakdown which when pushed or checked will automatically
update
the Expense Paid spreadsheet in the appropriate fields. It can be a
series
of buttons or checkboxes, as long as it updates the expense paid sheet.
--
Kevin J Matson
Office Manager
Roberts Commercial Drywall Inc.

Automatic update of Financial Spreadsheets
 

How it works is this. My first worksheet is for the analysis which just shows
totals. My second worksheet is the breakdown which breaks down all total
categories with purchases made. What i would like is to insert a button of
some sort next to each purchase so that when it has been paid i can check it
or push it and it will update the third spreadsheet (Expense paid) with the
transaction which was paid and the same information entered for that purchase
from the Expense Breakdown worksheet.
--
Kevin J Matson
Office Manager
Roberts Commercial Drywall Inc.


"Tom Ogilvy" wrote:

formulas don't work?

If you want a macro, your question lacks any detail that would support
suggesting one.

Private Sub CommandButton1_click()
worksheets("Analysis").Range("B9").Value = _
application.Sum(worksheets("Expense Breakdown"). _
Range("F10:F50")
End Sub

would be a sample of code associated with a commandbutton added from the
control toolbox toolbar, that when clicked sums F10:F50 on a worksheet
"Expense Breakdown" and puts the results in B9 of the sheet analysis.

--
Regards,
Tom Ogilvy

"Kevin" wrote in message
...
I have a series of financial spreadsheets for my company. Each job has its
own workbook with several spreadsheets in it; ex. Analysis, Expense
Breakdown, Expense paid. I have designed the spreadsheets so I can simply
type in the information needed for the Expense Breakdown and it updates
the
Analysis with totals. What I am looking for is a button, or check box
placed
next too the Breakdown which when pushed or checked will automatically
update
the Expense Paid spreadsheet in the appropriate fields. It can be a
series
of buttons or checkboxes, as long as it updates the expense paid sheet.
--
Kevin J Matson
Office Manager
Roberts Commercial Drywall Inc.

Automatic update of Financial Spreadsheets
 

Assuming Breakdown and paid are identical and the paid would be on the same
row on both sheets

A checkbox would be better I think because you would then know you had
pushed it.

Write a macro like

Sub Checkbox_click()
sName = Application.Caller
set cbox = worksheets("Expense Breakdown").Checkboxes(sName)
if cbox.Value = xlOn then
rw = cbox.TopLeftCell.Row
Worksheets("Expense Paid").Cells(rw,"F").Value = _
Worksheets("Expense Breakdown").Cells(rw,"G").Value
End if
End Sub

make the forms toolbar visible and drag the checkboxes to the appropriate
location on Expense Breakdown. Assign each to the above macro (which should
be in a general module, not the sheet module).

--
Regards,
Tom Ogilvy


"Kevin" wrote in message
...
How it works is this. My first worksheet is for the analysis which just
shows
totals. My second worksheet is the breakdown which breaks down all total
categories with purchases made. What i would like is to insert a button of
some sort next to each purchase so that when it has been paid i can check
it
or push it and it will update the third spreadsheet (Expense paid) with
the
transaction which was paid and the same information entered for that
purchase
from the Expense Breakdown worksheet.
--
Kevin J Matson
Office Manager
Roberts Commercial Drywall Inc.


"Tom Ogilvy" wrote:

formulas don't work?

If you want a macro, your question lacks any detail that would support
suggesting one.

Private Sub CommandButton1_click()
worksheets("Analysis").Range("B9").Value = _
application.Sum(worksheets("Expense Breakdown"). _
Range("F10:F50")
End Sub

would be a sample of code associated with a commandbutton added from the
control toolbox toolbar, that when clicked sums F10:F50 on a worksheet
"Expense Breakdown" and puts the results in B9 of the sheet analysis.

--
Regards,
Tom Ogilvy

"Kevin" wrote in message
...
I have a series of financial spreadsheets for my company. Each job has
its
own workbook with several spreadsheets in it; ex. Analysis, Expense
Breakdown, Expense paid. I have designed the spreadsheets so I can
simply
type in the information needed for the Expense Breakdown and it
updates
the
Analysis with totals. What I am looking for is a button, or check box
placed
next too the Breakdown which when pushed or checked will automatically
update
the Expense Paid spreadsheet in the appropriate fields. It can be a
series
of buttons or checkboxes, as long as it updates the expense paid
sheet.
--
Kevin J Matson
Office Manager
Roberts Commercial Drywall Inc.

Automatic update of Financial Spreadsheets
 

Kevin,

I usually do this by setting a column (usually column A) for the check.
Instead of checkboxes or buttons I use the Selection change event. [the
only problem is accidental selection of a cell in column A]. You might want
to add a second check for entries in other cells.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
If IsEmpty(Target) Then
Target = "X"
MsgBox "Do something"
Else: Target.ClearContents
End If
End If
End Sub

--
steveB

Remove "AYN" from email to respond
"Kevin" wrote in message
...
How it works is this. My first worksheet is for the analysis which just
shows
totals. My second worksheet is the breakdown which breaks down all total
categories with purchases made. What i would like is to insert a button of
some sort next to each purchase so that when it has been paid i can check
it
or push it and it will update the third spreadsheet (Expense paid) with
the
transaction which was paid and the same information entered for that
purchase
from the Expense Breakdown worksheet.
--
Kevin J Matson
Office Manager
Roberts Commercial Drywall Inc.


"Tom Ogilvy" wrote:

formulas don't work?

If you want a macro, your question lacks any detail that would support
suggesting one.

Private Sub CommandButton1_click()
worksheets("Analysis").Range("B9").Value = _
application.Sum(worksheets("Expense Breakdown"). _
Range("F10:F50")
End Sub

would be a sample of code associated with a commandbutton added from the
control toolbox toolbar, that when clicked sums F10:F50 on a worksheet
"Expense Breakdown" and puts the results in B9 of the sheet analysis.

--
Regards,
Tom Ogilvy

"Kevin" wrote in message
...
I have a series of financial spreadsheets for my company. Each job has
its
own workbook with several spreadsheets in it; ex. Analysis, Expense
Breakdown, Expense paid. I have designed the spreadsheets so I can
simply
type in the information needed for the Expense Breakdown and it updates
the
Analysis with totals. What I am looking for is a button, or check box
placed
next too the Breakdown which when pushed or checked will automatically
update
the Expense Paid spreadsheet in the appropriate fields. It can be a
series
of buttons or checkboxes, as long as it updates the expense paid sheet.
--
Kevin J Matson
Office Manager
Roberts Commercial Drywall Inc.

Automatic update of Financial Spreadsheets
 

Thank you so much for your help
--
Kevin J Matson
Office Manager
Roberts Commercial Drywall Inc.


"Tom Ogilvy" wrote:

Assuming Breakdown and paid are identical and the paid would be on the same
row on both sheets

A checkbox would be better I think because you would then know you had
pushed it.

Write a macro like

Sub Checkbox_click()
sName = Application.Caller
set cbox = worksheets("Expense Breakdown").Checkboxes(sName)
if cbox.Value = xlOn then
rw = cbox.TopLeftCell.Row
Worksheets("Expense Paid").Cells(rw,"F").Value = _
Worksheets("Expense Breakdown").Cells(rw,"G").Value
End if
End Sub

make the forms toolbar visible and drag the checkboxes to the appropriate
location on Expense Breakdown. Assign each to the above macro (which should
be in a general module, not the sheet module).

--
Regards,
Tom Ogilvy


"Kevin" wrote in message
...
How it works is this. My first worksheet is for the analysis which just
shows
totals. My second worksheet is the breakdown which breaks down all total
categories with purchases made. What i would like is to insert a button of
some sort next to each purchase so that when it has been paid i can check
it
or push it and it will update the third spreadsheet (Expense paid) with
the
transaction which was paid and the same information entered for that
purchase
from the Expense Breakdown worksheet.
--
Kevin J Matson
Office Manager
Roberts Commercial Drywall Inc.


"Tom Ogilvy" wrote:

formulas don't work?

If you want a macro, your question lacks any detail that would support
suggesting one.

Private Sub CommandButton1_click()
worksheets("Analysis").Range("B9").Value = _
application.Sum(worksheets("Expense Breakdown"). _
Range("F10:F50")
End Sub

would be a sample of code associated with a commandbutton added from the
control toolbox toolbar, that when clicked sums F10:F50 on a worksheet
"Expense Breakdown" and puts the results in B9 of the sheet analysis.

--
Regards,
Tom Ogilvy

"Kevin" wrote in message
...
I have a series of financial spreadsheets for my company. Each job has
its
own workbook with several spreadsheets in it; ex. Analysis, Expense
Breakdown, Expense paid. I have designed the spreadsheets so I can
simply
type in the information needed for the Expense Breakdown and it
updates
the
Analysis with totals. What I am looking for is a button, or check box
placed
next too the Breakdown which when pushed or checked will automatically
update
the Expense Paid spreadsheet in the appropriate fields. It can be a
series
of buttons or checkboxes, as long as it updates the expense paid
sheet.
--
Kevin J Matson
Office Manager
Roberts Commercial Drywall Inc.

Automatic update of Financial Spreadsheets
 

You might look at Steve Bell's suggestion - that might be really what you
want. Just be aware that any movement through the cell will trigger the
selectionchange event.
--
Regards,
Tom Ogilvy

"Kevin" wrote in message
...
Thank you so much for your help
--
Kevin J Matson
Office Manager
Roberts Commercial Drywall Inc.


"Tom Ogilvy" wrote:

Assuming Breakdown and paid are identical and the paid would be on the
same
row on both sheets

A checkbox would be better I think because you would then know you had
pushed it.

Write a macro like

Sub Checkbox_click()
sName = Application.Caller
set cbox = worksheets("Expense Breakdown").Checkboxes(sName)
if cbox.Value = xlOn then
rw = cbox.TopLeftCell.Row
Worksheets("Expense Paid").Cells(rw,"F").Value = _
Worksheets("Expense Breakdown").Cells(rw,"G").Value
End if
End Sub

make the forms toolbar visible and drag the checkboxes to the
appropriate
location on Expense Breakdown. Assign each to the above macro (which
should
be in a general module, not the sheet module).

--
Regards,
Tom Ogilvy


"Kevin" wrote in message
...
How it works is this. My first worksheet is for the analysis which
just
shows
totals. My second worksheet is the breakdown which breaks down all
total
categories with purchases made. What i would like is to insert a
button of
some sort next to each purchase so that when it has been paid i can
check
it
or push it and it will update the third spreadsheet (Expense paid)
with
the
transaction which was paid and the same information entered for that
purchase
from the Expense Breakdown worksheet.
--
Kevin J Matson
Office Manager
Roberts Commercial Drywall Inc.


"Tom Ogilvy" wrote:

formulas don't work?

If you want a macro, your question lacks any detail that would
support
suggesting one.

Private Sub CommandButton1_click()
worksheets("Analysis").Range("B9").Value = _
application.Sum(worksheets("Expense Breakdown"). _
Range("F10:F50")
End Sub

would be a sample of code associated with a commandbutton added from
the
control toolbox toolbar, that when clicked sums F10:F50 on a
worksheet
"Expense Breakdown" and puts the results in B9 of the sheet
analysis.

--
Regards,
Tom Ogilvy

"Kevin" wrote in message
...
I have a series of financial spreadsheets for my company. Each job
has
its
own workbook with several spreadsheets in it; ex. Analysis,
Expense
Breakdown, Expense paid. I have designed the spreadsheets so I can
simply
type in the information needed for the Expense Breakdown and it
updates
the
Analysis with totals. What I am looking for is a button, or check
box
placed
next too the Breakdown which when pushed or checked will
automatically
update
the Expense Paid spreadsheet in the appropriate fields. It can be
a
series
of buttons or checkboxes, as long as it updates the expense paid
sheet.
--
Kevin J Matson
Office Manager
Roberts Commercial Drywall Inc.