sumproduct .. validating 2 criterias (multiple tabs)

[Chuck]

hi guys,

i am trying to add additional functionality to my Project Budget
Expendature worksheet of which some of you may have seen.

anyways, the idea is to have sumproduct calculate all items that have
the same GL code & date of which it was purchased (date of purchase)

currently i have the following
TAB: Spend Calendar
ROW C8:N8 = Month #'s (Jan, feb ect)
COL B13:B36 = GL Codes (130000,190000 ect)

TAB: 001-013 (13 tabs)
CELL C6 = Date of Purchase
COL C24:C37 = GL Codes of each item within the purchase
COL J24:J37 = Total cost of the item which the GL Code is related to

can someone advise as to what the sumproduct formula would be for it
to validate the GL Code AND month of the purchase to provide the total
spend of that month?

someone here had provided this formula (to validate something else,
but looks relevant some how)
=SUMPRODUCT((SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"000")&"'!$C
$24:$C$37"),$B56,INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"000")&"'!$J
$24:$J$37")))
but not sure how to manipulate this to achieve what i am looking for

cheers

 

[Bernie Deitrick]

Chuck,

My standard advice in this type of situation is to bite the bullet, and combine all the sheets into
one sheet, with an additional column that contains the original sheet name. Using multiple tabs for
the same type of data is simply bad design - use one database (on one tab), and then filter or Pivot
the data to get the view that you want.

There are many examples of macros that you can use to combine your data tables. Below is one, with
the assumption that the data tables start in cell A1 of each sheet and are contiguous - no entirely
blank rows or columns. Also, the heading should be in row 1, the same for every sheet.

HTH,
Bernie
MS Excel MVP

Sub ConsolidateSheetsIntoDataBase()
With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

Dim i As Integer
Dim myDB As Worksheet

Set myDB = Worksheets.Add(Before:=Worksheets(1))
myDB.Name = "DataBase Sheet"

Worksheets(2).Cells.Copy Worksheets(1).Cells

With Worksheets(1)
.Cells(1, 1).EntireColumn.Insert
.Cells(1, 1).Value = "Department"
Intersect(.Range("A2:A" & .Rows.Count), _
.UsedRange).Value = Worksheets(2).Name
End With

For i = 3 To Worksheets.Count
With Worksheets(1)
myRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
Worksheets(i).Cells(1, 1).CurrentRegion.Offset(1) _
.Copy Worksheets(1).Cells(myRow, 2)
Intersect(.Range("A" & myRow & ":A" & .Rows.Count), _
.UsedRange).Value = Worksheets(i).Name
End With
Next i

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub

 

[Chuck]

hi Bernie

thanks for your comments. unfortunately i have to use multiple tabs
for this particular sheet as each purchase is concidered seperate. now
just trying to create a consolidated view of those purchases of each
code by each month. this was never requested before, but found out it
was a big thing for the PM's i work with (to include myself) to do
some type of reporting (as our finance system lacks any type of
accurate reporting, heck reporting in general)

also, the tabs must be maintained as each time i / other PM's make a
purchase, the tab is sent to the finance department to propigate based
on the details of those tabs

ill try and post this on the other NG to see other ideas

cheers
Chuck

 

[Bernie Deitrick]

Chuck,

also, the tabs must be maintained as each time i / other PM's make a
purchase, the tab is sent to the finance department to propigate based
on the details of those tabs

Create one tab, with a cell for a key number, and formulas that extract data from your database
based on the key number. Put all your headers, text, comments, and data fields wherever you want
them.

So, let's say you have your data base on a sheet DataBase, and it looks like this:

Key Data1 Data2 Data3
Key1 Value1-1 Value2-1 Value3-1
Key2 Value1-2 Value2-2 Value3-2

On your report sheet, use VLOOKUP formulas wherever you want data, like

=VLOOKUP($A$1,DataBase!$A$1:$D$10000,2,False)
=VLOOKUP($A$1,DataBase!$A$1:$D$10000,3,False)
=VLOOKUP($A$1,DataBase!$A$1:$D$10000,4,False)

Enter Key1 into cell A1 to return Value1-1 , Value2-1, and Value3-1 with the above formulas.

Then, to send the data to the finance department, enter your key value into cell A1, copy that
sheet, convert to values, and you're done.

You will have your database, and the ability to create a report based on any set of data in the
database.

HTH,
Bernie
MS Excel MVP