Language issues in formula

[nerzzul]

Greetings!

i have created a workbook with several sheets on it and the data takes readings from the sheetname. below are options and all of them, do the job for me what i need.

Option 1:
=MID(CELL("filename",I9),FIND("]",CELL("filename",I9))+1,255)

Option 2:
=RIGHT(CELL("filename",M9),LEN(CELL("filename",M9))-FIND("]",CELL("filename",M9)))

Option 3:
=REPLACE(CELL("filename",K9),1,FIND("]",CELL("filename",K9)),"")

so my question is when i share the file with someone who is not using excel in "English" language, they have a trouble and the formula is not working, because the word "filename" isn't converting/translating and the rest of the formula is. This problem happens to Turkish language office 2013 and newer versions. (haven't tested below versions).

any advise how we may shall process to fix the issue? since its very important and whole workbook is based on sheetnames.

thanks in advance for the help.

Sincerely,
Nerz

 

[dave3009]

Hi Nerz, welcome to the board,

Is the error happening when the user opens this from say an email? It is possible that as it isn't saved locally the translation won't occur i.e. the CELL("filename") returns nothing as there is no full filename.

Regards

Dave

 

[Aladin Akyurek]

As far as I know, Excel has not been able to convert such quoted textual specifications when in a different language environment.

 

[Peter_SSs]

Would either of these options be feasible for you?

1. Protect the workbook so that the sheet names cannot be changed?

2. Use some vba 'event' code that regularly updates the relevant sheet name(s) into the appropriate cell(s)?
I don't know how time-critical the update is for you but the process could run when a worksheet is activated, when a value is changed on the/a worksheet, when a different cell/range is selected, when the worksheet calculates etc.

 

[Peter_SSs]

I have no experience at all in dealing with such international issues, so I'm really just guessing.
This would only work, if it works at all, for one pair of language conversions.
If your existing formula is translated automatically all except "filename", then could you use the following where "xxxx" is whatever would be used in the equivalent Turkish formula instead of "filename"?

=IFERROR(REPLACE(CELL("filename",K9),1,FIND("]",CELL("filename",K9)),""),REPLACE(CELL("xxxx",K9),1,FIND("]",CELL("xxxx",K9)),""))

 

[MarcelBeug]

Just to inform you that in my Dutch Excel 2007 version, "filename" is still recognised, even without having the English language pack available.

So my suggestion would to first try Dave's option (post #2); if it doesn't work, Peter's option (post #5) looks quite feasible to me.

 

[nerzzul]

Hi Nerz, welcome to the board,

Is the error happening when the user opens this from say an email? It is possible that as it isn't saved locally the translation won't occur i.e. the CELL("filename") returns nothing as there is no full filename.

Regards

Dave

Dear Dave! thanks for the help, i have tried that already before, but no fix. still the same issue.

 

[nerzzul]

Hi Nerz, welcome to the board,

Is the error happening when the user opens this from say an email? It is possible that as it isn't saved locally the translation won't occur i.e. the CELL("filename") returns nothing as there is no full filename.

Regards

Dave

Dear Dave! thanks for the help, i have tried that already before, but no fix. still the same issue.

Would either of these options be feasible for you?

1. Protect the workbook so that the sheet names cannot be changed?

2. Use some vba 'event' code that regularly updates the relevant sheet name(s) into the appropriate cell(s)?
I don't know how time-critical the update is for you but the process could run when a worksheet is activated, when a value is changed on the/a worksheet, when a different cell/range is selected, when the worksheet calculates etc.

Dear Peter,

i am not very familiar with VBA, it may work what you are suggesting, could you please help me to try that out?

I have no experience at all in dealing with such international issues, so I'm really just guessing.
This would only work, if it works at all, for one pair of language conversions.
If your existing formula is translated automatically all except "filename", then could you use the following where "xxxx" is whatever would be used in the equivalent Turkish formula instead of "filename"?

=IFERROR(REPLACE(CELL("filename",K9),1,FIND("]",CELL("filename",K9)),""),REPLACE(CELL("xxxx",K9),1,FIND("]",CELL("xxxx",K9)),""))

i just try this formula and it did work! thanks so much for your great help! i highly appreciate it!

 

[Peter_SSs]

i just try this formula and it did work! thanks so much for your great help! i highly appreciate it!

Good news.

Does that mean we no longer need to investigate using vba?

 

[nerzzul]

Good news.

Does that mean we no longer need to investigate using vba?

everything is alright now we don't need the VBA code anymore i guess :D in the future if i require it, i know where to go )

thanks so much again!

Sincerely,
Nerz