Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Excel 2007 - Change data source Table head's cells' values ...
Hi,
I have a Table "Table1" as data source to two pivots on one sheet. I switch my file/application between different languages - the heads in the table are still meaning the same and also the pivots should still be looking in the same way. With VBA, I change the value in the table's head cells, and also changes the content in one column (2) where the filered word "yes" will be "Ja" in swedish etc. So it is both the tables head and some content that is changed. The versions table "Table1" the pivots are based on: - English Table version: Eng_Col1 Eng_Col2 Eng_Col3 1 Yes 234 2 No 234 3 Yes 234 4 Yes 3242 5 No 435 - Swedish Table version: Swe_Kol1 Swe_Kol2 Swe_Kol3 1 Ja 234 2 Nej 234 3 Ja 234 4 Ja 3242 5 Nej 435 One Pivot use "Yes" as Report Filter and another use "No". This means after I change to swedish language in the table, the first pivot uses "Ja" and the other uses "Nej". I have the Yes/Ja and No/Nej as named ranges: Yes: "Range_Eng_Yes Ja: "Range_Swe_Yes" No: "Range_Eng_No" Nej: "Range_Swe_No" ---- - English Pivot results after needed macro: Eng_Col2 (Yes) Sum of Eng_Col3 Eng_Col2 Total 1 234 2 234 3 234 4 3242 5 435 Grand Total 4379 - Swedish Pivot results after needed macro Swe_Kol2 (Ja) Sum of Swe_Kol3 Swe_Kol2 Total 1 234 2 234 3 234 4 3242 5 435 Grand Total 4379 ------ Cay you help me out with this? It should be the same fields hidden and visible in both languages. The thing that makes it a bit more complicated is the fact the filtered data in the pivot's "Report filter" also changes. Many thanks in advance /Tskogstrom |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Excel 2007 - Change data source Table head's cells' values ...
surely you only need one source table?
if you change, in the table, all "Yes" values to "Ja" and "No" values to "Nej" ... when you refresh the pivot table, this will pick up the chages you then don't need the "Eng" or "Swe" prefixes alternatively keep one table, but add another column for the other language Yes Ja 234 No Nej 234 .... "tskogstrom" wrote in message ... Hi, I have a Table "Table1" as data source to two pivots on one sheet. I switch my file/application between different languages - the heads in the table are still meaning the same and also the pivots should still be looking in the same way. With VBA, I change the value in the table's head cells, and also changes the content in one column (2) where the filered word "yes" will be "Ja" in swedish etc. So it is both the tables head and some content that is changed. The versions table "Table1" the pivots are based on: - English Table version: Eng_Col1 Eng_Col2 Eng_Col3 1 Yes 234 2 No 234 3 Yes 234 4 Yes 3242 5 No 435 - Swedish Table version: Swe_Kol1 Swe_Kol2 Swe_Kol3 1 Ja 234 2 Nej 234 3 Ja 234 4 Ja 3242 5 Nej 435 One Pivot use "Yes" as Report Filter and another use "No". This means after I change to swedish language in the table, the first pivot uses "Ja" and the other uses "Nej". I have the Yes/Ja and No/Nej as named ranges: Yes: "Range_Eng_Yes Ja: "Range_Swe_Yes" No: "Range_Eng_No" Nej: "Range_Swe_No" ---- - English Pivot results after needed macro: Eng_Col2 (Yes) Sum of Eng_Col3 Eng_Col2 Total 1 234 2 234 3 234 4 3242 5 435 Grand Total 4379 - Swedish Pivot results after needed macro Swe_Kol2 (Ja) Sum of Swe_Kol3 Swe_Kol2 Total 1 234 2 234 3 234 4 3242 5 435 Grand Total 4379 ------ Cay you help me out with this? It should be the same fields hidden and visible in both languages. The thing that makes it a bit more complicated is the fact the filtered data in the pivot's "Report filter" also changes. Many thanks in advance /Tskogstrom |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Excel 2007 - Change data source Table head's cells' values...
I'm not following you. When the user change language (it actually is
more languages than just the two examples) tha pivots' source, Table1's, heads are updated by code and also one of the columns (with a fixed validating choice list also changing language) cells' values are replaced with the new languages' words. Do you mean I should have columns hidden for every language and always run code on cells' changes to update the value in the associated cells for the other languages.? regards Tskogstrom ------------------------------------------------------- On May 26, 2:12*pm, "Patrick Molloy" wrote: surely you only need one source table? if you change, in the table, all "Yes" *values to "Ja" and "No" values to "Nej" ... when you refresh the pivot table, this will pick up the chages you then don't need the "Eng" or "Swe" prefixes alternatively keep one table, but add another column for the other language Yes Ja 234 No *Nej 234 ... "tskogstrom" wrote in message ... Hi, I have a Table "Table1" as data source to two pivots on one sheet. I switch my file/application between different languages - the heads in the table are still meaning the same and also the pivots should still be looking in the same way. With VBA, I change the value in the table's head cells, and also changes the content in one column (2) where the filered word "yes" will be "Ja" in swedish etc. So it is both the tables head and some content that is changed. The versions table "Table1" the pivots are based on: - English Table version: Eng_Col1 Eng_Col2 Eng_Col3 1 Yes 234 2 No 234 3 Yes 234 4 Yes 3242 5 No 435 - Swedish Table version: Swe_Kol1 Swe_Kol2 Swe_Kol3 1 Ja 234 2 Nej 234 3 Ja 234 4 Ja 3242 5 Nej 435 One Pivot use "Yes" as Report Filter and another use "No". This means after I change to swedish language in the table, the first pivot uses "Ja" and the other uses "Nej". I have the Yes/Ja and No/Nej as named ranges: Yes: "Range_Eng_Yes Ja: "Range_Swe_Yes" No: "Range_Eng_No" Nej: "Range_Swe_No" ---- - English Pivot results after needed macro: Eng_Col2 (Yes) Sum of Eng_Col3 Eng_Col2 Total 1 234 2 234 3 234 4 3242 5 435 Grand Total 4379 - Swedish Pivot results after needed macro Swe_Kol2 (Ja) Sum of Swe_Kol3 Swe_Kol2 Total 1 234 2 234 3 234 4 3242 5 435 Grand Total 4379 ------ Cay you help me out with this? It should be the same fields hidden and visible in both languages. The thing that makes it a bit more complicated is the fact the filtered data in the pivot's "Report filter" also changes. Many thanks in advance /Tskogstrom |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Excel 2007 - Change data source Table head's cells' values...
Also, when you change the source table's heads, and run a refresch in
the pivots, they becomes empty. And building the source table with parallell languages hidden, demands parallell hidden pivots, and that will not work in this case, it will be other problems from that. Any other ideas? regards Tskogstrom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table-Change Data Source? Excel 2007 | Charts and Charting in Excel | |||
Excel 2007 Pivot Table Changes the Source Data Range | Excel Discussion (Misc queries) | |||
source data for pivot table 2007 | Excel Discussion (Misc queries) | |||
View pivot table external data source in Excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 change pivot data source | Excel Discussion (Misc queries) |