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

Hi.
Column D-E data is from a vlookup table (another sheet). On '1st' etc
worksheets, column D is text only from the vlookup and too the E column with
price in Accounting format. As for column F, that is only formatted as a
Number.

Reading from other posts in various Excel groups, I expected D-F when
consolidated from all 31 worksheets would create a pivot with the D column
products on X (bottom axis) and count Y (left axis) showing F column right
side that shows the catagory/legend. The graph is used to see what products
were sold, how many products were sold and in what catagories.

ie. How many green pencils sold in catagory A, how many red in catagory A
and how many green in catagory B and so on. This will show up in a pivot for
the whole month from each day (31 worksheets) graphed for the month. As the
data is chosen from the vlookup, and refreshed on the pivot, wullah.

Highly prefered not to use one sheet for every day of the month. One
worksheet is one day. D column will show the products sold, E the price, and
F manually entered the catagory.

Only column F will not be added to the pivot when new data is added to the
day. The only sticking point. Everything else is perfect.

Thank you.


"Debra Dalgleish" wrote:

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

If column F contains a category, entered as text, all it will ever show
in the consolidated pivot table is a sum of zero, or a Count of the records.

The items in D are in the Row field, so they'll appear when updated.
Column E contains numbers, so those will also be updated.

Maybe you could create a new column, to the left of the Price column, to
combine the values in D & F:
= D6 & " -- " & F6
Then create the pivot table from the new column, and the price column.

sean wrote:
Hi.
Column D-E data is from a vlookup table (another sheet). On '1st' etc
worksheets, column D is text only from the vlookup and too the E column with
price in Accounting format. As for column F, that is only formatted as a
Number.

Reading from other posts in various Excel groups, I expected D-F when
consolidated from all 31 worksheets would create a pivot with the D column
products on X (bottom axis) and count Y (left axis) showing F column right
side that shows the catagory/legend. The graph is used to see what products
were sold, how many products were sold and in what catagories.

ie. How many green pencils sold in catagory A, how many red in catagory A
and how many green in catagory B and so on. This will show up in a pivot for
the whole month from each day (31 worksheets) graphed for the month. As the
data is chosen from the vlookup, and refreshed on the pivot, wullah.

Highly prefered not to use one sheet for every day of the month. One
worksheet is one day. D column will show the products sold, E the price, and
F manually entered the catagory.

Only column F will not be added to the pivot when new data is added to the
day. The only sticking point. Everything else is perfect.

Thank you.


"Debra Dalgleish" wrote:


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