Solved Two criteria in another sheet = X

July 11, 2012 at 06:25:12
Specs: Windows 7
I'd like to build a formula in C2. If the data in A2 matches data in column E of [other sheet called] WP Rpt, and the data in C1 matches data in column A [other sheet called] WP Rpt, return an "X" in C2.

A B C D
1 Filename Projectstatus 121 1200
2 11c197_3D1 Information ___
3 11c197_3D2 Information

Any suggestions, please?


See More: Two criteria in another sheet = X

Report •


✔ Best Answer
July 11, 2012 at 10:03:29
Oh my god, you've cracked it!

Not quite, the formula only works for cell C2.
If you try dragging it across or down, it will
give false returns.

To be able to drag it across & down,
you need to anchor certain cells
try this:

=IF(AND(COUNTIF('WP Rpt'!$E$1:$E$7,$A2),COUNTIF('WP Rpt'!$A$1:$A$7,C$1)),"X","")

Same formula, almost.

MIKE

http://www.skeptic.com/



#1
July 11, 2012 at 07:03:20
Not sure I completely understand what your doing, as you reference Two sheets,
but give examples of only One sheet.

If the data in A2 matches data in column E of [other sheet called] WP Rpt,

If the Data you wish to match is also in Row 2 of sheet WP Rpt

and

the data in C1 matches data in column A [other sheet called] WP Rpt,

If the Data you wish to match is in Row 1, of sheet WP Rpt, your heading row

then try this:

=IF(AND(A2='WP Rpt'!E2,C1='WP Rpt'!A1),"x","")

MIKE

http://www.skeptic.com/


Report •

#2
July 11, 2012 at 07:23:32
Thanks for your reply. I really didn't explain it very well, sorry. Let me try again.

Column A on the current sheet lists document filenames.
The headings along Row 1 of the current sheet (beginning at Column C) are WP references. These are numerically in order, the first few being 121 in C1, 1200 in D1, 1215 in E1, and so on.

In another sheet, called WP Rpt, I have a full report on all associations between WP number (the 121, 1200, 1215, etc) and the filenames they relate to. This means if I sort them by WP, all the filenames with that association will be listed, so there are multiple WP associations to each filename. In Column A, all the WPs are listed. In Column E, all the filenames are listed.

The first sheet will (hopefully) be a concise record of WP associations already applied (indicated by an X) so the users can choose other WPs that may apply, which haven't yet been applied (saving them choosing all of the WP associations every time a new revision comes in).

Does that better explain it?


Report •

#3
July 11, 2012 at 07:44:34
Sorry, still don't understand.

Post a short example of Both sheets, with row # and column letters.

BUT, first read this How-To, it explains how to post your data in this forum.

http://www.computing.net/howtos/sho...

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
July 11, 2012 at 08:17:02
Sheet A
	A	 	 B              C	D	E
1	File Name	Projectstatus	121	1200	1215
2	0121324 Gas Man	Information		
3	0121324 Remedia	Tender (Checked)			
4	0121324 Remedia	Information			
5	11c197_3D1	Information			
6	11c197_3D2	Information			

Sheet B (called WP Rpt)
	A	B	C		D		E
1	121	1883	Documents	Support		0121324 Gas Man
2	2400	2146	Documents	Superstructure	0121324 Gas Man
3	2140	1883	Specifications	Remediation	0121324 Remedia
4	2140	1883	Drawings	Remediation	11c197_3D1
5	2400	2146	Drawings	Superstructure	11c197_3D1
6	2140	1883	Documents	Remediation	11c197_3D2
7	2400	2146	Documents	Superstructure	11c197_3D2


Report •

#5
July 11, 2012 at 09:29:16
With your data as shown in #4
On Sheet A, Cell 2

Enter the formula:

=IF(AND(COUNTIF('WP Rpt'!E1:E7,A2),COUNTIF('WP Rpt'!A1:A7,C1)),"X","")

What about columns D & E on Sheet A?

MIKE

http://www.skeptic.com/


Report •

#6
July 11, 2012 at 09:37:10
The blanks in sheet A are where Xs would/would no appear, so these would be the cells I'd be entering the formula in. So, it would be in C2, C3, C4, C5, C6, D2, D3, D4, D5, D6, etc.

Report •

#7
July 11, 2012 at 09:48:30
Oh my god, you've cracked it! Thank you so much Mike! This has been driving me crazy.

Report •

#8
July 11, 2012 at 10:03:29
✔ Best Answer
Oh my god, you've cracked it!

Not quite, the formula only works for cell C2.
If you try dragging it across or down, it will
give false returns.

To be able to drag it across & down,
you need to anchor certain cells
try this:

=IF(AND(COUNTIF('WP Rpt'!$E$1:$E$7,$A2),COUNTIF('WP Rpt'!$A$1:$A$7,C$1)),"X","")

Same formula, almost.

MIKE

http://www.skeptic.com/


Report •

Ask Question