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 InformationAny suggestions, please?

✔ 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 toanchorcertain 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

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

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?

Sorry, still don't understand. Post a short example of

Bothsheets, 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

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

With your data as shown in #4

On 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?

MIKE

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.

Oh my god, you've cracked it! Thank you so much Mike! This has been driving me crazy.

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 toanchorcertain 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

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History