Excel Function

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

Peo Sjoblom

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"
is entered in any row on column A and "1" is entered in column C in the same
row on Worksheet 2.
 
G

Guest

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"
is entered in any row on column A and "1" is entered in column C in the same
row on Worksheet 2.
 
F

Frank Kabel

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top