First of all,
You'd better consider data pivot.
Anyways, It's had to explain the solution without your data, could you mock up data and create packaged workbook and attach it?
Thank you for your reply. I will start looking into that. In the meantime, here's a link to a twbx. Had to share with Google drive because it was too large to attach.