Question about functions

G

Guest

I'm not sure if I'm trying to use the right function or if I'm just messing
it up.

I'm trying to make a different version of a sheet that I already have. I
want to make a function that will search my other sheet for a word and if
that word is on that sheet, it will put an "X" in that box of the new sheet.

So far I have =IF(B3=, "X", " ")

It works if after the = you put the exact box that the word is in, but I
can't get it to search the whole sheet for that word.


Any help would be appreciated. Thank you.
 
G

Guest

Thank you for the speedy reply. That doesn't exactly solve my problem though.
I guess I explained it wrong.

I want the function to search a seperate sheet for a certain word and if
that word is on that sheet, then put an X in the box on the current sheet. I
know that B3 (in this example) has that word in it, I want to see if the
other sheet has that same word on it.
 
M

Martin Fishlock

Hi Spam N:

Try using the countif function.

=if(COUNTIF(Sheet2!$1:$65536,"hello")>0,"X","")

You need to change the last row if you are working in the lastest version of
excel.
 
G

Guest

Ah, I was using the wrong function. Thank you. I took that and modified it a
bit obviously (actual name of the sheet to search and "hello" to B3). Worked
perfect. Now if there was only a way to put that function in every box and
not have to type the cell to match to each time. IE B3, C3, D3.

Thanks
 
M

Martin Fishlock

If the function is in say cell B4 then if you copy if across it should
automatically update?

[B4]=if(COUNTIF(Sheet2!$1:$65536,B3)>0,"X","")

Select cell b4 and the cells to the right that you want to fill and then do
Ctrl+R or Edit->Fill->Right.
 
B

Bernard Liengme

In B2, C2, D2 enter the words
In the example, enter the word "hello" in B2 (no quotes, of course)
In B3 use =if(COUNTIF(Sheet2!$1:$65536,B2)>0,"X","")
Copy across to D3 for the other words.
best wishes
 
G

Guest

This works great, but I need it to only search certain cells on the sheet. I
tried to just select those cells and control-click another group but Excel
tells me "You've entered too many arguements for this function"

Do I need to make a function for each range of cells to search? If so, how
do I put more than one function in a single cell.

Thank you
 
B

Bernard Liengme

If I understand correctly, try
=if((COUNTIF(Sheet2!rangeA,B2)+COUNTIF(Sheet2!rangeB,B2)+COUNTIF(Sheet2!rangeC,B2))>0,"X","")
where RangeA, etc, have syntax in the form $A$1:$Z$20
best wishes
 
Top