PivotTable with No Source Data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

k.vanderstarren@gmail.com - 19 Jan 2006 21:16 GMT

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

Reply to this Message

Roger Govier - 20 Jan 2006 01:01 GMT

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.

Signature

Regards

Roger Govier

> Hi All,
>
[quoted text clipped - 24 lines]
> Thanks!
> Kris

Reply to this Message

k.vanderstarren@gmail.com - 20 Jan 2006 13:16 GMT

Thank you very much Roger ... that worked like a charm! :)

Reply to this Message

k.vanderstarren@gmail.com - 20 Jan 2006 13:55 GMT

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

Reply to this Message

Roger Govier - 20 Jan 2006 14:25 GMT

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.

Signature

Regards

Roger Govier

> Oooops, spoke too soon. The solution worked in the sense that it
> produced a blank PivotTable without generating an error but it caused
[quoted text clipped - 10 lines]
> Thanks,
> Kris

Reply to this Message

k.vanderstarren@gmail.com - 23 Jan 2006 17:42 GMT

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 to this Message