Problem with multi consolidation.

After a pivot table and chart are created, within the data ranges of the
source data more data is added but the data will not display upon refreshing
the pivot. Why is that?

So, having many worksheets using multi consolidation pivot, if any extra
data is added to the worksheets within the pivot source range, when
refreshing the pivot table/chart, that data will show up in the pivot.

This is a sticking point for the purpose required.

You can create dynamic ranges for the data, and refer to those when
adding the multiple consolidation ranges. There are naming instructions
he

http://www.contextures.com/xlPivot01.html

sean wrote:
Problem with multi consolidation.

After a pivot table and chart are created, within the data ranges of the
source data more data is added but the data will not display upon refreshing
the pivot. Why is that?

So, having many worksheets using multi consolidation pivot, if any extra
data is added to the worksheets within the pivot source range, when
refreshing the pivot table/chart, that data will show up in the pivot.

This is a sticking point for the purpose required.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Hi Debra.

I tried what you sent to me in steps 1 & 2 on your webpage and the column I
need (F5) doesn't work properly in displaying that data that row in the pivot
table. F5 shows the previous data and not the data i entered. D5 is fine.

I used these as per my worksheets with multi consolidation:
Range named as "DynamicPivot1" for 1st worksheet and DynamicPivot2 for
2nd... etc.

=OFFSET('1st'!$D$5,0,0,COUNTA('1st'!$D$5:F91),7)
=OFFSET('2nd'!$D$5,0,0,COUNTA('2nd'!$D$5:F91),7)

* Using the above, the range seems unlimited rows where I would liek it to
stop at F91.
* Tried the above and noticed the F91 range changes to another cell like F70
for some reason.
* Tried variations like:
=OFFSET('1st'!$D$5,0,0,COUNTA('1st'!$D$5:F91),3)
=OFFSET('2'!$D$5,0,0,COUNTA('2'!$D$5:F91),3)
"3" because F column is the last for pivoting/charts from "D".

Next creating the pivot, I named the range as:
DynamicPivot1
DynamicPivot2
After this, F5 column will not refresh the correct data for that row.

Help.


"Debra Dalgleish" wrote:

You can create dynamic ranges for the data, and refer to those when
adding the multiple consolidation ranges. There are naming instructions
he

http://www.contextures.com/xlPivot01.html

sean wrote:
Problem with multi consolidation.

After a pivot table and chart are created, within the data ranges of the
source data more data is added but the data will not display upon refreshing
the pivot. Why is that?

So, having many worksheets using multi consolidation pivot, if any extra
data is added to the worksheets within the pivot source range, when
refreshing the pivot table/chart, that data will show up in the pivot.

This is a sticking point for the purpose required.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

In the sample formula on the web page, the COUNTA function was used on
the entire column A:

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),7)

In your formula, you're counting the contents of a set range, $D$5:F91,
so it won't adjust if more rows are added.

Try changing it to:

=OFFSET('1st'!$D$5,0,0,COUNTA('1st'!$D:$D),7)

Also, the sample formula ends with a 7, which is the set number of
columns. You should change that to the number of columns in your source
table.


sean wrote:
Hi Debra.

I tried what you sent to me in steps 1 & 2 on your webpage and the column I
need (F5) doesn't work properly in displaying that data that row in the pivot
table. F5 shows the previous data and not the data i entered. D5 is fine.

I used these as per my worksheets with multi consolidation:
Range named as "DynamicPivot1" for 1st worksheet and DynamicPivot2 for
2nd... etc.

=OFFSET('1st'!$D$5,0,0,COUNTA('1st'!$D$5:F91),7)
=OFFSET('2nd'!$D$5,0,0,COUNTA('2nd'!$D$5:F91),7)

* Using the above, the range seems unlimited rows where I would liek it to
stop at F91.
* Tried the above and noticed the F91 range changes to another cell like F70
for some reason.
* Tried variations like:
=OFFSET('1st'!$D$5,0,0,COUNTA('1st'!$D$5:F91),3)
=OFFSET('2'!$D$5,0,0,COUNTA('2'!$D$5:F91),3)
"3" because F column is the last for pivoting/charts from "D".

