A Problem with a formula Excel

[Szwadron6]

Hi all,

I would like to ask you if you have a clue what might be the problem with the formula in Column G in the attached excel file.

I created a formula in Excel 2016 which works just like it supposed to.

But when I open this file in Excel 2013 (on two different computers) the formula works as long as I do not change a value in any cell in Column A then I got error #ARG!

I do not even have to change any value. It shows me that error when I only enter a cell and press Enter without even changing any value.

Is that a problem with Excel version? I did not use any function that was not available in previous Office version.

I will appreciate any advice on that problem. Thank you in advance!

[FDibbins]

Unable to open your file (Im at work), can you show the formula you are using?
It may be that the formula is not available in earlier excel versions

[MarvinP]

Hey Ford.
On his Warehouse sheet he has this monster in CSE in E2.

=IF(F2="Błędny format daty","Błędny format daty",IF(F2="","",IF(ROUND(MAX((All!$B$3:$B$1048576=Warehouse!A2)*(All!$G$3:$G$1048576)),0)=0,"Brak indeksu w arkuszu całość",IF(LEN(IF(LEN(F2)=7,VLOOKUP(LEFT(F2,2),'Raw data'!$F$2:$G$13,2,FALSE)&"."&F2,IF(LEN(F2)=10,F2,IF(LEN(F2)=5,VLOOKUP(LEFT(F2,2),'Raw data'!$F$2:$G$13,2,FALSE)&"."&F2,IF(LEN(F2)=9,LEFT(F2,5)&"."&RIGHT(F2,4),IF(LEN(F2)=6,LEFT(F2,2)&"."&MID(F2,3,2)&"."&MID(F2,5,4)))))))=8,LEFT(IF(LEN(F2)=7,VLOOKUP(LEFT(F2,2),'Raw data'!$F$2:$G$13,2,FALSE)&"."&F2,IF(LEN(F2)=10,F2,IF(LEN(F2)=5,VLOOKUP(LEFT(F2,2),'Raw data'!$F$2:$G$13,2,FALSE)&"."&F2,IF(LEN(F2)=9,LEFT(F2,5)&"."&RIGHT(F2,4),IF(LEN(F2)=6,LEFT(F2,2)&"."&MID(F2,3,2)&"."&MID(F2,5,4)))))),6)&"20"&RIGHT(IF(LEN(F2)=7,VLOOKUP(LEFT(F2,2),'Raw data'!$F$2:$G$13,2,FALSE)&"."&F2,IF(LEN(F2)=10,F2,IF(LEN(F2)=5,VLOOKUP(LEFT(F2,2),'Raw data'!$F$2:$G$13,2,FALSE)&"."&F2,IF(LEN(F2)=9,LEFT(F2,5)&"."&RIGHT(F2,4),IF(LEN(F2)=6,LEFT(F2,2)&"."&MID(F2,3,2)&"."&MID(F2,5,4)))))),2),IF(LEN(F2)=7,VLOOKUP(LEFT(F2,2),'Raw data'!$F$2:$G$13,2,FALSE)&"."&F2,IF(LEN(F2)=10,F2,IF(LEN(F2)=5,VLOOKUP(LEFT(F2,2),'Raw data'!$F$2:$G$13,2,FALSE)&"."&F2,IF(LEN(F2)=9,LEFT(F2,5)&"."&RIGHT(F2,4),IF(LEN(F2)=6,LEFT(F2,2)&"."&MID(F2,3,2)&"."&MID(F2,5,4)))))))-ROUND(MAX((All!$B$3:$B$7549=Warehouse!A2)*(All!$G$3:$G$7549)),0))))

I'm not putting it in Formula brackets as it might be easier to read like the above.

[FDibbins]

Thanks Marvin

I may have segmented that moster incorrectly, but it seems to me that you have some duplicate testing there.
IF(LEN(F2)=7,VLOOKUP(LEFT(F2,2),'Raw data'!$F$2:$G$13,2,FALSE)&"."&F2,IF(LEN(F2)=10,F2,
IF(LEN(F2)=5,VLOOKUP(LEFT(F2,2),'Raw data'!$F$2:$G$13,2,FALSE)&"."&F2,IF(LEN(F2)=9,LEFT(F2,5)&"."&RIGHT(F2,4),IF(LEN(F2)=6,LEFT(F2,2)&"."&MID(F2,3,2)&"."&MID(F2,5,4)))))))=8,LEFT(
IF(LEN(F2)=7,VLOOKUP(LEFT(F2,2),'Raw data'!$F$2:$G$13,2,FALSE)&"."&F2,IF(LEN(F2)=10,F2,
IF(LEN(F2)=5,VLOOKUP(LEFT(F2,2),'Raw data'!$F$2:$G$13,2,FALSE)&"."&F2,IF(LEN(F2)=9,LEFT(F2,5)&"."&RIGHT(F2,4),IF(LEN(F2)=6,LEFT(F2,2)&"."&MID(F2,3,2)&"."&MID(F2,5,4)))))),6)&"20"&RIGHT(
IF(LEN(F2)=7,VLOOKUP(LEFT(F2,2),'Raw data'!$F$2:$G$13,2,FALSE)&"."&F2,IF(LEN(F2)=10,F2,
IF(LEN(F2)=5,VLOOKUP(LEFT(F2,2),'Raw data'!$F$2:$G$13,2,FALSE)&"."&F2,IF(LEN(F2)=9,LEFT(F2,5)&"."&RIGHT(F2,4),IF(LEN(F2)=6,LEFT(F2,2)&"."&MID(F2,3,2)&"."&MID(F2,5,4)))))),2),
IF(LEN(F2)=7,VLOOKUP(LEFT(F2,2),'Raw data'!$F$2:$G$13,2,FALSE)&"."&F2,IF(LEN(F2)=10,F2,
IF(LEN(F2)=5,VLOOKUP(LEFT(F2,2),'Raw data'!$F$2:$G$13,2,FALSE)&"."&F2,IF(LEN(F2)=9,LEFT(F2,5)&"."&RIGHT(F2,4),IF(LEN(F2)=6,LEFT(F2,2)&"."&MID(F2,3,2)&"."&MID(F2,5,4)))))))-ROUND(MAX((All!$B$3:$B$7549=Warehouse!A2)*(All!$G$3:$G$7549)),0))))

Also, based on what I can tell, it may simplify things of you used some helper cells. It looks like you reference F2 a lot, with all sorts of extracts and tests on it. Maybe if you used a helper and ID'd the LEN, and did a lot of the LEFT/MID/RIGHT in another helper, then just referenced those helpers?

[Szwadron6]

Hi guys,

thank you for the tips. I found the problem and it was a quite simple one - the date was in a different format since I opened the file in Windows 7 and created the formula in Windows 10.

Nevertheless thanks for the support.

[FDibbins]

Thanks for the feedback, Im happy you got this resolved