Too many INDIRECT formulas? (slow workbook)

[Yoshi64]

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...

[6StringJazzer]

I am not seeing anything in your description that really calls for INDIRECT. In fact, given the way you have described your data I think this can be done very simply with a pivot table. Whether you need one pivot table or 50 is a matter of how you need to present the reports.

The only way to tell for sure is to see your file. Can you post your file with any personal information removed? I would only need to see Sales and one sample report sheet, not all 50.

[Yoshi64]

I think you're probably right. I spent the last hour messing with pivot tables for the first time, and they appear like they might do the trick. One question in the meantime (I'll post a sample file in a moment)... Can I set pivot tables to auto-refresh? As of now it seems when I input a new sale, the pivot table doesn't recognize and list that sale until I say "refresh table"

[6StringJazzer]

I do not know of a built-in way to autorefresh pivot tables but I have done it with macros that detect when the source data changes.

[Yoshi64]

Attached is a sample file that gives an example of what i'm trying to do.

As you can see in column G on the customer tab, I want to know the average cost of each product. So if customer 1 buys 4 chairs at $100, then comes back and buys 10 more chairs at $150, I want to know the average costs of all 14 chairs. What I did works but maybe there's a way to build it into the table.

The not refreshing thing might be an issue. Reason being is that this workbook will need to be dynamic, meaning as sales are inputted on the data sheet, the tables need to reflect that in real time. The way I envision the sheet working is there are 2 windows open, one with the sales data and another to look at the tables. So as I type data in on the left, window, I want it to reflect on the tables

[newdoverman]

Here is your file with an added Calculated field that can replace the calculation that you have to the right of the Pivot Table.

The updating of the table can be done with a macro that goes to worksheet 1 clicks in the PIVOT Table then clicks on the Options tab, then clicks on REFRESH.

Hope this helps.

[Yoshi64]

Thanks newdoverman. I can't figure out how you added Cost Ave to the table (I'm new to these obviously). Where do I add new PivotTable Fields beyond what appears there automatically from my data headers?

[newdoverman]

You're welcome. An explanation of how this was done follows. You can add more Calculated Fields if you need them.

Click in the Pivot Table to activate the Pivot Table tools. Click on the Options tab, you should see a grouping in the ribbon called Calculations. In the Calculations group there is an item called Fields, Items, & Sets. If you click on that, there is an item called Calculated Field. Click on that and a dialogue box will open with a field at the top called Name and it will have a default name. Ignore that and click the down arrow at the right side of the field and you will see Ave Cost the name of the inserted field. Click on that and the field under Name called Formula will show the calculation that created the Ave Cost field in the report.

[Yoshi64]

Perfect thanks. I figured out how to create my own Calculation fields as well in that section.

I have a new issue (I don't know if I should start a new thread) that came up as I tried to advance further along in my workbook creation. There is a second list of data inputs which need to be incorporated with the Sales, to come up with the full picture. These are in the "Transfer of Sales" tab. Basically after we make sales to customers, some of the sales are transferred to other stores to fill the order for us. Therefore the quantities and costs from these "Transfer of Sales" need to be subtracted from the "Sales" to give the final quantity and price.

The closest solution I came up with was creating new pivot tables and filtering each by store. The problem I'm running into is I can't get the products to line up. This is because we only transfer out some of the sales. So while we might sell products P1-P4 to a customer, maybe there is only a transfer for product 3. I need excel to somehow recognize this and align the Transfer P3 data with Sale P3 (instead of P1).

The reverse isn't an issue, as we will never transfer out a product which there wasn't a sale for.

You can see how I'd want it too look ideally. I had to do it manually though and it needs to be automated.

[Yoshi64]

Also I'm guessing there's a much smarter way to do this, by somehow combining the Transfers and Sales information into one pivot tables. I tried combining the "Transfer" and "Sales" tabs into one table of information and creating a single pivot table, but I couldn't figure out how to unclutter it.

[Yoshi64]

Actually maybe this shouldn't be too hard. All I really need to do is replace

=GETPIVOTDATA("Sum of Quantity",$G$16,"Partner","Co. A","Customer",1,"Product","chair")

with

=GETPIVOTDATA("Sum of Quantity",$G$16,"Partner","Co. A","Customer",1,"Product","[output the text in C6]")

[Yoshi64]

Nvm I think I got it. Just had to remove the quotes around C6 and it does exactly what I wanted.

[newdoverman]

Thanks for the feedback.

It is always great to have a solution