G
Greg in CO
Hi All!
This is a follow-up with more information to an earlier posting, where I got
asome great help from the folks here.
I have a workbook with many sheets, some for projects, some for departments.
I am trying to have a formula in a cell on the department sheet that will go
out to the project sheet and return a sum for a set of critiera, by month.
Data Info:
Department Sheet:
Column A contains the names of projects. This is populated by a drop menu.
There is a workseet for each project (the names match).
Column B contains a position (also from a drop menu)
Cell C3 is the Department Worksheet name.
Column E is the month of January.
The formula needs to be able to be copy/pasted across multiple columns
(months for several years)
Project Sheet:
Column B contains a department name (from a drop menu) that matches the
Department worksheet name.
Column C contains a position (same drop menu as on the Department sheet)
Column E is for the month of January.
I have formulas for both counting entries on the Project sheet that match
criteria and for summing that match criteria. The problem is that in order
to make the formula look at several Project sheets, I need to repeat the
formula, preceded by a "+" sign, which make the total entry very long. Also,
I have set up the formula using an INDIRECT, so that the cell reference to
the Project sheet name can contain any project name, not just a specific one
for that row. As the cell range in the INDIRECT is not dynamic, thus
preventing a copy/paste across rows where the range column indicators update
(i.e. e25:e50, f25:f50, etc.), the formula also has an OFFSET argument to
compensate for this.
I need to adapt the formulas to look for both the Department Name and the
Position across numerous project worksheets as well.
The current fomulas work, but are very cumbersome and looking across 20-30
project sheets, the file will get slower.
Is there a more elegant or terse formula to achieve the same results?
Summing formula:
=SUMIF((INDIRECT("'"&$A167&"'!$B$53:$B$311")),$C$3,OFFSET(INDIRECT("'"&$A167&"'!$e$53:e$311"),,COLUMNS($A:A)-1))
Logic: Look at A167 on the Department sheet, this is the Project name; go
the othe appropriate Project sheet and scan B53:B311, looking for the entry
in Cell C3 on the Department sheet (the Department Name); where you find an
entry equating to C3 on the Project sheet, sum the corresponding entry on
that row in Column E. Return the Sum to the Department sheet.
In order to have this look across multiple projects, this formula must be
repeated following a "+" sign. 20 projects - 20 repeats of this formula,
with the first reference in the INDIRECT being the next row with a project
name.
Counting Formula:
=SUMPRODUCT(--(ISNUMBER(MATCH(ProjectA!$A$12:$A$34,$A$132:$A$160,0))),--(ProjectA!N$12:N$34<>""))
Logic: Look in Column A on SheetA and compare it with entries in Column A on
Sheet B and if there are any matches, then look in Column N on SheetA and if
both
have entries and there is a match between the Column A entires on both
sheets, count the entries in Column N on SheetA which correspond to entries
in Column A on SheetA (which match the entries in Column A on Sheet B), then
place the count in a cell on Sheet B.
As above, to look across multiple projects, the formula must be repeated.
I would like to have this formula work using an INDIRECT, be able to be
copy/pasted across rows, and be able to compare two or more sets of criteria
in multiple ranges on the Department sheet against two or more sets of
criteria in multiple ranges on the project sheet, for multiple project sheets.
The logic would be something like this:
Look on any sheet named with an entry in Range 1, for any entry in Range 2,
Range 3, and Range 4, and where you have a match for Range 2,3, and 4, sum
the corresponding entry in Range 5 (a month column).
A variation would be the same logic, but returning a count of the entries.
I am not sure if this is possible or if it would make my file explode. My
end users are not Pivot Table friendly, so that is not an option. If this is
possible, I figured someone here would know how.
Thanks in advance for any help! I have learned so much from you folks!!!!
This is a follow-up with more information to an earlier posting, where I got
asome great help from the folks here.
I have a workbook with many sheets, some for projects, some for departments.
I am trying to have a formula in a cell on the department sheet that will go
out to the project sheet and return a sum for a set of critiera, by month.
Data Info:
Department Sheet:
Column A contains the names of projects. This is populated by a drop menu.
There is a workseet for each project (the names match).
Column B contains a position (also from a drop menu)
Cell C3 is the Department Worksheet name.
Column E is the month of January.
The formula needs to be able to be copy/pasted across multiple columns
(months for several years)
Project Sheet:
Column B contains a department name (from a drop menu) that matches the
Department worksheet name.
Column C contains a position (same drop menu as on the Department sheet)
Column E is for the month of January.
I have formulas for both counting entries on the Project sheet that match
criteria and for summing that match criteria. The problem is that in order
to make the formula look at several Project sheets, I need to repeat the
formula, preceded by a "+" sign, which make the total entry very long. Also,
I have set up the formula using an INDIRECT, so that the cell reference to
the Project sheet name can contain any project name, not just a specific one
for that row. As the cell range in the INDIRECT is not dynamic, thus
preventing a copy/paste across rows where the range column indicators update
(i.e. e25:e50, f25:f50, etc.), the formula also has an OFFSET argument to
compensate for this.
I need to adapt the formulas to look for both the Department Name and the
Position across numerous project worksheets as well.
The current fomulas work, but are very cumbersome and looking across 20-30
project sheets, the file will get slower.
Is there a more elegant or terse formula to achieve the same results?
Summing formula:
=SUMIF((INDIRECT("'"&$A167&"'!$B$53:$B$311")),$C$3,OFFSET(INDIRECT("'"&$A167&"'!$e$53:e$311"),,COLUMNS($A:A)-1))
Logic: Look at A167 on the Department sheet, this is the Project name; go
the othe appropriate Project sheet and scan B53:B311, looking for the entry
in Cell C3 on the Department sheet (the Department Name); where you find an
entry equating to C3 on the Project sheet, sum the corresponding entry on
that row in Column E. Return the Sum to the Department sheet.
In order to have this look across multiple projects, this formula must be
repeated following a "+" sign. 20 projects - 20 repeats of this formula,
with the first reference in the INDIRECT being the next row with a project
name.
Counting Formula:
=SUMPRODUCT(--(ISNUMBER(MATCH(ProjectA!$A$12:$A$34,$A$132:$A$160,0))),--(ProjectA!N$12:N$34<>""))
Logic: Look in Column A on SheetA and compare it with entries in Column A on
Sheet B and if there are any matches, then look in Column N on SheetA and if
both
have entries and there is a match between the Column A entires on both
sheets, count the entries in Column N on SheetA which correspond to entries
in Column A on SheetA (which match the entries in Column A on Sheet B), then
place the count in a cell on Sheet B.
As above, to look across multiple projects, the formula must be repeated.
I would like to have this formula work using an INDIRECT, be able to be
copy/pasted across rows, and be able to compare two or more sets of criteria
in multiple ranges on the Department sheet against two or more sets of
criteria in multiple ranges on the project sheet, for multiple project sheets.
The logic would be something like this:
Look on any sheet named with an entry in Range 1, for any entry in Range 2,
Range 3, and Range 4, and where you have a match for Range 2,3, and 4, sum
the corresponding entry in Range 5 (a month column).
A variation would be the same logic, but returning a count of the entries.
I am not sure if this is possible or if it would make my file explode. My
end users are not Pivot Table friendly, so that is not an option. If this is
possible, I figured someone here would know how.
Thanks in advance for any help! I have learned so much from you folks!!!!