Match a value to multple criteria both in rows and columns
 

In my source data sheet, starting in A1, I have the following information:

Property Dept Total Reported Wk 1 Wk 2 Wk 3 Wk4
ABC Finance 7 1 2 4 0
ABC Sales 2 1 1 0 0
XYX Finance 85 22 26 23 14

I then have one worksheet for each property (property name in A1), with the
departments vertically listed, starting in column C:

Department Total Reported Wk 1 Wk 2 Wk 3 Wk 4
HR
Finance
Sales

I need for cells D2:H2 to look up the property name from A1, the department
from C2 and then fill in the matching values for Total Reported and Wks 1-4.
I want it to be blank or display a 0 if there are no corresponding values
(i.e. HR has no reports).

Is this possible? I'd prefer to automate with a function rather than an
advanced filter paste that would require almost as much work as just
copying/pasting the data from the data source sheet to the property-specific
sheet.

Thanks in advance!

Match a value to multple criteria both in rows and columns
 

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"GreenDriver" wrote in message
...
In my source data sheet, starting in A1, I have the following information:

Property Dept Total Reported Wk 1 Wk 2 Wk 3 Wk4
ABC Finance 7 1 2 4 0
ABC Sales 2 1 1 0
0
XYX Finance 85 22 26 23 14

I then have one worksheet for each property (property name in A1), with
the
departments vertically listed, starting in column C:

Department Total Reported Wk 1 Wk 2 Wk 3 Wk 4
HR
Finance
Sales

I need for cells D2:H2 to look up the property name from A1, the
department
from C2 and then fill in the matching values for Total Reported and Wks
1-4.
I want it to be blank or display a 0 if there are no corresponding values
(i.e. HR has no reports).

Is this possible? I'd prefer to automate with a function rather than an
advanced filter paste that would require almost as much work as just
copying/pasting the data from the data source sheet to the
property-specific
sheet.

Thanks in advance!

Match a value to multple criteria both in rows and columns
 

=IF(ISNA(VLOOKUP(..........)),"",VLOOKUP(......... .))


"GreenDriver" wrote:

In my source data sheet, starting in A1, I have the following information:

Property Dept Total Reported Wk 1 Wk 2 Wk 3 Wk4
ABC Finance 7 1 2 4 0
ABC Sales 2 1 1 0 0
XYX Finance 85 22 26 23 14

I then have one worksheet for each property (property name in A1), with the
departments vertically listed, starting in column C:

Department Total Reported Wk 1 Wk 2 Wk 3 Wk 4
HR
Finance
Sales

I need for cells D2:H2 to look up the property name from A1, the department
from C2 and then fill in the matching values for Total Reported and Wks 1-4.
I want it to be blank or display a 0 if there are no corresponding values
(i.e. HR has no reports).

Is this possible? I'd prefer to automate with a function rather than an
advanced filter paste that would require almost as much work as just
copying/pasting the data from the data source sheet to the property-specific
sheet.

Thanks in advance!

Match a value to multple criteria both in rows and columns
 

Assuming that your data sheet is named €œMy Source€�, then copy this formula to
the report sheet cell D3 and copy it to the adjacent cells as needed.

=SUMPRODUCT(('My Source'!$A$1:$A$65000=$A$1)*('My
Source'!$B$1:$B$65000=Report!$C3),('My Source'!C$1:C$65000))


Tom