Vytautas Kacerauskis

Well... I think you need to do this.

1. what's in the cell itself, not just the result?
2. what's in the corresponding cell in the source file?

If you don't have the source then perhaps this is simply because it's a formula looking up information in another sheet that you don't have... or something like this.

I checked one cell. The original cell says "=SUM(D22:D24)".The translated target says "=SUM(D22:D24)".  Seems the same. But the cell in the translated target shows "#REF!", while  instead it should be "864 901"

Vytautas Kacerauskis

So, what's in cells D22 - D24?

Vytautas Kacerauskis

The reason I ask is because these are Excel errors.  So its likely that there is a formula in there that is looking for some hardcoded value that you have now translated.  So it can't find the value.

So your task, if it's down to you, is to trace the location of this formula and fix the problem.  Or at least point out to your customer what the problem is so they can fix it.

In the original cell D22 has a number "266 112". The formular bar says: "='Metinis atlyginimas'!AN10".  In the target translation cell D22 has "#REF!" And the formular bar says: "='Annual remuneration'!AN10" 

interestingly, in the same table, some of the cells are fine (i.e. show normal numbers), but the majority are messed up (show #REF!)

Vytautas Kacerauskis

ok - I think this explains the problem.  Metinis atlyginimas and Annual remuneration are the names of Worksheets in your Workbook.  You probably translated them so the names in the forumula don't exist anymore.

The easiest solution to fix this is to filter on all the segments that are Worksheet names and copy source to target.  Then save the target file.  To do that:

Use the Advanced Display Filter if yo have 2021, or the Community Advanced Display Filter if you have 2019:

Probably a good idea to uncheck this box when you translate Excel Workbooks that make use of formulae like this.  So always ask that question or inspect the files before you start:

Vytautas Kacerauskis

ok - I think this explains the problem.  Metinis atlyginimas and Annual remuneration are the names of Worksheets in your Workbook.  You probably translated them so the names in the forumula don't exist anymore.

The easiest solution to fix this is to filter on all the segments that are Worksheet names and copy source to target.  Then save the target file.  To do that:

Use the Advanced Display Filter if yo have 2021, or the Community Advanced Display Filter if you have 2019:

Probably a good idea to uncheck this box when you translate Excel Workbooks that make use of formulae like this.  So always ask that question or inspect the files before you start:

Thanks a lot Paul, I'll try to do that. So, as i understand the Worksheet Names MUST NOT be translated? Correct?

Vytautas Kacerauskis

That depends on the clients instructions.  They may have a workbook where the sheetnames need to be translated.  In this case you need to ask the right questions to make them aware of the implications... and also ensure you get paid for the time to resolve it if they also want you to make sure any formulae using sheet names still work.

You can do this in Excel using this option in the search and replace for example:

But if you do this you are taking on the responsibility of making sure the spreadsheet still functions correctly.  I wouldn't do that unless specifically asked.

Unfortunately, I now see that some of the worksheets that have been "untouched" by translation yet (Worksheet names unchanged either), also have cells that read ###### or something like that instead of normal numers. 

I was wondering, if I re-created the project and, under file types/common, asked TO INCLUDE number segments in the translation, so that actual numbers appeared in the Source side of the Editor, and then simply copied the numbers from Source to Target, could that help?

Vytautas Kacerauskis

##### probably just means the Columns are not wide enough to see the number. Not a problem.

Paul, this WORKED. I recreated the project, unchecked Sheet Names, retranslated from the memory, and all the numbers seem alright now in the target excel file. I am only half through the translation, but I hope it'll be fine from now on.

Vytautas Kacerauskis

Excellent... glad you're sorted.  Thanks for letting us know.