Indirect

[Cooki]

Hi All im getting #Ref for this formula, but i cant see the issue with it

=IF(COUNTIF(INDIRECT("'["&$F$1&"]"&$E$1&"'!"),A2),"Match","No Match")

F1 is the workbook name
E1 is the tab name

When im evaluating it, its saying "'["&$F$1&"]"&$E$1&"'!" is the issue

Can someone spot where ive gone wrong?

Thanks

 

[JLGWhiz]

Let's write out what your formula would look like if F1 = "Book1.xlsx" and E1 - "Data"

=IF(COUNTIF([Book1.xlsx]Data!, A2), "Match", "No Match")

Now let's look at the syntax for CountIF

COUNTIF(RangeArray, CiteriaRange)

Looks like you are missing the RangeArray between the apostrophe '!' and comma ','. You have the workbook and sheet but no range/

 

[offthelip]

you haven't selecte a range: this works:
=IF(COUNTIF(INDIRECT("'["&$F$1&"]"&$E$1&"'!A1:a10"),A2),"Match","No Match")

 

[Cooki]

Dam..... so close, just overlooked the range.

Much appreciated both, works fine now

 

[JLGWhiz]

Dam..... so close, just overlooked the range.

Much appreciated both, works fine now

Happy to assist,
Regards, JLG