Next creating the pivot, I named the range as:
DynamicPivot1
DynamicPivot2
After this, F5 column will not refresh the correct data for that row.

Help.


"Debra Dalgleish" wrote:


You can create dynamic ranges for the data, and refer to those when
adding the multiple consolidation ranges. There are naming instructions
he

http://www.contextures.com/xlPivot01.html

sean wrote:

Problem with multi consolidation.

After a pivot table and chart are created, within the data ranges of the
source data more data is added but the data will not display upon refreshing
the pivot. Why is that?

So, having many worksheets using multi consolidation pivot, if any extra
data is added to the worksheets within the pivot source range, when
refreshing the pivot table/chart, that data will show up in the pivot.

This is a sticking point for the purpose required.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Hello Debra. This is driving me crazy.

The data range is not being all captured in the pivot but the data is in the
range. The 3rd column never updates with new added data inputed but the row
(D) updates but not the 3rd (F) column.

Using specifically OFFSET('1st'!$D$5,0,0,COUNTA('1st'!$D$5:$F$91),3) for the
fixed range. The data for the pivot starts in D5:F5 and only down to row 91.
It is not required to go any further as no more rows will be entered. In the
worksheet "1st", "2"..."31", I'm using multiconsolidation as there are 31
worksheets (each day in 1mth).

When I create a defined range
=OFFSET('sheet'!$D$5,0,0,COUNTA('sheet'!$D$5:$F$91 ),3) and is named as
DynamicPivot, using multiconsolidation I add the ranges DynamicPivot1,
DynamicPivot2 (each day of month) and labels are automatic for the chart
report. When I add new data to the sheet beneath the current data already in
sheet "1st" or "2" etc, that data is not dynamically listed in the pivot from
column "F" but "D" updates. Mind you there are blank rows to seperate entries
specifically and BLANK is unchecked in the pivot.

It's driving me mad. What is the problem Debra?


"Debra Dalgleish" wrote:

In the sample formula on the web page, the COUNTA function was used on
the entire column A:

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),7)

In your formula, you're counting the contents of a set range, $D$5:F91,
so it won't adjust if more rows are added.

Try changing it to:

=OFFSET('1st'!$D$5,0,0,COUNTA('1st'!$D:$D),7)

Also, the sample formula ends with a 7, which is the set number of
columns. You should change that to the number of columns in your source
table.


sean wrote:
Hi Debra.

I tried what you sent to me in steps 1 & 2 on your webpage and the column I
need (F5) doesn't work properly in displaying that data that row in the pivot
table. F5 shows the previous data and not the data i entered. D5 is fine.

I used these as per my worksheets with multi consolidation:
Range named as "DynamicPivot1" for 1st worksheet and DynamicPivot2 for
2nd... etc.

=OFFSET('1st'!$D$5,0,0,COUNTA('1st'!$D$5:F91),7)
=OFFSET('2nd'!$D$5,0,0,COUNTA('2nd'!$D$5:F91),7)

* Using the above, the range seems unlimited rows where I would liek it to
stop at F91.
* Tried the above and noticed the F91 range changes to another cell like F70
for some reason.
* Tried variations like:
=OFFSET('1st'!$D$5,0,0,COUNTA('1st'!$D$5:F91),3)
=OFFSET('2'!$D$5,0,0,COUNTA('2'!$D$5:F91),3)
"3" because F column is the last for pivoting/charts from "D".

Next creating the pivot, I named the range as:
DynamicPivot1
DynamicPivot2
After this, F5 column will not refresh the correct data for that row.

Help.


"Debra Dalgleish" wrote:


You can create dynamic ranges for the data, and refer to those when
adding the multiple consolidation ranges. There are naming instructions
he

http://www.contextures.com/xlPivot01.html

sean wrote:

Problem with multi consolidation.

