I have 15 unrelated attributes of the transactional data, all of which can be used in the Mapping table (but not all are required). For example for Mapping 1, the user might need to select only City, whereas on Mapping 2, they might select five of the 15 attributes to map the sum of Sales. Each Mapping has different attributes of the 15. If an attribute is not used for a given Mapping, then the formula should sum all values for that attribute. The existing tool in Excel achieves this using Data Validation on the attributes in the Mapping table. If the user enters *, SUMIFs works correctly to pull in all values for that attribute. The transactional table is already list formatted, but if I have to create a dimensionalized mapping table it will be impossibly huge, and defeat the purpose of being able to map in the first place (it becomes not a mapping table, but a summing table).
... View more