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!

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!

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!

.

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!

.

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!

.

.

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!

.

.

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!

.

.

.

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!

.

.

.

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!

.

.

.

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!

.

.

.

.

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!

.

.

.

.

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!

.

.

.

.