Pivot Tables: Pivot table layout

Re: Pivot Tables: Pivot table layout

The problem with your sample file A3 was the way you defined the dynamiv range. Your data actually start in cell A5, so the DNR needs to begin there. The number of rows in the dataarea must be calculated not counting the entries in column A above A5. So, the DNR should be dfined as (there is more than one way to to get the same result, but start with this one.)
=OFFSET(Sheet1!$A$5,0,0,COUNTA(Sheet1!$A:$A)-3,11)
Once you get this definition correct, the other symptoms you described should vanish.

Re: Pivot Tables: Pivot table layout

Thanks.
I'm not clear about something though.
If my database starts at A5, then at the end of the DNR should it not be -4 instead of -3, since there are 4 rows above A5 ?

Also, when I set this DNR and tried creating a pivot table, the top left of the range was showing as A1.
In addition, the end part of the range was $A$3376 - correct for the amount of records currently present, but not correct for the column reference. Why would it start at A1 and why would it not find the last column in the range ?

Also, when the pivot table has been made, the field list box only has one field in it - the first one, col A, called Date.
I then ran the wizard again and manually edited the range during step 2 of 3 to start at $A$5, and then I spotted that the end part of the range was also referring to column A (incorrect reference as per the wizard was $A$1:$A$3376).
I then manually edited the range to show the correct references of $A$5:$K$3376.

However, after running with this new DNR, the stubborn problem of new data not being picked up by the pivot table remains.

If I insert rows within the existing Dynamic Name Range and fill the rows with data, the pivot table picks up the new data.
The pivot table also updates correctly when I delete rows. It seems therefore that there is problem with entering data outside the defined DNR after setting up the DNR - surely this can't be true, because it would be pointless having a DNR ?

Re: Pivot Tables: Pivot table layout

1) the -3 was because there were 3 entries in the range A1:A4. The DNR posted earlier gets its number of rows by counting the entries in column A, so the number of entries above A5 need to be subtracted. A good DNR would actually count them rather than having the -3 hard coded, but let's leave that until we get your pivot table working.

2) the number of columns was specified by the 11 in the DNR definition. So starting with A, the 11 says the range should go to K.

3) From your comments I suspect you are not using the defined name as the source of your pivot table. Look at the pivot table source and see. It should not be showing an actual address, but should instead be the name assigned to your dynamic range, in this case DataArea.

Re: Pivot Tables: Pivot table layout

The range that appears in Wizard's step 2 of 3 has NEVER been the range name, it has always shown up as the actual range of cells and I noticed how your sample files featured the name.

The range name also doesn't appear in the Name dropdown Box, top left of the spreadhseet, above column A.

Do you know why the range name does not appear when in the wizard and how can I get it to do so ?

Re: Pivot Tables: Pivot table layout

I tried typing the DNR name in the range box instead of letting the wizard use the actual range address. When new data was added to the pivot table and refreshed, hey presto, the new data was picked up!
I feel quite relieved and wanna punch the air and say hooray : D because of how long this problem has taken to crack, but I am somewhat deflated :confused: because I don't understand why the wizard would always ONLY pick up the range of cells with data in them during step 2 of 3 (thereby making any future data "invisble" to it) instead of accepting the name of the DNR ?

Before I start the Pivot Table wizard process, the cursor was resting in a cell of the my data area - should the Wizard not have picked up the DNR name instead of the range of cells with data in them ?

After one successful pivot table creation, I saved the file, closed it down and opened it up again. I then went to create a new pivot table but the DNR name was not showing up as the range in step 2 of 3 - nor was there anything else in the range - it was just empty. Should the DNR name be retained ?

Re: Pivot Tables: Pivot table layout

You are giving Excel too much credit. When you start the pivot table wizard and it gets to step 2, the wizard doesn't know anything about what you have in mind for it to do. It has a simple rule: Look as the selected cell and its current region (the filled in cells around it) and place that range as a suggested souirce for the pivot table. You have to look at that suggestion and decide whether to accept it or not. In your particular case, you don't want to accept it. You instead want to tell it to use the DNR. You do that by typing the in the name. Excel will then remember to use the DNR from then on for that pivot table.

Re: Pivot Tables: Pivot table layout

Only it doesn't keep the name entered in step 2, as the range box is blank in subsequent attempts to create pivot tables.

Re: Pivot Tables: Pivot table layout

It doesn't keep the name as a default for any new pivot table; it does keep it with the original pivot table you assigned it to. Remeber, Excel doesn't know what you really want when you create a new pivot table. You have to tell it.

Incidentally, if you want to have more than one pivot table use the same source you can base the second and suceeding pivot tables on the first one at step 1. Saves time and memory.

Re: Pivot Tables: Pivot table layout

It seems that we have finally come to the end of the road on this saga and that thankfully you have been able to resolve the problems and irritations that have emerged from my project. I am very grateful to you for this as well helping me learn more about Excel, which I can carry to future projects and perhaps, in turn, help others.

I obviously couldn't have completed this project without your help and wish to express my sincerest gratitude to you.
I hope you continue to cast your wizardry wisdom to all help seekers in the near future and in the millenia to come.