ERoberts,

Welcome to the forum!
Why not just reference the cell containing the "VALUE NAME" in the formula instead of typing it out? So instead of:
=COUNTIFS('LOAD LOG'!A:A,"76 RANCH MCM A 1H",'LOAD LOG'!F:F, "")

Use this:
=COUNTIFS('LOAD LOG'!A:A,C1,'LOAD LOG'!F:F, "")