# 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

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 cellstry this:=IF(AND(COUNTIF('WP Rpt'!\$E\$1:\$E\$7,\$A2),COUNTIF('WP Rpt'!\$A\$1:\$A\$7,C\$1)),"X","")Same formula, almost.MIKEhttp://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 andthe 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 rowthen try this:=IF(AND(A2='WP Rpt'!E2,C1='WP Rpt'!A1),"x","")MIKEhttp://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...MIKEhttp://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 #4On Sheet A, Cell 2Enter 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?MIKEhttp://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