#value error

[Klippie]

Good day. I have 2 excel sheets where one extracts information from the other. It uses an array formula.

I have been using these 2 sheets for quite some time but for some reason, the formulas aren't working anymore. I think it is important to know that there is a formula extracting info from the same worksheet and that is still working, but I don't think it is an array formula.

So the big thing is that it is still working on someone else's computer. I had a look and we are both working with Excel 2013. I did notice now that the computer is running windows 7 and I am on windows 10. As far as I know, I have had windows 10 for quite some time so it was working while I had windows 10. I just think it is an important point to mention this. Could it have something to do with the windows versions? how do I get past this?

 

[6StringJazzer]

Well, for starters, you'll have to show us the formula.

 

[Klippie]

=IF(SUMPRODUCT(--(LEFT([DrillPenetrationWorksheet.xlsx]WorkTableForImportToDB!$R$4:$R$99933,3)=LEFT(J2379,3)),--(VALUE([DrillPenetrationWorksheet.xlsx]WorkTableForImportToDB!$B$4:$B$99933)=VALUE(B2379)),--([DrillPenetrationWorksheet.xlsx]WorkTableForImportToDB!$H$4:$H$99933=F2379),--([DrillPenetrationWorksheet.xlsx]WorkTableForImportToDB!$G$4:$G$99933=I2379),--([DrillPenetrationWorksheet.xlsx]WorkTableForImportToDB!$F$4:$F$99933=VALUE(H2379)),[DrillPenetrationWorksheet.xlsx]WorkTableForImportToDB!$J$4:$J$99933)=0,"",SUMPRODUCT(--(LEFT([DrillPenetrationWorksheet.xlsx]WorkTableForImportToDB!$R$4:$R$99933,3)=LEFT(J2379,3)),--(VALUE([DrillPenetrationWorksheet.xlsx]WorkTableForImportToDB!$B$4:$B$99933)=VALUE(B2379)),--([DrillPenetrationWorksheet.xlsx]WorkTableForImportToDB!$H$4:$H$99933=F2379),--([DrillPenetrationWorksheet.xlsx]WorkTableForImportToDB!$G$4:$G$99933=I2379),--([DrillPenetrationWorksheet.xlsx]WorkTableForImportToDB!$F$4:$F$99933=VALUE(H2379)),[DrillPenetrationWorksheet.xlsx]WorkTableForImportToDB!$J$4:$J$99933))

This is the formula. But its working on another guy's computer so doubt if its the formula.

 

[6StringJazzer]

Are you certain that both files are identical on both computers? If DrillPenetrationWorksheet.xlsx has different data on one, it cause cause an error while not doing so on the other.

It is extremely unlikely that the version of Windows is related to the error.

 

[Klippie]

The files are on a shared network so we are using the exact same file. I just cant figure out why its working on the one computer but not on another.

 

[Klippie]

So we found the problem. I changed my windows date format and that fixed the issue.

 

[6StringJazzer]

Thanks for reporting back with your solution. I guess your LEFT functions must be looking at dates and expecting them to be formatted in a specific way, which would not be evident from seeing the formula.