Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable with No Source Data
Hi All,
I have a workbook that has data on one sheet that is used to feed a PivotTable on another sheet. I used a dynamic named range to allow the PivotTable range to grow/shrink without including blank rows. I also have macros coded to automatically refresh the PivotTable every time the sheet that it's on is activated. This setup works beautifully until all of the source data is deleted (i.e. the only thing left on the sheet with the data is the row with the column headings). If all of the source data is deleted, I receive the following message when the PivotTable sheet is activated: Run-time error '1004': This command requires at least two rows of source data. You cannot use the command on a selection in only one row. ...." I understand what the message means (a PivotTable requires more than one row of data) but can anyone recommend an elegant way to handle this situation? As the spreadsheet won't be used by me, I'd like the solution to allow users to enter and remove data freely. Any assistance would be greatly appreciated. Thanks! Kris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable with No Source Data
Hi
However you are defining your dynamic range, make the row count element the maximum of 1 and rowcount. something like =OFFSET($A$1,0,0,MAX(2,COUNTA($A:$A)),12) You have to have a header in row 1, hence COUNTA is bound to return 1, so just force it to be 2 if there is just the single header line. -- Regards Roger Govier wrote in message oups.com... Hi All, I have a workbook that has data on one sheet that is used to feed a PivotTable on another sheet. I used a dynamic named range to allow the PivotTable range to grow/shrink without including blank rows. I also have macros coded to automatically refresh the PivotTable every time the sheet that it's on is activated. This setup works beautifully until all of the source data is deleted (i.e. the only thing left on the sheet with the data is the row with the column headings). If all of the source data is deleted, I receive the following message when the PivotTable sheet is activated: Run-time error '1004': This command requires at least two rows of source data. You cannot use the command on a selection in only one row. ..." I understand what the message means (a PivotTable requires more than one row of data) but can anyone recommend an elegant way to handle this situation? As the spreadsheet won't be used by me, I'd like the solution to allow users to enter and remove data freely. Any assistance would be greatly appreciated. Thanks! Kris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable with No Source Data
Thank you very much Roger ... that worked like a charm! :)
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable with No Source Data
Oooops, spoke too soon. The solution worked in the sense that it
produced a blank PivotTable without generating an error but it caused a problem with a date column in the PivotTable that's being grouped. The PivotTable contains a date column that's grouped by Year and Month. Refreshing with a blank data source destroyed this grouping. My guess is that this is because you cannot grouping a column with blank rows. Any ideas? Thanks, Kris |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable with No Source Data
Hi Kris
Since you say you are running macros, could you not just ensure that there is a single "row" of data by just writing a date to cell A2 of the data sheet e.g. A2=TODAY() Then, the count will still include just one "blank" row of data as well as the headers, but the grouping will work because there will be a valid date in row 1. It will not affect any future PT reports as all other values in that row would be null. -- Regards Roger Govier wrote in message oups.com... Oooops, spoke too soon. The solution worked in the sense that it produced a blank PivotTable without generating an error but it caused a problem with a date column in the PivotTable that's being grouped. The PivotTable contains a date column that's grouped by Year and Month. Refreshing with a blank data source destroyed this grouping. My guess is that this is because you cannot grouping a column with blank rows. Any ideas? Thanks, Kris |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable with No Source Data
Thanks again for the responses Roger - your suggestions were a big
help. The way I ended up solving the problem was to create two rows in the source data that contained only the year and month and I used these two columns in my pivot table rather than the original date column that I was using. This approach allowed the PivotTable to automatically group by year and month without having to define a custom grouping. Sincerely, Kris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what types of source data can't be grouped in pivottable | Excel Discussion (Misc queries) | |||
Dynamic PivotTable Data Source | Excel Discussion (Misc queries) | |||
Pivottable - use same source data, and group data differently | Excel Discussion (Misc queries) | |||
Select different source data for a PivotTable | Excel Worksheet Functions | |||
How to source data from multiple worksheets in a PivotTable? | Excel Programming |