Using same connection in all worksheets?

[supportsvc]

I have a workbook with 5 worksheets
of which 4 are pivot tables and charts

I can't seem to figure out UNPIVOT to a table format?
As I need the 5th worksheet to use the same connection, so when clicking Existing Connection to add to the 5th worksheet, this as a Table format (AKA: RawData)

It added another Connection

Connection in the 4 worksheets is: YearlyOrders
the 5th worksheet, even though selecting Existing Connection, created another: YearlyOrders1
when I try to edit and remove the 1, it says connection already exists.

How do I have all the worksheets using the one connection: YearlyOrders?

 

[SkipVought]

Hi,

“I can't seem to figure out UNPIVOT to a table format?”

Do you want the result to be a table that looks just like your PT, or do you want the result to be a normalized table that contains one column for each ROW area and two colums for the COLUMN area?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[supportsvc]

Hello,

Looking for the latter "normalized table that contains one column for each ROW area and two colums for the COLUMN area"

AKA: "RawData

 

[SkipVought]

Check out this...
faq68-7103

This process was designed to make a table from pivoted data, not from a PivotTable.

But it can be adapted by 1) selecting in your PT. then 2) drag the ROWS field(s) and COLUMNS field(s) out of the ROWS/COLUMNS area and up into the Choose Fields area.

Then double-click the TOTAL VALUE cell to generate the Normalized Table.

I have never used it this way before, but my testing seems to confirm that this is what happens.

GOOD LUCK! let me know how it turns out for you.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[supportsvc]

Not seeing
2) Step 1 of 3 - Select Option Button: Multiple Consolidation Ranges -- [Next]

 

[SkipVought]

Since you are working from a PivotTable rather than a pivoted table (ie pivoted by some other process that is not directly a PivotTable), I suggested that you start with the step in my process (for a pivoted table rather than a PivotTable), start with dragging the ROWS/COLUMNS fields FROM those areas respectively TO the Choose fields area.

6) Drag the Row and Column buttons OFF the Layout

7) Mysteriously, you are on another sheet that has a 4-cell pivot table. Double click the BOTTOM RIGHT CELL

8) Again, on another sheet -- This is you data normalized, or at least closer to it. You'll need to change headings at least.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[supportsvc]

I get an error message

We can't make this change for the selected cells because it will affect a PivotTable. Use the field list to change the report. If you are trying to insert or delete cells, move the PivotTable and try again.

I know that if you double-click on a column cell in the pivot, it'll open the details for that one item into another sheet. But won't do it if you select all of it.

However, that looses the connection.

 

[SkipVought]

Did you “Use the [PivotTable] field list to change the report“?

You should not be inserting or deleting cells in the PT.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[supportsvc]

?

start with dragging the ROWS/COLUMNS fields FROM those areas respectively TO the Choose fields area.

 

[SkipVought]

My PT

Drag or REMOVE ROWS field(s)

Drag or REMOVE COLUMNS field(s)

Double-Click Total

Result is my external source data table on new sheet...

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]