Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about functions
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about functions
=IF(B3="the word","X","")
Adjust to suit "Spamn" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about functions
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. "Teethless mama" wrote: =IF(B3="the word","X","") Adjust to suit "Spamn" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about functions
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. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Spamn" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about functions
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 "Martin Fishlock" wrote: 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. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Spamn" wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about functions
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. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Spamn" wrote: 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 "Martin Fishlock" wrote: 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. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Spamn" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about functions
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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Spamn" wrote in message ... 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 "Martin Fishlock" wrote: 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. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Spamn" wrote: 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about functions
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 "Bernard Liengme" wrote: 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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Spamn" wrote in message ... 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 "Martin Fishlock" wrote: 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. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Spamn" wrote: 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about functions
If I understand correctly, try
=if((COUNTIF(Sheet2!rangeA,B2)+COUNTIF(Sheet2!rang eB,B2)+COUNTIF(Sheet2!rangeC,B2))0,"X","") where RangeA, etc, have syntax in the form $A$1:$Z$20 best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Spamn" wrote in message ... 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 "Bernard Liengme" wrote: 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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Spamn" wrote in message ... 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 "Martin Fishlock" wrote: 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. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Spamn" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visible rows and functions that work | Excel Worksheet Functions | |||
Improvements for text finding functions | Excel Discussion (Misc queries) | |||
Database Functions - question using formulas as criteria | Excel Worksheet Functions | |||
Default User Defined Functions - How? | Excel Discussion (Misc queries) | |||
PASTE DOWN FUNCTIONS | Excel Worksheet Functions |