Forum Discussion
Solved
Excel External Reference Formula
I am trying to count the number of times a name appears on a calendar, however; the areas where the names are on the calendar are populated via a reference from an external excel workbook. I am ...
Okay, now i'm not on mobile and checked the file. There is Line Feed at the end of each string, CHAR(10) other words.
Better in the source replace CHAR(10) to nothing, alternatively add CHAR(10) in calculations like
=COUNTIF(A6:BD15,BE13&CHAR(10))+COUNTIF(A24:BD32,BE13&CHAR(10))+COUNTIF(A41:BD49,BE13&CHAR(10))+COUNTIF(A58:BD66,BE13&CHAR(10))+COUNTIF(A75:BD83,BE13&CHAR(10))
As for alternativies which functions to use that doesn't matter - use any one with which you are more comfortable.
Hi Eric,
I guess the issue is not in formula but in your data where you have some extra invisible symbol. For example, in your sample file copy cell S13 (with EarlyS) and paste as value in any empty cell. Tale LEN() of it. It gives 7, not 6.