Forum Discussion
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 trying to use the COUNTIF function however, it does not work on external references that are larger than one cell. For example, the external reference is " {='[Copy of August Monthly_PEX(974).xlsx]Friday, August 04, 2017 (216)'!$C$15:$C$20} ". This reference is typed, but the actual name of the employee is in the cell. However, the COUNTIF function does not seem to realize the actual name is there and instead of counting up, the counter remains at zero.
The puzzling part of this whole thing is that in other parts of the calendar where only a single cell is referenced even if the referenced cell is a Merged Cell, ie $A$1:$A$2, the proper counters count up one using the COUNTIF function.
I have attatched the calendar in question for reference but not the referenced workbook.
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.
6 Replies
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.
- That's not the space, another ascii symbol
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.
