Excel Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

Wondering if anyone can help me with my worksheet...

I need it so that cell A1 on Worksheet 1 displays an 'X' if and when "A" is entered in any row on column A and "1" is entered in column C in the same row on Worksheet 2.

Does this make sense?
 
One way

=IF(SUMPRODUCT(--EXACT(Sheet2!A1:A1000,"A"),--(Sheet2!C1:C1000=1))>0,"X","")

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



Lee Kelly said:
Hi

Wondering if anyone can help me with my worksheet...

I need it so that cell A1 on Worksheet 1 displays an 'X' if and when "A"
Click to expand...
is entered in any row on column A and "1" is entered in column C in the same
row on Worksheet 2.
 
Your initial formula worked a treat. However, here's were it gets tricky. This is the formula I now have in one of my cells...

=IF(SUMPRODUCT(--('WE 30.07.04'!$A24:$A100=51),--('WE 30.07.04'!$C24:$C100=1)),"X","")

What I also need if for the formula to populate the numbers it is searching for (ie "51" and "1"), based on which cell it is in, in the table I have created.
--
Thanks in advance


Peo Sjoblom said:
One way

=IF(SUMPRODUCT(--EXACT(Sheet2!A1:A1000,"A"),--(Sheet2!C1:C1000=1))>0,"X","")

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



Lee Kelly said:
Hi

Wondering if anyone can help me with my worksheet...

I need it so that cell A1 on Worksheet 1 displays an 'X' if and when "A"
Click to expand...
is entered in any row on column A and "1" is entered in column C in the same
row on Worksheet 2.
Click to expand...
 
Hi
if you for example put this formula in column B and column A stores
your search value try for B1:
=IF(SUMPRODUCT(--('WE 30.07.04'!$A24:$A100=A1),--('WE
30.07.04'!$C24:$C100=1)),"X","")

and copy down
 
Back
Top