After a pivot table and chart are created, within the data ranges of the
source data more data is added but the data will not display upon refreshing
the pivot. Why is that?

So, having many worksheets using multi consolidation pivot, if any extra
data is added to the worksheets within the pivot source range, when
refreshing the pivot table/chart, that data will show up in the pivot.

This is a sticking point for the purpose required.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

What type of data is in each column D, E and F?
Only the entries in column D will be listed in the consolidated pivot
table. Columns E and F will appear as column headings, and only a total
sum or count will appear for those items.
Is that what you expect to happen?

Can you store all the data on one sheet, with an extra column for the
date, instead of storing it on separate sheets?

sean wrote:
Hello Debra. This is driving me crazy.

The data range is not being all captured in the pivot but the data is in the
range. The 3rd column never updates with new added data inputed but the row
(D) updates but not the 3rd (F) column.

Using specifically OFFSET('1st'!$D$5,0,0,COUNTA('1st'!$D$5:$F$91),3) for the
fixed range. The data for the pivot starts in D5:F5 and only down to row 91.
It is not required to go any further as no more rows will be entered. In the
worksheet "1st", "2"..."31", I'm using multiconsolidation as there are 31
worksheets (each day in 1mth).

When I create a defined range
=OFFSET('sheet'!$D$5,0,0,COUNTA('sheet'!$D$5:$F$91 ),3) and is named as
DynamicPivot, using multiconsolidation I add the ranges DynamicPivot1,
DynamicPivot2 (each day of month) and labels are automatic for the chart
report. When I add new data to the sheet beneath the current data already in
sheet "1st" or "2" etc, that data is not dynamically listed in the pivot from
column "F" but "D" updates. Mind you there are blank rows to seperate entries
specifically and BLANK is unchecked in the pivot.

It's driving me mad. What is the problem Debra?


"Debra Dalgleish" wrote:


In the sample formula on the web page, the COUNTA function was used on
the entire column A:

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),7)

In your formula, you're counting the contents of a set range, $D$5:F91,
so it won't adjust if more rows are added.

Try changing it to:

=OFFSET('1st'!$D$5,0,0,COUNTA('1st'!$D:$D),7)

Also, the sample formula ends with a 7, which is the set number of
columns. You should change that to the number of columns in your source
table.


sean wrote:

Hi Debra.

I tried what you sent to me in steps 1 & 2 on your webpage and the column I
need (F5) doesn't work properly in displaying that data that row in the pivot
table. F5 shows the previous data and not the data i entered. D5 is fine.

I used these as per my worksheets with multi consolidation:
Range named as "DynamicPivot1" for 1st worksheet and DynamicPivot2 for
2nd... etc.

=OFFSET('1st'!$D$5,0,0,COUNTA('1st'!$D$5:F91),7 )
=OFFSET('2nd'!$D$5,0,0,COUNTA('2nd'!$D$5:F91),7 )

* Using the above, the range seems unlimited rows where I would liek it to
stop at F91.
* Tried the above and noticed the F91 range changes to another cell like F70
for some reason.
* Tried variations like:
=OFFSET('1st'!$D$5,0,0,COUNTA('1st'!$D$5:F91),3 )
=OFFSET('2'!$D$5,0,0,COUNTA('2'!$D$5:F91),3)
"3" because F column is the last for pivoting/charts from "D".

Next creating the pivot, I named the range as:
DynamicPivot1
DynamicPivot2
After this, F5 column will not refresh the correct data for that row.

Help.


"Debra Dalgleish" wrote:



You can create dynamic ranges for the data, and refer to those when
adding the multiple consolidation ranges. There are naming instructions
he

http://www.contextures.com/xlPivot01.html

sean wrote:


Problem with multi consolidation.

After a pivot table and chart are created, within the data ranges of the
source data more data is added but the data will not display upon refreshing
the pivot. Why is that?

So, having many worksheets using multi consolidation pivot, if any extra
data is added to the worksheets within the pivot source range, when
refreshing the pivot table/chart, that data will show up in the pivot.

This is a sticking point for the purpose required.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html