Linked Formulas Not Consistently Working

[BEEROG]

I actually have two separate, related questions, working in Excel 2016, 64bit.
I have numerous sheets that all reference table data on my main worksheet, Card1Totals[QTY09 RCVD]. The [QTY09 RCVD] is pulled with a SUMIF formula from an external Workbook. The first issue is that, when opening the workbook in question, you MUST open the source for the links to populate. The second issue is that the internal links only populate when you go to the worksheet and recalculate on that worksheet. (CTR+SHFT+ALT+F9 nor any other combination overall works.)

I have insured that the automatic calculation is on. (Yes, turned it off, restarted everything and turned it back on to no avail.) I have copied my workbook to a newly created workbook to insure it's not an upgrade compatibility thing. I have also disabled the fuzzy lookup add on and restarted. What am I missing???

The finicky formulas that are consistently causing the internal issues are:
=SUMIF(Card1Totals[INV CARD LABEL],MID(CELL("filename"),FIND("]",CELL("filename"))+1,255),Card1Totals[QTY09 RCVD])

Any help is greatly appreciated!!!

[Pete_UK]

Try this amendment (changes in red):

=SUMIF(Card1Totals[INV CARD LABEL],MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),Card1Totals[QTY09 RCVD])

Hope this helps.

Pete

[BEEROG]

I figured this one out. The original formula looks for the name of the ACTIVE worksheet. If you have the formula in a workbook that is open and set to automatically update / calculate, it updates the value to whatever worksheet name you are actively in at the time. That throws ALL of your linked formulas off! I am by no means a guru, so I did a copy and pasted values only right over the top of my formula with no other books open. It was a "bit" cumbersome, however; my workbook is a template for someone to keep track of their inventory with, so it's a one time configuration.



Perhaps there's a better formula out there that will lock in on it's own worksheet name...? Until then, thank you all!