I've created a workbook that relies heavily on the INDIRECT function. I'm now reading that INDIRECT is a "volatile" function and too many of them creates slowdowns. My workbook must have well over 10,000 of these INDIRECT functions. But I don't know another way to accomplish what I need. Maybe someone can help?
----------
Here's a summary of what my workbook does:
All the data is inputted into a sheet called "Sales". Each row represents a single sale, with five columns of information. One of those columns is "customer ID". Each customer ID will show up multiple times, so for 1000 sales there might be 50 unique customers.
I then need to summarize the data for each customer into a table. So in this example, I need 50 tables. All 50 tables need to exist and be populated at all times, because the information from all the tables will populate a sheet called "Total Summary".
----------
The way I've been accomplishing this by creating a sheet for each customer (50 in total). Each customer sheet uses an array function to list all the row locations where there is a match on "customer ID". This output a column of ~20 numbers between 1-1000. On the five columns to the right, I use INDIRECT to combine that number with the appropriate column from the "Sales" sheet. This gives me detailed list of all the sales from that specific customer. I can then use this list to perform calculations which fills out the table for that customer. But it has taken hundreds of INDIRECT functions to accomplish. Multiply that by 50 sheets...
I'm also using INDIRECT on my "Total Summary" page to display the simplified totals for each customer. This is because the customer sheets are simply named 1 through 50, so this way I can just drag from 1-50 in column A on "Total Summary" and all the INDIRECT functions to the right will reference the number in column A to find the appropriate sheet.
In theory this works great, but my sheet seems to be much too big to be using so many of these volatile functions...
Bookmarks