• State Suggested Answer
  • Replies 2 replies
  • Answers 1 answer
  • Subscribers 433 subscribers
  • Views 220 views
  • Users 0 members are here
Options
Related

Handling translation of Excel files with Pivot Tables and embedded cached data that are used in those Pivot tables

Simon Bratina

Hello, 

This question may not be totally Trados Studio, but I'm asking it anyway.

I got and excel workbook with couple of worksheets with PivotTables.

Client wanted us to translate what is visible on the worksheets (what Pivot criteria filters).  And Studio didn't have a problem processing that file and generating target translation. 

But ... when I opened generated Excel file, I was able to see the translations as long Excel was in Read-only-mode.
Excel screenshot showing Protected View warning and a PivotTable with columns for Description of location, Name, Description 1, and Description 2 with items like MONITOR and LEASED EQUIPMENT.

Once I enable Editing (in Excel dialog) Pivot tables get refreshed and translations get replaced with the source language.  

Excel screenshot with a PivotTable displaying columns Opis lokacije, Naziv, Opis 1, and items such as MONITOR and OPREMA V NAJEMU, indicating a translated version of the first image.



And here are the questions to more experienced in the Community: 

- can anyone suggest a solution that would all me to preserve the translated  content of the above mentioned example even after allowing teh eddititng of that file?

- can anyone suggest a general best practice suggestion for processing the Excel file with Pivot tables, where dataset snapshot  iz either cached or embeded in this Excel or there is an external Dataset connected to this excel. 

Thank you

Simon

Generated Image Alt-Text
[edited by: RWS Community AI at 12:48 PM (GMT 1) on 11 Oct 2024]
emoji
Rate translation
×
Suggest better translation
×
Moderator UI
×
  • Get AI Suggestion
Parents
  • 0

     

    Since the issue arises when the pivot tables are refreshing and when editing is enabled in Excel causing them to revert to the source data instead of displaying the translated content here's a few suggestions... although I imagine you have thought of these already:

    1. Disable the automatic refresh of pivot tables when the workbook is opened or when editing is enabled.

    2. If your client only requires the translated data without needing pivot table functionality, you could copy the pivot table and paste it as values in a new sheet.  This way the translated text would be preserved without the risk of refreshing back to the source data.

    3. Instead of linking to an external dataset (if this is the case?), try embedding a snapshot of the data directly in the workbook, which may reduce the likelihood of an automatic refresh.

    4. If the pivot table must remain dynamic, maybe use a macro that re-applies the translated content after each refresh.  So store the translated text in a hidden sheet or file and re-inject it into the pivot table fields as needed.

    5. Perhaps consider exporting the data to a format that doesn’t rely on pivot tables (like CSV), translate it, and then re-import it if necessary.  This would avoid Excel’s pivot refresh behaviour entirely.

    6. For processing Excel files with pivot tables, it might even be best to ensure that any external data dependencies are removed before translation work begins.  So try using "Values Only" sheets for translation if pivot tables or formulas aren’t strictly necessary for the final format.

    Just some thoughts... no idea if they'll be helpful or not.

    Paul Filkin | RWS Group

    ________________________
    Design your own training!
    You've done the courses and still need to go a little further, or still not clear? 
    Tell us what you need in our Community Solutions Hub

    emoji
  • 0 in reply to Paul

    Hi  

    Thanks' for the quick feedback and a number of suggestions.

    In the end we did copy "Pivot table results" and pasted them as Static tables with values on additional sheets.

    I was looking also in the other options but with now usefull result - as data is cached in the Excel file itself.

    I'll check the options again.

    Best regards, 

    Simon
    emoji
Reply Children
No Data