Displaying Specific Values in Pivot Table

This is a matter of “I get by with the help of my friends.” In this case, my friends are pivot tables. My philosophy is to get Excel to do as much of the work as possible and use VB to tie the pieces together.

Future State 1 is a simple pivot table but instead of “X” you get a count of how many catalogs fit the Country / Peril combination. This is displayed on sheet FS1.

I use this pivot table to get me the row and column headers for Future State 2 (FS2).

I go down the countries and across the perils and use a helper pivot table in Columns N:O on the data sheet. By setting the country and peril filters, I get a list of catalogs that match. I use this list to compute the string.

The value in Cell Q1 on the data sheet is a check to make sure I have data for a given Country / Peril combination.

I use the value in cell Q1 to keep me out of trouble while trying to loop through the list. There are three "headers" in the Q Column. So if there are two countries that meet the conditions of the filters, Q1 = 5. It's simply a count of all the non-blank cells in Column Q.

So if Q1 > 3 then I have at least one item on the list and my code works. However if Q1 = 3, it means that there are no items on the list and if I try to loop through the list, the code may give me an error message.

When you work with pivot tables, you have to do some extra checking. For example if you are trying to set a filter to a value, but that value doesn't exist in the pivot table, Excel will offer to change the current selection to that value and really screw things up. So, I like to check that there is at least on occurrence of the item before using it.

As for FS2. I made the pivot table on FS1 mainly as a demonstration that you could get the first step of your request done with a pivot table alone without having to use VB code.

I then used VB to copy the row and column headers to FS2.

I could have eliminated this two-step by making the pivot table like I did in FS1 but leave out the values.

If you want, I could give you a version without FS1. It was included only for proving a point. It's a minor code change.

While I am explaining the list I should mention that it is a named dynamic range. Here is some information on named dynamic ranges: http://www.utteraccess.com/wiki/inde...Dynamic_Ranges.

If you have any other questions on the code or dynamic ranges, feel free to ask.

This this is a slightly "cleaner" version. It still has the FS1 page, but it is used to get the row and column headers (which it does with pivot tables) and for the scratch calculations for the catalog strings. I removed the scratch calculations from the data sheet to keep it "clean." You can hide the FS1 page.

In this version of the workbook - and even in the former version of the workbook, I accomplished the trick of getting the catalog values with the help of a pivot table. In the former version, this pivot table was on the data sheet. In the new version, it's on the FS1 sheet.

I'm using the FS1 sheet as a "scratch" sheet to do calculations. The pivot table in Column A simply gets me a unique list of country names. I copy and paste this list to FS2. The pivot table in column C gets a list of the perils. I copy and paste this with a transpose to FS2.

The pivot table in columns E:F does the work of finding the catalogs. First I go down the list of country names and I set the filter in cell F1 to the country name. While this name is set, I go down the list of perils and set the filter in cell F2. This gives me a list of all catalogs that are associated with the Coutnry / Peril pair.

The code makes a string out of that. For example, for Country = Anguilla and Peril = TC, there are two catalogs: 10K and 50K.

The number in cell H1 is used as a check. It's simply a COUNTA of column E. If it's 3 then it means the only thing in column E are the headers; no data is retuened, so I don't try to read the list and process it. If it's > 3 it means some data was returned and I should process the list.