How to avoid #value when some files don't exist yet?

[nmgmarques]

Hi all.

I have the following formula in a sheet I update each month:

=IFS(TEXT(NUMBERVALUE(P2);"00")="01";'K:\MD\_data\Fim do Mês\Data\2019\2019-01\[Orders On Hand 2019-01.XLSX]Sheet1'!$N$2/1000;TEXT(NUMBERVALUE(P2);"00")="02";'[Orders On Hand 2019-02.XLSX]Sheet1'!$N$2/1000;TEXT(NUMBERVALUE(P2);"00")="03";'K:\MD\_data\Fim do Mês\Data\2019\2019-03\[Orders On Hand 2019-03.XLSX]Sheet1'!$N$2/1000;TEXT(NUMBERVALUE(P2);"00")="04";'K:\MD\_data\Fim do Mês\Data\2019\2019-04\[Orders On Hand 2019-04.XLSX]Sheet1'!$N$2/1000;TEXT(NUMBERVALUE(P2);"00")="05";'K:\MD\_data\Fim do Mês\Data\2019\2019-05\[Orders On Hand 2019-05.XLSX]Sheet1'!$N$2/1000;TEXT(NUMBERVALUE(P2);"00")="06";'K:\MD\_data\Fim do Mês\Data\2019\2019-06\[Orders On Hand 2019-06.XLSX]Sheet1'!$N$2/1000;TEXT(NUMBERVALUE(P2);"00")="07";'K:\MD\_data\Fim do Mês\Data\2019\2019-07\[Orders On Hand 2019-07.XLSX]Sheet1'!$N$2/1000;TEXT(NUMBERVALUE(P2);"00")="08";'K:\MD\_data\Fim do Mês\Data\2019\2019-08\[Orders On Hand 2019-08.XLSX]Sheet1'!$N$2/1000;TEXT(NUMBERVALUE(P2);"00")="09";'K:\MD\_data\Fim do Mês\Data\2019\2019-09\[Orders On Hand 2019-09.XLSX]Sheet1'!$N$2/1000;TEXT(NUMBERVALUE(P2);"00")="10";'K:\MD\_data\Fim do Mês\Data\2019\2019-10\[Orders On Hand 2019-10.XLSX]Sheet1'!$N$2/1000;TEXT(NUMBERVALUE(P2);"00")="11";'K:\MD\_data\Fim do Mês\Data\2019\2019-11\[Orders On Hand 2019-11.XLSX]Sheet1'!$N$2/1000;TEXT(NUMBERVALUE(P2);"00")="12";'K:\MD\_data\Fim do Mês\Data\2019\2019-12\[Orders On Hand 2019-12.XLSX]Sheet1'!$N$2/1000)

This code looks at the number value in P2 where i input the month number (usually previous month). Based on this cell, it should then open specific files from a specific path. Basically, the path name and file name are always the same, save for the month changing from -01 to -02, -03 and so on.

The problem is, these files get created as the year moves on, so naturally they don't exist yet. This results in Excel asking me to point to the files when I try to update the cells, and even after canceling out of all the non existent ones (currently -03 to -12), it returns the #value result.

Any suggestions on how to handle this?

 

[sandy666]

maybe IFERROR() ?

 

[nmgmarques]

maybe IFERROR() ?

You mean instead of IFS?

 

[sandy666]

no, wrap whole formula with IFERROR()

=IFERROR(your_formula,"")

or use PowerQuery (From Folder) and if any new file will appear in this forlder it will get data from the new file

 

[sandy666]

or try (not tested)

=CHOOSE(MATCH(TEXT(NUMBERVALUE(P2);"00");{"01";"02";"03";"04";"05";"06";"07";"08";"09";"10";"11";"12"};0);
IFERROR('K:\MD\_data\Fim do Mês\Data\2019\2019-01\[Orders On Hand 2019-01.XLSX]Sheet1'!$N$2/1000;"");
IFERROR('[Orders On Hand 2019-02.XLSX]Sheet1'!$N$2/1000;"");
IFERROR('K:\MD\_data\Fim do Mês\Data\2019\2019-03\[Orders On Hand 2019-03.XLSX]Sheet1'!$N$2/1000;"");
IFERROR('K:\MD\_data\Fim do Mês\Data\2019\2019-04\[Orders On Hand 2019-04.XLSX]Sheet1'!$N$2/1000;"");
IFERROR('K:\MD\_data\Fim do Mês\Data\2019\2019-05\[Orders On Hand 2019-05.XLSX]Sheet1'!$N$2/1000;"");
IFERROR('K:\MD\_data\Fim do Mês\Data\2019\2019-06\[Orders On Hand 2019-06.XLSX]Sheet1'!$N$2/1000;"");
IFERROR('K:\MD\_data\Fim do Mês\Data\2019\2019-07\[Orders On Hand 2019-07.XLSX]Sheet1'!$N$2/1000;"");
IFERROR('K:\MD\_data\Fim do Mês\Data\2019\2019-08\[Orders On Hand 2019-08.XLSX]Sheet1'!$N$2/1000;"");
IFERROR('K:\MD\_data\Fim do Mês\Data\2019\2019-09\[Orders On Hand 2019-09.XLSX]Sheet1'!$N$2/1000;"");
IFERROR('K:\MD\_data\Fim do Mês\Data\2019\2019-10\[Orders On Hand 2019-10.XLSX]Sheet1'!$N$2/1000;"");
IFERROR('K:\MD\_data\Fim do Mês\Data\2019\2019-11\[Orders On Hand 2019-11.XLSX]Sheet1'!$N$2/1000;"");
IFERROR('K:\MD\_data\Fim do Mês\Data\2019\2019-12\[Orders On Hand 2019-12.XLSX]Sheet1'!$N$2/1000;""))

 

[Yongle]

Excel is a bit stubborn with broken links (even intentional ones!) and tries to be helpful by pointing you to a folder
One way to avoid this problem is to have no broken links in the worksheet itself

One option would be to put the formula in the cell when you press a button or when value in P2 changes
The formula would be driven by the value of P2
You could also add a test to see if the file exists BEFORE putting the formula in cell A1 (in example below)

Dim f As String
f  = "='K:\MD\_data\Fim do Mês\Data\2019\2019-XXXX\[Orders On Hand  2019-XXXX.XLSX]Sheet1'!$N$2/1000"
Range("A1").Formula = Replace(f, "XXXX", Format(Range("$P$2"), "00"))