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.
I agree with Sergei Baklan . The list of names in some of the sheets of your source workbook seem to have an additional space suffixed to some of the names, example: "EarlyS" is named as "EarlyS ". So the correction should be in the source workbook of the external links.