countif problem

[ajm]

i am getting an unexpected result using an "if(countif" combination. i have the same formula across the columns in my sheet, but for some reason i can't see, its not working in the last column.

Column B : IF(COUNTIF(BAS!ABN,A4)>0,"Y","")
Column C: IF(COUNTIF(ITR!ABN,A4)>0,"Y","")
Column D: IF(COUNTIF(PAYG!ABN,A4)>0,"Y","")
Column E: IF(COUNTIF(FBT!ABN,A4)>0,"Y","")

and so on to Column K: IF(COUNTIF(ESS!ABN,A4) >0,"Y","")

Column A contains a 10 digit numeral which, if present, will appear in Column A of each sheet the Title of the column on each tab is ABN. I have manually added it to the ESS sheet so I know it is there. Vlookup also returns a result from this tab. the 10 digit number is a real number in all locations.

I don't get it? Any suggestions?

what could i possibly

 

[kweaver]

Aren't you missing a row number here, for example? IF(COUNTIF(BAS!ABNrow,A4)>0,"Y","") and/or is this a reference to column ABN?

 

[ajm]

Aren't you missing a row number here, for example? IF(COUNTIF(BAS!ABNrow,A4)>0,"Y","") and/or is this a reference to column ABN?

hey kweaver, its a reference to a column on each sheet headed "ABN". I haven't seen a formula written like this before but it was working, as you can see in the columns B-J. very odd.

 

[Marcelo Branco]

My guess....
It appears that several named ranges have been created, one for each worksheet, all with the same name, ABN, and with Scope = Worksheet, but not for ESS.
Check in Names Manager .

M.