Indirect function countif

[puiuluipui]

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!

 

[muddan madhu]

Try this

=COUNTIF(INDIRECT("["&N1&"]"&"Sheet1!$C:$C"),C5)

 

[puiuluipui]

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!!!!

Try this

=COUNTIF(INDIRECT("["&N1&"]"&"Sheet1!$C:$C"),C5)

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!

.

 

[muddan madhu]

=IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&"sheet"&{1,2,3}
&"!"&"C:C")),M1))>1,"YES","NO")

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!!!!

Try this

=COUNTIF(INDIRECT("["&N1&"]"&"Sheet1!$C:$C"),C5)

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 changeevery
week. I will put in "N1" the name of other workbook.
Can this be done?
Thanks!

.

 

[puiuluipui]

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!

=IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&"sheet"&{1,2,3}
&"!"&"C:C")),M1))>1,"YES","NO")

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!!!!

Try this

=COUNTIF(INDIRECT("["&N1&"]"&"Sheet1!$C:$C"),C5)

On Nov 14, 2:56 pm, puiuluipui <[email protected]>
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!

.

.

 

[muddan madhu]

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.

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!

=IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&"sheet"&{1,2,3}
&"!"&"C:C")),M1))>1,"YES","NO")

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!!!!
:
Try this
=COUNTIF(INDIRECT("["&N1&"]"&"Sheet1!$C:$C"),C5)
On Nov 14, 2:56 pm, puiuluipui <[email protected]>
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!
.

.

 

[puiuluipui]

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!

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.

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!

=IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&"sheet"&{1,2,3}
&"!"&"C:C")),M1))>1,"YES","NO")

On Nov 14, 4:58 pm, puiuluipui <[email protected]>
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!!!!

:
Try this

=COUNTIF(INDIRECT("["&N1&"]"&"Sheet1!$C:$C"),C5)

On Nov 14, 2:56 pm, puiuluipui <[email protected]>
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!

.

.

 

[muddan madhu]

try this

=IF(SUMPRODUCT(COUNTIF((INDIRECT("'["&TEXT(N1,"0")
&"]"&A2:A5&"'!"&"C:C")),M1))>1,"YES","NO")

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!

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.

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!
:
=IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&"sheet"&{1,2,3}
&"!"&"C:C")),M1))>1,"YES","NO")
On Nov 14, 4:58 pm, puiuluipui <[email protected]>
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!!!!
:
Try this
=COUNTIF(INDIRECT("["&N1&"]"&"Sheet1!$C:$C"),C5)
On Nov 14, 2:56 pm, puiuluipui <[email protected]>
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!
.
.

.

 

[muddan madhu]

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")

try this

=IF(SUMPRODUCT(COUNTIF((INDIRECT("'["&TEXT(N1,"0")
&"]"&A2:A5&"'!"&"C:C")),M1))>1,"YES","NO")

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!

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 <[email protected]>
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!
:
=IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&"sheet"&{1,2,3}
&"!"&"C:C")),M1))>1,"YES","NO")
On Nov 14, 4:58 pm, puiuluipui <[email protected]>
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!!!!
:
Try this
=COUNTIF(INDIRECT("["&N1&"]"&"Sheet1!$C:$C"),C5)
On Nov 14, 2:56 pm, puiuluipui <[email protected]>
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!
.
.
.

 

[puiuluipui]

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!

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")

try this

=IF(SUMPRODUCT(COUNTIF((INDIRECT("'["&TEXT(N1,"0")
&"]"&A2:A5&"'!"&"C:C")),M1))>1,"YES","NO")

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!

:
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 <[email protected]>
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!

=IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&"sheet"&{1,2,3}
&"!"&"C:C")),M1))>1,"YES","NO")

On Nov 14, 4:58 pm, puiuluipui <[email protected]>
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!!!!

:
Try this

=COUNTIF(INDIRECT("["&N1&"]"&"Sheet1!$C:$C"),C5)

On Nov 14, 2:56 pm, puiuluipui <[email protected]>
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!


.

.

 

[puiuluipui]

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!!!!

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")

try this

=IF(SUMPRODUCT(COUNTIF((INDIRECT("'["&TEXT(N1,"0")
&"]"&A2:A5&"'!"&"C:C")),M1))>1,"YES","NO")

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!

:
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 <[email protected]>
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!

=IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&"sheet"&{1,2,3}
&"!"&"C:C")),M1))>1,"YES","NO")

On Nov 14, 4:58 pm, puiuluipui <[email protected]>
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!!!!

:
Try this

=COUNTIF(INDIRECT("["&N1&"]"&"Sheet1!$C:$C"),C5)

On Nov 14, 2:56 pm, puiuluipui <[email protected]>
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!


.

.

 

[muddan madhu]

Thank you for the feedback.

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!!!!

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")

try this
=IF(SUMPRODUCT(COUNTIF((INDIRECT("'["&TEXT(N1,"0")
&"]"&A2:A5&"'!"&"C:C")),M1))>1,"YES","NO")
On Nov 15, 2:46 am, puiuluipui <[email protected]>
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 everyweek, 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!
:
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 <[email protected]>
wrote:
Hi, it's working except for sheet 1.
both workbooks have the same name for the sheets and i think itis 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!
:
=IF(SUMPRODUCT(COUNTIF((INDIRECT("["&N1&"]"&"sheet"&{1,2,3}
&"!"&"C:C")),M1))>1,"YES","NO")
On Nov 14, 4:58 pm, puiuluipui <[email protected]>
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!!!!
:
Try this
=COUNTIF(INDIRECT("["&N1&"]"&"Sheet1!$C:$C"),C5)
On Nov 14, 2:56 pm, puiuluipui <[email protected]>
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!
.
.
.

.