Excel Work Books

[Gary Gibbons]

Hi group;
I'm something of a newbie to Excel and have come across a problem that
has been causing some headaches for my organization.
The problem is this:
We have a budget workbook with 2 worksheets-one for input of revenue
sources that tracks invoices, and a 2nd sheet that holds the annual
projected budget, tracks the monthly totals for each revenue source,
and keeps track of
the budget amount as it is reduced by revenue in put.

The data input which tracks each invoice has 7 columns that represent
invoice #, revenue source code #, individual's name that is paying;
paying department name, and invoice total.

The budget tracker sheet has columns representing the revenue source,
the correlating source number, the months of the year, projected
revenue, current revenue, and the differential. There are 37 rows
holding the individual revenue source names, and each of the
correlating numbers in adjacent cells.

The problem is this:
All entries must be duplicated manually from the input sheet over to
the tracker sheet. Very wasteful of time and inaccurate.
What I am attempting to do is write a macro, or VBA script that will
allow the user to input data on the input sheet, and have it
automagicaly update the budget tracking sheet.

So far:
I have tried recording a macro that directly inputs the info- the
problem is the sheets aren't synchronized, nor have I figured out how
to tell the macro it must use a specific identifier for each entry and
combine the new with the previous. I have been looking through VBA
script, and have some ideas-including re-formatting the row/column
layout; but so far, the scripts I have looked at and tried don't show
a key piece of what needs to happen, which is selecting the
corresponding cell in the budget tracker to place the information from
the input sheet, and perform the required SUM function.

Here is a bit of the design:

Input sheet
__________________________________________________________________________
Invoice # | Project Code # | Month | Descr | Manager | Dept |Invoice
Total|
__________________________________________________________________________
SCC-001 | CC20103 | Jan | txt | Jones | DPR | $30,000.


Budget Tracker sheet
_____________________________________________________________________________
Source Name | Project Code | Estimated Rev. |Jan |~|TotBilled| Var
_____________________________________________________________________________
DPR | CC20103 | $50,000.00 |$30K|~|$30,000. |($20K)

Info is entered into the input sheet as invoices are created. That
currency figure is then manually entered into the budget tracker for
each month an invoice is created. The problem is that if there are
multiple entries for the same project code in a month, the numbers
must also be summed manually and then entered. This is where the
problems with accuracy and relationship arise.
I want to enter the figures into the input sheet only, and have the
budget tracker update from that activity automatically.

I would prefer to do this with Access or SQL, but we are restricted to
Excel for the time being.

I'm also condidering a re-write into VB or VB.Net to establish a user
form font end, and use Excel as a database backend.

Any ideas or input will be most appreciated!!

 

[Dick Kusleika]

Gary

I think you want to use array formulae, and if I understand you correctly,
you shouldn't need any VBA. Here's a nice description of array formulae:

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

Input sheet
__________________________________________________________________________
Invoice # | Project Code # | Month | Descr | Manager | Dept |Invoice
Total|
__________________________________________________________________________
SCC-001 | CC20103 | Jan | txt | Jones | DPR | $30,000.

I'm assuming that this sheet is many rows long, that the invoice number,
description and amount are unique and that the proj code, month, manager and
department are not unique and are from some pre-defined list (e.g. only so
many departments)

Budget Tracker sheet
____________________________________________________________________________
_
Source Name | Project Code | Estimated Rev. |Jan |~|TotBilled| Var
____________________________________________________________________________
_
DPR | CC20103 | $50,000.00 |$30K|~|$30,000. |($20K)

On this sheet, I'm assuming that you have one line for every combination of
source name and project code that has an estimate revenue figure. I'm also
assuming that the tilde after Jan means that you have one column for every
month. That's a lot of assumptions, I know.

If I wanted a cell in the Budget sheet to add up to all the January invoices
where DPR was the Source and CC20103 was the Proj Code, then I would right a
formula like this (More assumptions: The input is on a sheet called
"Input"; DPR and CC20103 are in A2 and B2, respectively, and the date is the
column header in D1; and the range of invoices on the input sheet is
G2:G1000)

=SUM((Input!$B$2:$B$1000=B2)*(Input!$F$2:$F$1000=A2)*(Input!$C$2:$C$1000=D$1
)*(Input!$G$2:$G$1000))

Here's the english version: If the project code = CC20103 AND the source
name = DPR AND the Month = Jan, then include the Invoice Total column in the
sum. Note that you have to enter this formula with Control+Shift+Enter, not
just enter.

Read Chip's page on arrays and what I've written here, and come back with
any questions. Hopefully I've understood what you're trying to do and this
will get you started in the right direction.

 

[Gary Gibbons]

Thank you very much Mr.Kusleika! That did the trick with just a bit of
trouble-shooting on my part. The link to the Array Formula article was
informative, and with your example I made the change on some sample
sheets, and everything worked beautifully!
You made the correct assumptions in your discussion, and everything fit
with little corrections on my part.
Having this logic barrier come down for me is great also-definite "aha"
moment!
It opens up some interesting things for my project now that this is
resolved. Maybe I'll work out a front end in VB.Net, and see where I go
with that!

Thanks again!