Hi there! I'm new, but I'll try my best at explaining what I'm looking for.
I use a spreadsheet to populate 40+ 25-page contracts via Word mail merge. The contracts require a table with a list of items and their cost. However, each contract has different combinations of a set of 12 or so items with different costs for each item.
I have the Excel document set up with two sheets; one (Sheet1) is the more user-friendly version in which the basic information can be entered, and the other sheet (Sheet2) references the data from Sheet1 but with all the necessary formatting for the merge. I currently have all of the cost data in Sheet 1 by columns, essentially something like this:
Item1 Item2 Item3
ContractA ACost1 ACost3
ContractB BCost1 BCost2
ContractC CCost2 CCost3
ContractD DCost1 DCost3
I would like to be able to reference this data in Sheet2, skipping over any blank fields like so:
ContractA Item1 ACost1 Item3 ACost3
ContractB Item1 BCost1 Item2 BCost2
ContractC Item2 CCost2 Item3 CCost3
ContractD Item1 DCost1 Item3 DCost3
Does anyone know how I can do this?
EDIT: An example workbook is attached.