Automatic Worksheet updating

[onthefly]

How do I get a journal worksheet, where invoices are written in
automatically update a worksheet for each account.

The journal worksheet has the columb headings
DATE, ACCOUNT, INVOICE, AMOUNT, GST, PAID, CHQ, CASH, & COMMENT.

The Account worksheet has the title of each account, e.g. AGL
One worksheet per account.

I would like to be able to enter into the journal sheet, invoic
details and have them automatically update to the appropriate accoun
worksheet in date order with the same layout of the journal sheet.

Is there a template anywhere that I could modify or can someone show m
the necessary formulars to get this simple book keeping sorted.

I have a exaple of the excel sheet attached

Kyle
(e-mail address removed)

Attachment filename: template for invoices.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=47183

 

[Ben]

Kyle,

I had a similiar question and Dave Peterson posted the
following in response to my Question:
"Not that I'm aware.

And I think that the "sending" workbook would have to be
saved.

Maybe a workaround is to have an "ontime" macro run every
10 minutes that
refreshes links.

Chip Pearson has lots of nice instructions for OnTime
procedures at:

http://www.cpearson.com/excel/ontime.htm

Stolen shamelessly from Chip's page and the help for
updatelinks

Option Explicit
Public RunWhen As Double
Public Const cRunIntervalSeconds = 600 '10 minutes
Public Const cRunWhat = "MyRefresh"
Sub auto_open()
Call StartTimer
End Sub
Sub auto_close()
Call StopTimer
End Sub
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen,
procedure:=cRunWhat, _
schedule:=True
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
End Sub
Sub myRefresh()
With ThisWorkbook
.UpdateLink Name:=.LinkSources
End With
Call StartTimer 'get ready for next time
End Sub

If you're new to macros, you may want to read David's
intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm"


hth

 

[Dave Peterson]

If running a macro when you're ready is automatic enough, you may want to look
at Debra Dalgleish's site.

She has some examples that separate lists into different sheets.
http://www.contextures.com/excelfiles.html

Look for:

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- creates a list of unique items,
creates a sheet for each item, then replaces old data with current.
AdvFilterCity.xls 46 kb

and

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

 

[Dave Peterson]

This was actually for updating links from other workbooks.

I read it as a slightly different question. Separating one giant worksheet into
a bunch of smaller worksheets based on a key column.