In excell worksheet there is a cell named: Total Sales which requires data
from Access sales data sales which has the report/form if we run can produce
the total YTD Sales.

My question is how can we link this excell cell to the access total YTD
sales. We know in access can only produce report if we run it, while
we(Budget control Dept) want the data be avaialbe any time. Because Budget
Contorl dept needs their excell worksheet be uptodate anytime (Hotline) We
are using network for this purpose.

We appreciate your idea/help

Frank

Did you try under Data - Import External Data - New Database Query? You can
generate a macro for importing the data.

I start to record a macro and then import some data and got the following

Sub Macro3()
'
' Macro3 Macro

'

'
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=C:\TEMP\PMI Part
Log.mdb;DefaultDir=C:\TEMP;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout" _
), Array("=5;")), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT `IIRM-2 Material Received`.ID, `IIRM-2 Material
Received`.`Procurement Part Number`, `IIRM-2 Material Received`.`Upscreen
Part Number`, `IIRM-2 Material Received`.`Wafer Lot Number1`" & Chr(13) & ""
& Chr(10) & "FROM `C:\" _
, "TEMP\PMI Part Log`.`IIRM-2 Material Received` `IIRM-2 Material
Received`")
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

"Frank Situmorang" wrote:

In excell worksheet there is a cell named: Total Sales which requires data
from Access sales data sales which has the report/form if we run can produce
the total YTD Sales.

My question is how can we link this excell cell to the access total YTD
sales. We know in access can only produce report if we run it, while
we(Budget control Dept) want the data be avaialbe any time. Because Budget
Contorl dept needs their excell worksheet be uptodate anytime (Hotline) We
are using network for this purpose.

We appreciate your idea/help

Frank

Frank,

Joel has an excellent suggestion for getting YTD sales data into Excel, but
if you need to use an €œapproved€� number, then keep reading.

Frank, I assume that you want to use the numbers generated in the Access
report to make certain that you are using the same number as other
departments. Please note that Excel is perfectly capable of retrieving and
consolidating this data from a number of different sources, not only from
Access, but also including databases kept in other vendors Relational
Database Management Systems (RDBMS), such as Oracle. If you have a legal or
departmental reason to use only an €˜approved number from a specific report,
then this is where we will go for now. We will attempt to gather the exact
same data as the Access report; if possible, even using the same gathering
tools.

There are a number of ways to do this. There are more complicated ways, but
the easiest way might be to use the query that brings data to the report to
bring that same data to Excel, then use Excel to summarize the data (on a
separate worksheet in the same workbook) and link that summary to the €˜Total
Sales cell. Excel can retrieve data from an Access query just as easily as
it can from an Access table.

Frank, this is going to look complicated, but as you get into it and take it
a step at a time, it will be simple, at least the second time you do it.
After finding all the pieces the first time, you will be able to do this in
your sleep.

Access reports normally get numbers and other data from various sources
using Structured Query Language (SQL) statements. (Please see Note 1.) These
statements are called Queries in Access. Access reports can use queries in
several ways. Generally, in my experience, the three most popular would be:

1. A query is made and saved in the Access database file and the report is
linked to this pre-made query when the report is developed.
2. The report is developed without an external query; the query is created
during the design process of the report. The query is then stored inside the
report design as a SQL statement and is not a stand-alone query. Both 1 and 2
are static queries, the queries do not change.
3. The report is designed for use with a dynamic query. Typically, in Access
this would be a report that is called from an Access form. The form (an
interactive document, as compared to a view-only Report) would allow the user
to make selections such as date range, sales area, and/or sales person.
Typically, the user would then click a €œshow report€� button on the form. In
the background, the code behind that button would build a SQL statement (the
query in text form) and then would call the report while linking the SQL
statement to the report at run time. This method, and number 2 above, could
present some slight, albeit different, challenges.

The report builder might have used the query to simply select just
year-to-date sales records and then used Access report tools to do the
summarization. On the other hand, a certain amount of summarization can be
done in SQL and the report could have received pre-summarized data. Two ways
to find this out: open the report in design view or ask the report builder.

Assuming you would have already asked the builder if that option was
available, you may need to go into the report and look for the necessary
information. From here we must assume that you have security permissions to
see the database window (Please see Note 2.) with all of the database objects
and are not locked behind a menu. Even if the report is locked behind a menu,
the possibility may still exist to open the report in design view.

Assuming that you can see the report in the report page or tab of the
database object window, highlight the report and click €˜Design in the
database window tools, or right-click the report and select €˜Design.

From this point on, be careful to not save any changes you may inadvertently
make in the report; just say no if asked by a dialog box if you want to save,
and stay away from the little 3.5 floppy disk €˜save emblem in the Access
window while the report is open in design view. It would be safer to work
with a copy of the report, if you can create objects in the database. (Right
click the report and Copy, click a clean area in the database report objects
window and click Save, provide a name for your temporary copy and OK. Then
use the temp copy to discover the data source or sources used. Delete the
temp copy when finished)

If you are opening the report from a menu and not directly opening it in the
database objects reports window, then a final possibility is to open the
report first and this may give you access to the Design view button. This
button has a generally triangular shaped symbol on it and is usually on the
far left of the tool bar in the window immediately above the report.

If you can get the report opened in design view, then you need to €˜show the
€˜Properties window for the report. This button has a small hand holding a
sheet of paper as the icon. If you call the Properties window as soon as you
open the report in Design view then you will have the properties of the
report showing; if you have clicked on anything in the report, then the
properties of whatever you clicked will be showing. The word €œReport€� should
show in the top of the properties window and there will be a dropdown box in
which you can select €œReport€� if needed.

What you want is the Data tab or section of the Properties window, once you
make sure you are looking at the Report Properties. This tab contains a
property called €œRecord Source€� and this is where the rubber meets the road
in getting data into the report.

What you find in €œRecord Source€� may take different forms. The name of a
table or saved query may be used there, or an actual SQL statement. If good
naming conventions have been used in the database, the name of a query will
contain clues, such as the word query or something like €˜qry used as part of
the query name. If it is based on a single table, then a word or table title,
such as €œSales_Data€� might be all that appears there. If it is a single word,
or short title-like phrase and it is not obvious that it is a table or query,
look in the Queries tab of the database objects window and see if you find
the name there.

If you find a saved query, you are practically home free, sort of. This
query might be an Aggregate query that first retrieves and filters, then
groups and compiles the data before sending it to the report, or it could be
a simple Select query that only retrieves and filters the data (in this case,
by date for the YTD report, for instance) before it sends the data to the
report.

Frank, there are several possible branches from here, too many to go forward
without further guidance into your exact situation, so if you are still
there, please let me know whether you would prefer to duplicate the reports
numbers from scratch in Excel, or would like to use the exact same query as
the Access report.

Basically, I need to know if you have and can get this far with what I have
written here, or if this needs further explanation, and if you would (and
can) just get those sales numbers directly without going to all of the
trouble of using the exact same query as the Access report. If you want to
continue this way, once we are able to select the same records as the report,
we will still be doing the aggregating and compiling in Excel. This method
will give you the exact same number as the report, it is all a matter of
making sure we select the exact same records as the report.

If you are still there at this point, please let me know how (and if) you
want to go forward, that is if you have not already solved the problem on
your own.
Hope this helps, let me know if you are interested in pursuing this. I have
the automatic notification set so just reply here.

SongBear

Note #1. Access reports can be based on a single table with no query
involved. This is unusual because, normally the data necessary to make a
coherent report is scattered in several related tables. If, in your case, a
single table contains all sales data and the report uses only that table, no
problem, we can handle that.

Note #2. The database object window can be either a free-floating window
inside of the Access main application window, or, if maximized, an extra
level of icons and object windows just inside the main Access application
window. If maximized the database object window is normally identifiable by
having a different color menu/border bar than the Access application window.
Security settings can make is inaccessible to users, so if you cannot get to
it, you may be stuck with duplicating the reports numbers directly with
Excel database queries, or finding the reports creator and gaining access
that way.


"Frank Situmorang" wrote:

In excell worksheet there is a cell named: Total Sales which requires data
from Access sales data sales which has the report/form if we run can produce
the total YTD Sales.

My question is how can we link this excell cell to the access total YTD
sales. We know in access can only produce report if we run it, while
we(Budget control Dept) want the data be avaialbe any time. Because Budget
Contorl dept needs their excell worksheet be uptodate anytime (Hotline) We
are using network for this purpose.

We appreciate your idea/help

Frank

thanks song for your extensive explanation, I am studying all your
suggestion. In fact I have been successful to develop an access database for
tracking supplier invoices and it is "go live" now.

Now my Japanese boss asked me again to create a lingk between an excell
spreadsheet in the Budget Control Section with the Revenue section and asked
if it is possible to make it in access.

But I think, since I want to make it quickly, so I intend to make it in
excell but my problem is this

there is a sheet contains these columns:
1. job number,
2 invoice amount,
3. payment date

This is filled out by revenue section on timely basis randomly( not
sequenced by job)

I alreadty created another sheet(summarySheet) contains summary using
"Sumif" for total ivoicec by project).

How can I make in the same summary sheet, the total collection by using
SumIF but fon only if the payment date exists( Not blank). I tried to to
combine 2 criteria 1 from summary sheet but it does not work for the total
collection by job number.

Thank you very much.

Frank



"SongBear" wrote:

Frank,

Joel has an excellent suggestion for getting YTD sales data into Excel, but
if you need to use an €œapproved€� number, then keep reading.

Frank, I assume that you want to use the numbers generated in the Access
report to make certain that you are using the same number as other
departments. Please note that Excel is perfectly capable of retrieving and
consolidating this data from a number of different sources, not only from
Access, but also including databases kept in other vendors Relational
Database Management Systems (RDBMS), such as Oracle. If you have a legal or
departmental reason to use only an €˜approved number from a specific report,
then this is where we will go for now. We will attempt to gather the exact
same data as the Access report; if possible, even using the same gathering
tools.

There are a number of ways to do this. There are more complicated ways, but
the easiest way might be to use the query that brings data to the report to
bring that same data to Excel, then use Excel to summarize the data (on a
separate worksheet in the same workbook) and link that summary to the €˜Total
Sales cell. Excel can retrieve data from an Access query just as easily as
it can from an Access table.

Frank, this is going to look complicated, but as you get into it and take it
a step at a time, it will be simple, at least the second time you do it.
After finding all the pieces the first time, you will be able to do this in
your sleep.

Access reports normally get numbers and other data from various sources
using Structured Query Language (SQL) statements. (Please see Note 1.) These
statements are called Queries in Access. Access reports can use queries in
several ways. Generally, in my experience, the three most popular would be:

1. A query is made and saved in the Access database file and the report is
linked to this pre-made query when the report is developed.
2. The report is developed without an external query; the query is created
during the design process of the report. The query is then stored inside the
report design as a SQL statement and is not a stand-alone query. Both 1 and 2
are static queries, the queries do not change.
3. The report is designed for use with a dynamic query. Typically, in Access
this would be a report that is called from an Access form. The form (an
interactive document, as compared to a view-only Report) would allow the user
to make selections such as date range, sales area, and/or sales person.
Typically, the user would then click a €œshow report€� button on the form. In
the background, the code behind that button would build a SQL statement (the
query in text form) and then would call the report while linking the SQL
statement to the report at run time. This method, and number 2 above, could
present some slight, albeit different, challenges.

The report builder might have used the query to simply select just
year-to-date sales records and then used Access report tools to do the
summarization. On the other hand, a certain amount of summarization can be
done in SQL and the report could have received pre-summarized data. Two ways
to find this out: open the report in design view or ask the report builder.

Assuming you would have already asked the builder if that option was
available, you may need to go into the report and look for the necessary
information. From here we must assume that you have security permissions to
see the database window (Please see Note 2.) with all of the database objects
and are not locked behind a menu. Even if the report is locked behind a menu,
the possibility may still exist to open the report in design view.

Assuming that you can see the report in the report page or tab of the
database object window, highlight the report and click €˜Design in the
database window tools, or right-click the report and select €˜Design.

From this point on, be careful to not save any changes you may inadvertently
make in the report; just say no if asked by a dialog box if you want to save,
and stay away from the little 3.5 floppy disk €˜save emblem in the Access
window while the report is open in design view. It would be safer to work
with a copy of the report, if you can create objects in the database. (Right
click the report and Copy, click a clean area in the database report objects
window and click Save, provide a name for your temporary copy and OK. Then
use the temp copy to discover the data source or sources used. Delete the
temp copy when finished)

If you are opening the report from a menu and not directly opening it in the
database objects reports window, then a final possibility is to open the
report first and this may give you access to the Design view button. This
button has a generally triangular shaped symbol on it and is usually on the
far left of the tool bar in the window immediately above the report.

If you can get the report opened in design view, then you need to €˜show the
€˜Properties window for the report. This button has a small hand holding a
sheet of paper as the icon. If you call the Properties window as soon as you
open the report in Design view then you will have the properties of the
report showing; if you have clicked on anything in the report, then the
properties of whatever you clicked will be showing. The word €œReport€� should
show in the top of the properties window and there will be a dropdown box in
which you can select €œReport€� if needed.

What you want is the Data tab or section of the Properties window, once you
make sure you are looking at the Report Properties. This tab contains a
property called €œRecord Source€� and this is where the rubber meets the road
in getting data into the report.

What you find in €œRecord Source€� may take different forms. The name of a
table or saved query may be used there, or an actual SQL statement. If good
naming conventions have been used in the database, the name of a query will
contain clues, such as the word query or something like €˜qry used as part of
the query name. If it is based on a single table, then a word or table title,
such as €œSales_Data€� might be all that appears there. If it is a single word,
or short title-like phrase and it is not obvious that it is a table or query,
look in the Queries tab of the database objects window and see if you find
the name there.

If you find a saved query, you are practically home free, sort of. This
query might be an Aggregate query that first retrieves and filters, then
groups and compiles the data before sending it to the report, or it could be
a simple Select query that only retrieves and filters the data (in this case,
by date for the YTD report, for instance) before it sends the data to the
report.

Frank, there are several possible branches from here, too many to go forward
without further guidance into your exact situation, so if you are still
there, please let me know whether you would prefer to duplicate the reports
numbers from scratch in Excel, or would like to use the exact same query as
the Access report.

Basically, I need to know if you have and can get this far with what I have
written here, or if this needs further explanation, and if you would (and
can) just get those sales numbers directly without going to all of the
trouble of using the exact same query as the Access report. If you want to
continue this way, once we are able to select the same records as the report,
we will still be doing the aggregating and compiling in Excel. This method
will give you the exact same number as the report, it is all a matter of
making sure we select the exact same records as the report.

If you are still there at this point, please let me know how (and if) you
want to go forward, that is if you have not already solved the problem on
your own.
Hope this helps, let me know if you are interested in pursuing this. I have
the automatic notification set so just reply here.

SongBear

Note #1. Access reports can be based on a single table with no query
involved. This is unusual because, normally the data necessary to make a
coherent report is scattered in several related tables. If, in your case, a
single table contains all sales data and the report uses only that table, no
problem, we can handle that.

Note #2. The database object window can be either a free-floating window
inside of the Access main application window, or, if maximized, an extra
level of icons and object windows just inside the main Access application
window. If maximized the database object window is normally identifiable by
having a different color menu/border bar than the Access application window.
Security settings can make is inaccessible to users, so if you cannot get to
it, you may be stuck with duplicating the reports numbers directly with
Excel database queries, or finding the reports creator and gaining access
that way.


"Frank Situmorang" wrote:

In excell worksheet there is a cell named: Total Sales which requires data
from Access sales data sales which has the report/form if we run can produce
the total YTD Sales.

My question is how can we link this excell cell to the access total YTD
sales. We know in access can only produce report if we run it, while
we(Budget control Dept) want the data be avaialbe any time. Because Budget
Contorl dept needs their excell worksheet be uptodate anytime (Hotline) We
are using network for this purpose.

We appreciate your idea/help

Frank

Frank
OK, so the data is not already in Access, I did not understand this from the
original question. And you do have experience in Access, which is good to
know as a starting point for future questions.

I think that there may be a quick answer but I need you to try it and tell
me if further help is needed. If further help is needed, I might be of more
help if I do not have to re-create a sample of your worksheets blindly to
test my suggestions.

I was just sitting here trying to imagine what your sheets looked like
exactly so I could work with the problem and suddenly realized, you need to
meet an old friend of mine.
This old friend is called the Conditional Sum Wizard. It helps you create
multiple level sum-if formulas and it might just be the fastest way to answer
your question. A lot better than writing a book.

You find the Conditional Sum Wizard by dropping and expanding your Tools
menu. It is an add-in that comes with Excel €“ I am pretty sure I have seen it
at least since XL97, not sure.

If the conditional sum wizard is not showing, then click Add Ins and check
it to install it.
Then follow the wizard.

Let me know if this is what you needed; if this does not get it done, we can
keep working on it here.
SongBear

"Frank Situmorang" wrote:

thanks song for your extensive explanation, I am studying all your
suggestion. In fact I have been successful to develop an access database for
tracking supplier invoices and it is "go live" now.

Now my Japanese boss asked me again to create a lingk between an excell
spreadsheet in the Budget Control Section with the Revenue section and asked
if it is possible to make it in access.

But I think, since I want to make it quickly, so I intend to make it in
excell but my problem is this

there is a sheet contains these columns:
1. job number,
2 invoice amount,
3. payment date

This is filled out by revenue section on timely basis randomly( not
sequenced by job)

I alreadty created another sheet(summarySheet) contains summary using
"Sumif" for total ivoicec by project).

How can I make in the same summary sheet, the total collection by using
SumIF but fon only if the payment date exists( Not blank). I tried to to
combine 2 criteria 1 from summary sheet but it does not work for the total
collection by job number.

Thank you very much.

Frank

Song,

Conditional wizzard using sumif can only work if the criteria sits in the
same sheet, in my case one criteria is in the summary sheet, while the other
criteria is on the data sheet.

OK, to have the total for paid amount column, I already add 1 column for
paid amount using if statement to populate the cash payment and in the
summary sheet I can have the total payment for each job number. Sorry Song,
live is very hard in Indonesia, so we (accounting people) tried to do it
ourselves, actually it should have been IT dept. do this, but we do not have
IT dept in our company.

Thanks for all your idea.

Frank

"SongBear" wrote:


Frank
OK, so the data is not already in Access, I did not understand this from the
original question. And you do have experience in Access, which is good to
know as a starting point for future questions.

I think that there may be a quick answer but I need you to try it and tell
me if further help is needed. If further help is needed, I might be of more
help if I do not have to re-create a sample of your worksheets blindly to
test my suggestions.

I was just sitting here trying to imagine what your sheets looked like
exactly so I could work with the problem and suddenly realized, you need to
meet an old friend of mine.
This old friend is called the Conditional Sum Wizard. It helps you create
multiple level sum-if formulas and it might just be the fastest way to answer
your question. A lot better than writing a book.

You find the Conditional Sum Wizard by dropping and expanding your Tools
menu. It is an add-in that comes with Excel €“ I am pretty sure I have seen it
at least since XL97, not sure.

If the conditional sum wizard is not showing, then click Add Ins and check
it to install it.
Then follow the wizard.

Let me know if this is what you needed; if this does not get it done, we can
keep working on it here.
SongBear

"Frank Situmorang" wrote:

thanks song for your extensive explanation, I am studying all your
suggestion. In fact I have been successful to develop an access database for
tracking supplier invoices and it is "go live" now.

Now my Japanese boss asked me again to create a lingk between an excell
spreadsheet in the Budget Control Section with the Revenue section and asked
if it is possible to make it in access.

But I think, since I want to make it quickly, so I intend to make it in
excell but my problem is this

there is a sheet contains these columns:
1. job number,
2 invoice amount,
3. payment date

This is filled out by revenue section on timely basis randomly( not
sequenced by job)

I alreadty created another sheet(summarySheet) contains summary using
"Sumif" for total ivoicec by project).

How can I make in the same summary sheet, the total collection by using
SumIF but fon only if the payment date exists( Not blank). I tried to to
combine 2 criteria 1 from summary sheet but it does not work for the total
collection by job number.

Thank you very much.

Frank

Frank,
I was able to make the Conditional Sum Wizard work across two worksheets, I
did not try it across two workbooks. As I note below, the Conditional Sum Wiz
creates an array formula, a special formula in Excel that stops being an
array formula when modified, and I nearly always have to modify the formula a
little. You turn the modified formula back into an array formula by hitting
control-shift-enter.

The following is based on a test setup I created in two worksheets in the
same workbook. The results of these formula examples from my test setup are
depicted below the formulas and discussions.


Total Invoiced Per Job
(Notes: these formulas are in cells on the Summary sheet; these are not
array formulas (see below where we do use array formulas); these formulas use
a criteria defined in the Summary sheet (column C), and each formula uses
both a criteria-match range and a numbers-to-sum range in the Data sheet)
=SUMIF(DataSht!$C$7:$C$36,SummarySht!C6,DataSht!$D $7:$D$36)
=SUMIF(DataSht!$C$7:$C$36,SummarySht!C7,DataSht!$D $7:$D$36)
=SUMIF(DataSht!$C$7:$C$36,SummarySht!C8,DataSht!$D $7:$D$36)
=SUMIF(DataSht!$C$7:$C$36,SummarySht!C9,DataSht!$D $7:$D$36)
=SUMIF(DataSht!$C$7:$C$36,SummarySht!C10,DataSht!$ D$7:$D$36)


Total Paid Per Job
(Notes: these multiple criteria formulas are all array formulas; these
formulas are in cells on the Summary sheet; the match-range and the sum-range
are on the Data sheet. One criteria is defined in Column C of the local
(Summary) sheet, the other criteria is simply looking for 'greater than zero'
in the date range.)
{=SUM(IF(DataSht!$C$7:$C$36=C6,IF(DataSht!$E$7:$E$ 36
0,DataSht!$D$7:$D$36,0),0))}
{=SUM(IF(DataSht!$C$7:$C$36=C7,IF(DataSht!$E$7:$E$ 36
0,DataSht!$D$7:$D$36,0),0))}
{=SUM(IF(DataSht!$C$7:$C$36=C8,IF(DataSht!$E$7:$E$ 36
0,DataSht!$D$7:$D$36,0),0))}
{=SUM(IF(DataSht!$C$7:$C$36=C9,IF(DataSht!$E$7:$E$ 36
0,DataSht!$D$7:$D$36,0),0))}
{=SUM(IF(DataSht!$C$7:$C$36=C10,IF(DataSht!$E$7:$E $36
0,DataSht!$D$7:$D$36,0),0))}
More Notes: You can not put the curly brackets on an array formula; if you
type curly brackets in it will not work - Excel has to do it. Enter or modify
the formula (which removes the brackets), then, when finished €“ or when you
want to test it - make sure the cursor is in the formula in the formula bar
and hit the simultaneous three key combination of Shift-Control-Enter.

I made the first of these particular formulas using the Conditional Sum
Wizard, but had to modify the formula to make it work the way I wanted it to.
I then drag-copied the formula down into the other four cells.

The Conditional Sum Wiz creates an array formula for you. I normally use the
wizard to build a basic formula and nearly always have to modify it. When I
built this formula with the wizard, I used the second option at the end of
the wizard, which is to let the wizard define cells in which to put criteria.
This way, for the job number criteria, I clicked the cell with the job number
on the same row in the short list of jobs. The wizard asked me if I wanted to
replace the current contents of that cell with the job number that I had
selected earlier in the wizard. I went ahead and allowed this and then just
typed the correct number back in that job number cell.

This caused the wizard to create a cell reference to the job number cell for
that row.

When the next criteria came up in the wizard, I knew I was going to modify
it, so I just clicked an empty cell. When the wizard was finished, I modified
the formula to replace the reference in the formula to the empty cell with
€œ0€� (Greater than zero). Since the formula was modified, it was no longer an
array formula and I had to Ctl-Shft-Enter the formula as previously mentioned.


I created a fake data sheet to test this; the results are depicted below.
Row and Column numbers are as given.
Note that the checksum (Total paid) for each worksheet is the same,
indicating that the formulas are working. Now that the sheets are set up, all
I have to do is enter a new date in the Payment Date column of the Data sheet
and the job total on the Summary sheet and the totals in both sheets
checksums change automatically.

The assumptions we 1. Each job can have multiple invoices; 2. Each
invoice is paid in full when paid, no partial payments per invoice. 3.
Invoices are recorded at the time of initiation of the transaction; payments
can be made and recorded later. 4. The bottom limit of the columns of
invoices is arbitrary; you would extend the selection in the formulas to
include enough rows to hold all of the data for the number of invoices you
expect to have there. (in other words, instead of stopping your formula at
row 36, extend it to row 200 or row 1000, or some appropriate number. It will
work just as well and this will prevent one reason for needing to modify the
formula later.

If this helps, answers the question, or if it requires clarification, please
let us know.

SongBear

Frank, one more thing: As you have noticed, the computer is a tool which
increases capability, efficiency and productivity, reduces mistakes, and can
help make your company (and yourself) more competitive in the marketplace.
But, as we entrust more and more mission critical data to it, the computer
has a tendency to lull us into a false sense of safety. Catastrophic failure
can still happen, a hard disk or a computer can still fail, wind storms,
floods, fire, etc. can destroy data in a single location.

In the absence of an IT department, it falls to you to back up critical
data, preferably in several different media forms, as part of an overall
disaster recovery plan. Secondary hard drives in the same computer is the
minimum, with copies of data on hard drives in multiple computers being
better. Also data burned on CD or DVD, data stored on €œmemory sticks€�,
external stand alone hard drives; with all of the portable media stored
off-site in professional external storage sites, in an on-site fire-proof
safe, in bank deposit boxes, etc.

Advice that you did not ask for: Figure out what is appropriate for your
local economic/cultural/business infrastructure and for your companys
economic/cultural/business infrastructure and become your bosss ally in
creating, maintaining, and promoting a competent disaster recovery plan for
the company.

The TEST Worksheets (both worksheets in the same book)

Worksheet: SummarySht (Summary Sheet)
Checksum formula: =SUM(E6:E10)

C D E
3 SummarySht Total Paid: $661.60
4
5 Total
Invoiced Total Paid
Job Per Job Per Job
6 12132 $582.79 $-
7 25252 $639.25 $222.65
8 3654 $1,161.71 $-
9 6985 $845.17 $179.96
10 325785 $848.63 $259.00



Worksheet: DataSht (Data Sheet)
Checksum formula: =SUMIF(E7:E36,"0",D7:D36) [note: not an array formula]

C D E
4 DataSht Total Paid $661.60
5
6 Job Invoice Payment
Number Amount Date

7 12132 $12.00
8 25252 $14.00 1/3/2007
9 3654 $145.00
10 6985 $26.00 2/12/2007
11 325785 $35.00
12 12132 $33.00
13 25252 $52.00
14 3654 $458.00
15 6985 $22.00
16 325785 $258.00 3/3/2007
17 12132 $45.00
18 25252 $65.00 4/1/2007
19 3654 $35.00
20 6985 $258.00
21 325785 $1.00 2/2/2007
22 12132 $138.50
23 25252 $143.65 1/15/2007
24 3654 $148.80
25 6985 $153.96 3/9/2007
26 325785 $159.11
27 12132 $164.26
28 25252 $169.42
29 3654 $174.57
30 6985 $179.72
31 325785 $184.88
32 12132 $190.03
33 25252 $195.18
34 3654 $200.34
35 6985 $205.49
36 325785 $210.65



***********END***********


"Frank Situmorang" wrote:

Song,

Conditional wizzard using sumif can only work if the criteria sits in the
same sheet, in my case one criteria is in the summary sheet, while the other
criteria is on the data sheet.

OK, to have the total for paid amount column, I already add 1 column for
paid amount using if statement to populate the cash payment and in the
summary sheet I can have the total payment for each job number. Sorry Song,
live is very hard in Indonesia, so we (accounting people) tried to do it
ourselves, actually it should have been IT dept. do this, but we do not have
IT dept in our company.

Thanks for all your idea.

Frank

"SongBear" wrote:


Frank
OK, so the data is not already in Access, I did not understand this from the
original question. And you do have experience in Access, which is good to
know as a starting point for future questions.

I think that there may be a quick answer but I need you to try it and tell
me if further help is needed. If further help is needed, I might be of more
help if I do not have to re-create a sample of your worksheets blindly to
test my suggestions.

I was just sitting here trying to imagine what your sheets looked like
exactly so I could work with the problem and suddenly realized, you need to
meet an old friend of mine.
This old friend is called the Conditional Sum Wizard. It helps you create
multiple level sum-if formulas and it might just be the fastest way to answer
your question. A lot better than writing a book.

You find the Conditional Sum Wizard by dropping and expanding your Tools
menu. It is an add-in that comes with Excel €“ I am pretty sure I have seen it
at least since XL97, not sure.

If the conditional sum wizard is not showing, then click Add Ins and check
it to install it.
Then follow the wizard.

Let me know if this is what you needed; if this does not get it done, we can
keep working on it here.
SongBear

"Frank Situmorang" wrote:

thanks song for your extensive explanation, I am studying all your
suggestion. In fact I have been successful to develop an access database for
tracking supplier invoices and it is "go live" now.

Now my Japanese boss asked me again to create a lingk between an excell
spreadsheet in the Budget Control Section with the Revenue section and asked
if it is possible to make it in access.

But I think, since I want to make it quickly, so I intend to make it in
excell but my problem is this

there is a sheet contains these columns:
1. job number,
2 invoice amount,
3. payment date

This is filled out by revenue section on timely basis randomly( not
sequenced by job)

I alreadty created another sheet(summarySheet) contains summary using
"Sumif" for total ivoicec by project).

How can I make in the same summary sheet, the total collection by using
SumIF but fon only if the payment date exists( Not blank). I tried to to
combine 2 criteria 1 from summary sheet but it does not work for the total
collection by job number.

Thank you very much.

Frank