| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Indirect function countif
Hi, i have a number in "C5" and i need to search for this number in another
workbook. I need to search only in column C:C. But i need an indirect function because the name of book2 will change every week. I will put in "N1" the name of other workbook. Can this be done? Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect function countif
Try this
=COUNTIF(INDIRECT("["&N1&"]"&"Sheet1!$C:$C"),C5) On Nov 14, 2:56*pm, puiuluipui wrote: Hi, i have a number in "C5" and i need to search for this number in another workbook. I need to search only in column C:C. But i need an indirect function because the name of book2 will change every week. I will put in "N1" the name of other workbook. Can this be done? Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect function countif
Hi, it's working, but it's looking only in one sheet. Can your code search in
5 sheets? And i need to display "yes" if the code finds a duplicate nr, and "no" if doesn't find a duplicates. Can this be done? Thanks!!!! "muddan madhu" wrote: Try this =COUNTIF(INDIRECT("["&N1&"]"&"Sheet1!$C:$C"),C5) On Nov 14, 2:56 pm, puiuluipui wrote: Hi, i have a number in "C5" and i need to search for this number in another workbook. I need to search only in column C:C. But i need an indirect function because the name of book2 will change every week. I will put in "N1" the name of other workbook. Can this be done? Thanks! . |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect function countif
=IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&"sheet"&{1,2,3} &"!"&"C:C")),M1))1,"YES","NO") On Nov 14, 4:58*pm, puiuluipui wrote: Hi, it's working, but it's looking only in one sheet. Can your code search in 5 sheets? And i need to display "yes" if the code finds a duplicate nr, and "no" if doesn't find a duplicates. Can this be done? Thanks!!!! "muddan madhu" wrote: Try this =COUNTIF(INDIRECT("["&N1&"]"&"Sheet1!$C:$C"),C5) On Nov 14, 2:56 pm, puiuluipui wrote: Hi, i have a number in "C5" and i need to search for this number in another workbook. I need to search only in column C:C. But i need an indirect function because the name of book2 will change every week. I will put in "N1" the name of other workbook. Can this be done? Thanks! . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect function countif
Hi, it's working except for sheet 1.
both workbooks have the same name for the sheets and i think it is a little bit confusing for excel. ex: workbook 1 and 2 sheets(same name for the sheets in both wbooks): LUNI MARTI MIERCURI JOI VINERI PLANNING SEARCH I don't want to search in "planning" and in "search". and i cand replacein your code, 1,2,3 with LUNI, MARTI....... What am i doing wrong? Thanks for your patience! Thanks! "muddan madhu" wrote: =IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&"sheet"&{1,2,3} &"!"&"C:C")),M1))1,"YES","NO") On Nov 14, 4:58 pm, puiuluipui wrote: Hi, it's working, but it's looking only in one sheet. Can your code search in 5 sheets? And i need to display "yes" if the code finds a duplicate nr, and "no" if doesn't find a duplicates. Can this be done? Thanks!!!! "muddan madhu" wrote: Try this =COUNTIF(INDIRECT("["&N1&"]"&"Sheet1!$C:$C"),C5) On Nov 14, 2:56 pm, puiuluipui wrote: Hi, i have a number in "C5" and i need to search for this number in another workbook. I need to search only in column C:C. But i need an indirect function because the name of book2 will change every week. I will put in "N1" the name of other workbook. Can this be done? Thanks! . . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect function countif
Try this
=IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&A2:A5&"!"&"C1:C10")),M1)) 1,"YES","NO") You need mention the sheet names in the Range A2:A5 Change the range according to the need. On Nov 14, 7:38*pm, puiuluipui wrote: Hi, it's working except for sheet 1. both workbooks have the same name for the sheets and i think it is a little bit confusing for excel. ex: workbook 1 and 2 sheets(same name for the sheets in both wbooks): LUNI MARTI MIERCURI JOI VINERI PLANNING SEARCH I don't want to search in "planning" and in "search". and i cand replacein your code, 1,2,3 with LUNI, MARTI....... What am i doing wrong? Thanks for your patience! Thanks! "muddan madhu" wrote: =IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&"sheet"&{1,2,3} &"!"&"C:C")),M1))1,"YES","NO") On Nov 14, 4:58 pm, puiuluipui wrote: Hi, it's working, but it's looking only in one sheet. Can your code search in 5 sheets? And i need to display "yes" if the code finds a duplicate nr, and "no" if doesn't find a duplicates. Can this be done? Thanks!!!! "muddan madhu" wrote: Try this =COUNTIF(INDIRECT("["&N1&"]"&"Sheet1!$C:$C"),C5) On Nov 14, 2:56 pm, puiuluipui wrote: Hi, i have a number in "C5" and i need to search for this number in another workbook. I need to search only in column C:C. But i need an indirect function because the name of book2 will change every week. I will put in "N1" the name of other workbook. Can this be done? Thanks! . . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect function countif
Hi, it's working if i use to workbook 2 a name like "BOOK2". I tried your
last code and it's working perfect with "BOOK2", but my second workbook's name is "02.11-06.11"(the name of second workbook will change every week, but this is the format:dd.mm-dd.mm).The name of the second workbook is a period of 5 days, from monday to friday. With this name, the code is not working anymore. Can you fix this last thing? Thanks allot! "muddan madhu" wrote: Try this =IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&A2:A5&"!"&"C1:C10")),M1)) 1,"YES","NO") You need mention the sheet names in the Range A2:A5 Change the range according to the need. On Nov 14, 7:38 pm, puiuluipui wrote: Hi, it's working except for sheet 1. both workbooks have the same name for the sheets and i think it is a little bit confusing for excel. ex: workbook 1 and 2 sheets(same name for the sheets in both wbooks): LUNI MARTI MIERCURI JOI VINERI PLANNING SEARCH I don't want to search in "planning" and in "search". and i cand replacein your code, 1,2,3 with LUNI, MARTI....... What am i doing wrong? Thanks for your patience! Thanks! "muddan madhu" wrote: =IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&"sheet"&{1,2,3} &"!"&"C:C")),M1))1,"YES","NO") On Nov 14, 4:58 pm, puiuluipui wrote: Hi, it's working, but it's looking only in one sheet. Can your code search in 5 sheets? And i need to display "yes" if the code finds a duplicate nr, and "no" if doesn't find a duplicates. Can this be done? Thanks!!!! "muddan madhu" wrote: Try this =COUNTIF(INDIRECT("["&N1&"]"&"Sheet1!$C:$C"),C5) On Nov 14, 2:56 pm, puiuluipui wrote: Hi, i have a number in "C5" and i need to search for this number in another workbook. I need to search only in column C:C. But i need an indirect function because the name of book2 will change every week. I will put in "N1" the name of other workbook. Can this be done? Thanks! . . . |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect function countif
try this
=IF(SUMPRODUCT(COUNTIF((INDIRECT("'["&TEXT(N1,"0") &"]"&A2:A5&"'!"&"C:C")),M1))1,"YES","NO") On Nov 15, 2:46*am, puiuluipui wrote: Hi, it's working if i use to workbook 2 a name like "BOOK2". I tried your last code and it's working perfect with "BOOK2", but my second workbook's name is "02.11-06.11"(the name of second workbook will change every week, but this is the format:dd.mm-dd.mm).The name of the second workbook is a period of 5 days, from monday to friday. With this name, the code is not working anymore. Can you fix this last thing? Thanks allot! "muddan madhu" wrote: Try this =IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&A2:A5&"!"&"C1:C10")),M1)) 1,"YES","NO") You need mention the sheet names in the Range A2:A5 Change the range according to the need. On Nov 14, 7:38 pm, puiuluipui wrote: Hi, it's working except for sheet 1. both workbooks have the same name for the sheets and i think it is a little bit confusing for excel. ex: workbook 1 and 2 sheets(same name for the sheets in both wbooks): LUNI MARTI MIERCURI JOI VINERI PLANNING SEARCH I don't want to search in "planning" and in "search". and i cand replacein your code, 1,2,3 with LUNI, MARTI....... What am i doing wrong? Thanks for your patience! Thanks! "muddan madhu" wrote: =IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&"sheet"&{1,2,3} &"!"&"C:C")),M1))1,"YES","NO") On Nov 14, 4:58 pm, puiuluipui wrote: Hi, it's working, but it's looking only in one sheet. Can your code search in 5 sheets? And i need to display "yes" if the code finds a duplicate nr, and "no" if doesn't find a duplicates. Can this be done? Thanks!!!! "muddan madhu" wrote: Try this =COUNTIF(INDIRECT("["&N1&"]"&"Sheet1!$C:$C"),C5) On Nov 14, 2:56 pm, puiuluipui wrote: Hi, i have a number in "C5" and i need to search for this number in another workbook. I need to search only in column C:C. But i need an indirect function because the name of book2 will change every week. I will put in "N1" the name of other workbook. Can this be done? Thanks! . . . |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect function countif
Hey, Text function not required, just an Apostrophe is enough to work
around. =IF(SUMPRODUCT(COUNTIF((INDIRECT("'["&N1&"]"&A2:A5&"'!"&"C:C")),M1)) 1,"YES","NO") On Nov 15, 12:45*pm, muddan madhu wrote: try this =IF(SUMPRODUCT(COUNTIF((INDIRECT("'["&TEXT(N1,"0") &"]"&A2:A5&"'!"&"C:C")),M1))1,"YES","NO") On Nov 15, 2:46*am, puiuluipui wrote: Hi, it's working if i use to workbook 2 a name like "BOOK2". I tried your last code and it's working perfect with "BOOK2", but my second workbook's name is "02.11-06.11"(the name of second workbook will change every week, but this is the format:dd.mm-dd.mm).The name of the second workbook is a period of 5 days, from monday to friday. With this name, the code is not working anymore. Can you fix this last thing? Thanks allot! "muddan madhu" wrote: Try this =IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&A2:A5&"!"&"C1:C10")),M1)) 1,"YES","NO") You need mention the sheet names in the Range A2:A5 Change the range according to the need. On Nov 14, 7:38 pm, puiuluipui wrote: Hi, it's working except for sheet 1. both workbooks have the same name for the sheets and i think it is a little bit confusing for excel. ex: workbook 1 and 2 sheets(same name for the sheets in both wbooks): LUNI MARTI MIERCURI JOI VINERI PLANNING SEARCH I don't want to search in "planning" and in "search". and i cand replacein your code, 1,2,3 with LUNI, MARTI....... What am i doing wrong? Thanks for your patience! Thanks! "muddan madhu" wrote: =IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&"sheet"&{1,2,3} &"!"&"C:C")),M1))1,"YES","NO") On Nov 14, 4:58 pm, puiuluipui wrote: Hi, it's working, but it's looking only in one sheet. Can your code search in 5 sheets? And i need to display "yes" if the code finds a duplicate nr, and "no" if doesn't find a duplicates. Can this be done? Thanks!!!! "muddan madhu" wrote: Try this =COUNTIF(INDIRECT("["&N1&"]"&"Sheet1!$C:$C"),C5) On Nov 14, 2:56 pm, puiuluipui wrote: Hi, i have a number in "C5" and i need to search for this number in another workbook. I need to search only in column C:C. But i need an indirect function because the name of book2 will change every week. I will put in "N1" the name of other workbook. Can this be done? Thanks! . . . |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect function countif
It's working! Thanks for your patience!
All codes are great, and i will vote for all. I needed a particular code, but maybe others will need one of previous code. Thanks allot for your help! "muddan madhu" wrote: Hey, Text function not required, just an Apostrophe is enough to work around. =IF(SUMPRODUCT(COUNTIF((INDIRECT("'["&N1&"]"&A2:A5&"'!"&"C:C")),M1)) 1,"YES","NO") On Nov 15, 12:45 pm, muddan madhu wrote: try this =IF(SUMPRODUCT(COUNTIF((INDIRECT("'["&TEXT(N1,"0") &"]"&A2:A5&"'!"&"C:C")),M1))1,"YES","NO") On Nov 15, 2:46 am, puiuluipui wrote: Hi, it's working if i use to workbook 2 a name like "BOOK2". I tried your last code and it's working perfect with "BOOK2", but my second workbook's name is "02.11-06.11"(the name of second workbook will change every week, but this is the format:dd.mm-dd.mm).The name of the second workbook is a period of 5 days, from monday to friday. With this name, the code is not working anymore. Can you fix this last thing? Thanks allot! "muddan madhu" wrote: Try this =IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&A2:A5&"!"&"C1:C10")),M1)) 1,"YES","NO") You need mention the sheet names in the Range A2:A5 Change the range according to the need. On Nov 14, 7:38 pm, puiuluipui wrote: Hi, it's working except for sheet 1. both workbooks have the same name for the sheets and i think it is a little bit confusing for excel. ex: workbook 1 and 2 sheets(same name for the sheets in both wbooks): LUNI MARTI MIERCURI JOI VINERI PLANNING SEARCH I don't want to search in "planning" and in "search". and i cand replacein your code, 1,2,3 with LUNI, MARTI....... What am i doing wrong? Thanks for your patience! Thanks! "muddan madhu" wrote: =IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&"sheet"&{1,2,3} &"!"&"C:C")),M1))1,"YES","NO") On Nov 14, 4:58 pm, puiuluipui wrote: Hi, it's working, but it's looking only in one sheet. Can your code search in 5 sheets? And i need to display "yes" if the code finds a duplicate nr, and "no" if doesn't find a duplicates. Can this be done? Thanks!!!! "muddan madhu" wrote: Try this =COUNTIF(INDIRECT("["&N1&"]"&"Sheet1!$C:$C"),C5) On Nov 14, 2:56 pm, puiuluipui wrote: Hi, i have a number in "C5" and i need to search for this number in another workbook. I need to search only in column C:C. But i need an indirect function because the name of book2 will change every week. I will put in "N1" the name of other workbook. Can this be done? Thanks! . . . . |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect function countif
The only diference is that i replace "1" with "0", because the one with
"1" is working if in second workbook are 2 duplicate nr. The one with "0" work if in the second workbook is only one nr that is the same with the nr in first workbook. Ex: "1" wbook 1 = 1234565 wbook 2 = 123456 and 123456 (the formula need 2 nr in workbook2 to work) Ex: "0" wbook 1 = 123456 wbook 2 = 123456 (the formula need 1 nr in workbook2 to work) Thanks again!!!! "muddan madhu" wrote: Hey, Text function not required, just an Apostrophe is enough to work around. =IF(SUMPRODUCT(COUNTIF((INDIRECT("'["&N1&"]"&A2:A5&"'!"&"C:C")),M1)) 1,"YES","NO") On Nov 15, 12:45 pm, muddan madhu wrote: try this =IF(SUMPRODUCT(COUNTIF((INDIRECT("'["&TEXT(N1,"0") &"]"&A2:A5&"'!"&"C:C")),M1))1,"YES","NO") On Nov 15, 2:46 am, puiuluipui wrote: Hi, it's working if i use to workbook 2 a name like "BOOK2". I tried your last code and it's working perfect with "BOOK2", but my second workbook's name is "02.11-06.11"(the name of second workbook will change every week, but this is the format:dd.mm-dd.mm).The name of the second workbook is a period of 5 days, from monday to friday. With this name, the code is not working anymore. Can you fix this last thing? Thanks allot! "muddan madhu" wrote: Try this =IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&A2:A5&"!"&"C1:C10")),M1)) 1,"YES","NO") You need mention the sheet names in the Range A2:A5 Change the range according to the need. On Nov 14, 7:38 pm, puiuluipui wrote: Hi, it's working except for sheet 1. both workbooks have the same name for the sheets and i think it is a little bit confusing for excel. ex: workbook 1 and 2 sheets(same name for the sheets in both wbooks): LUNI MARTI MIERCURI JOI VINERI PLANNING SEARCH I don't want to search in "planning" and in "search". and i cand replacein your code, 1,2,3 with LUNI, MARTI....... What am i doing wrong? Thanks for your patience! Thanks! "muddan madhu" wrote: =IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&"sheet"&{1,2,3} &"!"&"C:C")),M1))1,"YES","NO") On Nov 14, 4:58 pm, puiuluipui wrote: Hi, it's working, but it's looking only in one sheet. Can your code search in 5 sheets? And i need to display "yes" if the code finds a duplicate nr, and "no" if doesn't find a duplicates. Can this be done? Thanks!!!! "muddan madhu" wrote: Try this =COUNTIF(INDIRECT("["&N1&"]"&"Sheet1!$C:$C"),C5) On Nov 14, 2:56 pm, puiuluipui wrote: Hi, i have a number in "C5" and i need to search for this number in another workbook. I need to search only in column C:C. But i need an indirect function because the name of book2 will change every week. I will put in "N1" the name of other workbook. Can this be done? Thanks! . . . . |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indirect function countif
Thank you for the feedback.
On Nov 15, 2:54*pm, puiuluipui wrote: The only diference is that i replace "1" with "0", because the one with * "1" is working if in second workbook are 2 duplicate nr. The one with "0" work if in the second workbook is only one nr that is the same with the nr in first workbook. Ex: "1" wbook 1 = 1234565 wbook 2 = 123456 *and *123456 (the formula need 2 nr in workbook2 to work) Ex: "0" wbook 1 = 123456 wbook 2 = 123456 (the formula need 1 nr in workbook2 to work) Thanks again!!!! "muddan madhu" wrote: Hey, Text function not required, just an Apostrophe is enough to work around. =IF(SUMPRODUCT(COUNTIF((INDIRECT("'["&N1&"]"&A2:A5&"'!"&"C:C")),M1)) 1,"YES","NO") On Nov 15, 12:45 pm, muddan madhu wrote: try this =IF(SUMPRODUCT(COUNTIF((INDIRECT("'["&TEXT(N1,"0") &"]"&A2:A5&"'!"&"C:C")),M1))1,"YES","NO") On Nov 15, 2:46 am, puiuluipui wrote: Hi, it's working if i use to workbook 2 a name like "BOOK2". I tried your last code and it's working perfect with "BOOK2", but my second workbook's name is "02.11-06.11"(the name of second workbook will change every week, but this is the format:dd.mm-dd.mm).The name of the second workbook is a period of 5 days, from monday to friday. With this name, the code is not working anymore. Can you fix this last thing? Thanks allot! "muddan madhu" wrote: Try this =IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&A2:A5&"!"&"C1:C10")),M1)) 1,"YES","NO") You need mention the sheet names in the Range A2:A5 Change the range according to the need. On Nov 14, 7:38 pm, puiuluipui wrote: Hi, it's working except for sheet 1. both workbooks have the same name for the sheets and i think it is a little bit confusing for excel. ex: workbook 1 and 2 sheets(same name for the sheets in both wbooks): LUNI MARTI MIERCURI JOI VINERI PLANNING SEARCH I don't want to search in "planning" and in "search". and i cand replacein your code, 1,2,3 with LUNI, MARTI....... What am i doing wrong? Thanks for your patience! Thanks! "muddan madhu" wrote: =IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&"sheet"&{1,2,3} &"!"&"C:C")),M1))1,"YES","NO") On Nov 14, 4:58 pm, puiuluipui wrote: Hi, it's working, but it's looking only in one sheet. Can your code search in 5 sheets? And i need to display "yes" if the code finds a duplicate nr, and "no" if doesn't find a duplicates. Can this be done? Thanks!!!! "muddan madhu" wrote: Try this =COUNTIF(INDIRECT("["&N1&"]"&"Sheet1!$C:$C"),C5) On Nov 14, 2:56 pm, puiuluipui wrote: Hi, i have a number in "C5" and i need to search for this number in another workbook. I need to search only in column C:C. But i need an indirect function because the name of book2 will change every week. I will put in "N1" the name of other workbook. Can this be done? Thanks! . . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using INDIRECT Function and INDEX Function | Excel Discussion (Misc queries) | |||
| how to countif when using indirect cell | Excel Discussion (Misc queries) | |||
| Countif indirect question | Excel Discussion (Misc queries) | |||
| INDIRECT function inside AND function | Excel Worksheet Functions | |||
| INDIRECT WITH COUNTIF | Excel Worksheet Functions | |||
Linear Mode
