Excel formulas not working on new computer

[kmoreau48]

Hello everyone,

I built an excel sheet where some formulas don't seem to be working on my new computer.

Description of the workbook and its formulas: The workbook contains daily timesheets where the data is then compiled by job number on the sheets between "start" and "end"
In the excel sheets 0 to 5, the formula used in the range D4:Q30 works on the computer where I made the original excel workbook and it should return the value based on the name of the employee and the job number from the daily timesheets.

Hoping that someone can point me in the right direction in order figure out why the formulas doesn't work on my new computer.

I couldn't figure out how to attach a file to my post so here is a dropbox link:
https://www.dropbox.com/s/2i8uqn8xx9ercj4/19-1665_FDT_SF-2018-10-20_TEST.xlsx?dl=0

 

[AlanY]

I think basically it is lost in translation

your formula in D4 etc has INDIRECT(TEXT(D$2,"jjj")&"!$B:$B")...

the text in D$2 is Sun but you don't have that in your sheet name, i guess it's Dim in french?
try to change one or the other to see if it works

 

[kmoreau48]

Thanks for pointing that out, I think you're right, I made the workbook on a french computer. Wonder how I could make the workbook compatible with both English and French language... if you have any tips that would be appreciated. At least now I know what the issue is.

Thanks,

 

[BlueHornet]

If you have to use formulas that refer to sheet names that may change from time to time (whether due to language difference or user preference or even error), it would be well to become familiar with the too-little-used
=CELL( ) functions.

First take a look at plain old =CELL( "filename") and see the value that is entered. (Enter it exactly that way. Don't substitute your filename, but use the term "filename" as written.)

Now put these formulas somewhere in a spreadsheet to test them, and you'll see what I mean:
=LEFT( CELL( "filename"), FIND( "[", CELL( "filename")) -1 )
=MID( CELL( "filename"), FIND( "[", CELL( "filename")) + 1, FIND( "]", CELL( "filename")) - FIND( "[", CELL( "filename")) -1)
=RIGHT( CELL( "filename"), LEN( CELL( "filename")) - FIND( "]", CELL( "filename")))

Using the last formula in particular will give you a volatile cell that will ALWAYS contain the exact name of the worksheet.