The number of rows for every category is undetermined, so making it using worksheet formulas assumes, you predesign maximal possible number of rows into every category. Doesn't it sound as horror story for you?
Your summary setup is too complex for Pivot Table, so an ODBC Query may be the best solution.
In attached file, you have to edit query's Data Connection string and Command text (until then you get an error whenever you open the workbook) so the right workbook in right location is connected (Data > Connections > Properties. Activate tab Definition and there are 2 fields where you have to edit file path - and file name in case you change it too).
|