Forum Discussion
Excel External Reference Formula
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.
- 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.
Thank you so much for your assistance! I was aware of that extra character and deleted it on a temporary copy on a desktop, but my surface doesn't have a num pad so I couldn't delete it on my tablet. Turns out, using the Find and Replace function works as well. Alt+010 on a num pad is the code for a line break...full stop... what ever you want to call it! But had it not been for your keen eyes I would have spent many extra hours drying my eyes by staring at a computer screen! Thank you again!
