Based on a suggestion in another forum, I used the concept of "helper" cells and then hid them. Basically I used ADDRESS and OFFSET formulas together to set the values of the hidden fields based on the cells that actually contained the value in the merge fields. Now I can modify the values of the Merge fields on demand, which will change the values of the hidden fields enabling the sumproduct formulas to work as needed. Great simple solution that I didn't think about.
|