SOLVED

Error when opening the file with Offset and Column within the formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2272031%22%20slang%3D%22en-US%22%3EError%20when%20opening%20the%20file%20with%20Offset%20and%20Column%20within%20the%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2272031%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22lia-message-subject-wrapper%20lia-component-subject%20lia-component-message-view-widget-subject-with-options%22%3E%3CDIV%20class%3D%22MessageSubject%22%3E%3CSTRONG%3E%3CSPAN%20class%3D%22%22%3EI've%20an%20%26nbsp%3B.xlsx%20sheet%2C%26nbsp%3Busing%20MAC%2C%20where%20it's%20showed%20the%20error%20%22%23VALUE%22%20in%20the%20cells%20where%20there's%20this%20function%2C%20es%3A%20%3D(OFFSET('Dettaglio%20Costi'!E27%3B%3BCOLUMN(A4)*3-3%3B%3B))*-1.%20I%20noted%20that%20if%20I%20reloaded%20the%20formula%20all%20return%20to%20normality%2C%20with%20the%20correct%20value%20of%20the%20formula.%20the%20problem%20is%20relative%20only%20for%20that%20cells%20with%20%26nbsp%3BCOLUMN%20formula.%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2272031%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor
I've an  .xlsx sheet, using MAC, where it's showed the error "#VALUE" in the cells where there's this function, es: =(OFFSET('Dettaglio Costi'!E27;;COLUMN(A4)*3-3;;))*-1. I noted that if I reloaded the formula all return to normality, with the correct value of the formula. the problem is relative only for that cells with  COLUMN formula.
3 Replies
best response confirmed by Gianluca_mario (New Contributor)
Solution

@Gianluca_mario On a Mac myself, but can't replicate your problem. The formula you mentioned in your text look a bit odd though with double semi-colons, but perhaps that happened during pasting/typing. If I correct the formula for that and use comma's in stead (that's how my system is set-up), it works fine on my end, although I can't check what the formula should return in your case. Can you upload a file that demonstrates the issue, without disclosing any confidential information?

Thank you so much! it works!