carteBlanche
New Member
- Joined
- Oct 7, 2014
- Messages
- 19
Hi everybody,
I have a workbook with a data set and a relatively large report. The raw data are pulled from some ERP software and stored in a table in a worksheet. Then, I use a combination of SUMIFS and INDIRECT functions to organise the data in a report in another worksheet.
Basically, INDIRECT is used within SUMIFS (as its first argument) to refer to the desired column of the raw data table. The columns of the table with the raw data as well as their order change more often than not each time I output the data from the ERP. I like the flexibility offered by INDIRECT for that reason.
I've used this method with smaller reports but I knew I'd have to find a non-volatile alternative to INDIRECT for larger reports. So here I am, my workbook is painfully slow and I am stuck after hours of search on the internet.
I've read about alternatives using CHOOSE/MATCH or named ranges but never managed to make something of it without using INDIRECT at some point.
I've used INDIRECT for way too long and probably can't open my mind to something different or another approach to this, so any pointers would be much appreciated
Please note that:
- I do not use pivot tables on purpose because of the data structure in the ERP software;
- I did not explore VBA-oriented options on purpose as well because the workbook is used by many people;
- I'd like to avoid having to set calculations to manual;
- I could re-organise the structure of the output data in the ERP (and have a single column with amounts, thus eliminating the need to refer to the desired column) but that would result in a table with way too many rows.
Thanks,
Tom.
I have a workbook with a data set and a relatively large report. The raw data are pulled from some ERP software and stored in a table in a worksheet. Then, I use a combination of SUMIFS and INDIRECT functions to organise the data in a report in another worksheet.
Basically, INDIRECT is used within SUMIFS (as its first argument) to refer to the desired column of the raw data table. The columns of the table with the raw data as well as their order change more often than not each time I output the data from the ERP. I like the flexibility offered by INDIRECT for that reason.
I've used this method with smaller reports but I knew I'd have to find a non-volatile alternative to INDIRECT for larger reports. So here I am, my workbook is painfully slow and I am stuck after hours of search on the internet.
I've read about alternatives using CHOOSE/MATCH or named ranges but never managed to make something of it without using INDIRECT at some point.
I've used INDIRECT for way too long and probably can't open my mind to something different or another approach to this, so any pointers would be much appreciated

Please note that:
- I do not use pivot tables on purpose because of the data structure in the ERP software;
- I did not explore VBA-oriented options on purpose as well because the workbook is used by many people;
- I'd like to avoid having to set calculations to manual;
- I could re-organise the structure of the output data in the ERP (and have a single column with amounts, thus eliminating the need to refer to the desired column) but that would result in a table with way too many rows.
Thanks,
Tom.