Handeling different languages in a workbook

[PO]

Excel 2003, Sp2

Hello,

I have a workbook that consists of 3 different sheets.
2 of them contain instructions and diagrams and are "static", i.e. the user
doesn't change any data. The third sheet, data sheet, is created from a
hidden template sheet. The user can create as many data sheets as he likes.
Usually, however, the number of data sheets doesn't exceed more then 6-7
sheets.

Next years version of the workbook needs to handle 4 different languages,
i.e. all column/row/cell descriptions need to be converted to the users
native language if needed (using a custom menu).

I was thinking of 2 ways to solve this:

1. Using a hidden translation table and replace all column/row/cell
descriptions with lookup formulas.
2. Using a hidden translation table with a sheet!cell reference column
(since the user can insert rows in the data sheet the reference has to be
linked to the cell it's representing) and a procedure that iterates through
all the Sheet!cell references, updating the entire workbook at runtime.

Creating 4 different versions of the file isn't an option and the file
should be self contained, i.e. I don't want to use resource or other files
that have to be installed.

Alternative 1 might cause performance problems since there will be a lot of
lookup formulas that need to be recalculated, especially if the user has
created several data sheets.

I would appreciate some suggestions, maybe there is a better way to solve
this?

TIA
Pete

 

[Alexander Wolff]

2. Using a hidden translation table with a sheet!cell reference column

(since the user can insert rows in the data sheet the reference has
to be linked to the cell it's representing) and a procedure that
iterates through all the Sheet!cell references, updating the entire
workbook at runtime.

This is the better one. Even the cell ref could be omitted (simple search -
replace), but it is a good idea for safety if you have many short words
which could replace parts of cells. So, for a search - replace, better use
the option "whole cells" (I use German version, so I don't know the exact
option's name).

 

[charabeuh]

hello,

see the exemple http://cjoint.com/?lbfOP05jNi

Excel 2003 SP3

 

[PO]

Hi,

Thanks both of you! Charabeuh's example is using alt. 1. It works fine but
I'm not sure how performance will be when there are 4-500 translations to be
made.

Guess I need to do some tests.

Regards
Pete

 

[charabeuh]

hello,

All references from datasheet cells to the translation sheet have to be
absolute references and not relative references (as they are in my example).
So users can insert new lines and columns in their datasheets.

Feed back would be great.