Locking Size of Pivot Table

[TechieJr]

Hi All,

I am using the pivot table function of Excel to do some grouping and totalling of information coming from an Access database. I need some help in figuring out how to lock the size of the pivot table.

Here's what's happening. In the database, a query selects values of loans coming from 7 different sources and the 8 different types of clients they can go to. For my report, I need to group and total the 7 sources into 4 and the 8 types of clients into 3. The pivot table is working and does this just great.

Next, I need to move these values back into Access. I have a second worksheet in the workbook which has absolute references to cells in the pivot table on the first worksheet. Thus, the second worksheet takes the table format of the pivot table and puts it all in a single row to be imported back into Access as a new table.

The problem. My problem comes when there is no information for one of the loan sources or one of the client types. The pivot table condenses and the absolute references on the second sheet are pointing to the wrong cell. I want to lock the size of the pivot table so that the cells on the second worksheet are always pointing to the correct cells on the first. I would also like to fill the empty cells with 0's.

I would appreciate hearing if someone has a way of doing this or ideas on different ways of accomplishing this.

Thanks a bunch! (And sorry for a lengthy post.)
TechieJr.

 

[SkipVought]

Hi TechieJr, this is TechieSr! ;-)

I've had situations like this. It's naturally, because there is no data of a particular value that you are looking for in the table.

What I have done is load the table with a set of dummy records that have no significant data other than the elements I want present in my table.

Klugie but it works! ;-)

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TechieJr]

Hi Skip,

Having some dummy records to fill in the blanks is something I have thought a little about, but it would create issues with other aspects of the database. I have not had the opportunity to give it a full think-through to see if the other issues could be avoided somehow.

You could clarify something for me. Which table have you filled with dummy values? The table in the Access database or the pivot table in Excel? I haven't found a way to fill a pivot table directly with dummy values.

I'll have to give the dummy record in Access some further consideration.

Thanks for the input.
TechieJr.

 

[SkipVought]

Your SOURCE data must have ALL the values that you need to display in either the COLUMN or ROW area. So they would have to be in the Access table as entries.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884