mercredi 28 février 2018

Excel nested if statements - need help troubleshooting

I need some fresh eyes. I have been working on this incrementally and go from having it work to broken. At this point my eyes are crossing and I could use some help. Column H in this spreadsheet contains a machine id and column I is a date. I want it to display nothing if both H and I are blank (This is the point where I broke it most recently and decided to ask for help. This logic is not include.) If either H or I but not both have a value, it will display "NO". If both H and I have values, it will call a custom function that will create the directory if it does not already exist. Additionally, I want to display "YES" if the directory is created or exists. All of the functionality was working before I tried to display nothing if both H and I were empty.

This is the formula I am working with:

=IF(COUNTA(H21:I21)<>COLUMNS(H21:I21), "NO",IF(CREATEDIR(CONCATENATE(TEXT(I21,"yyyy"),"\",TEXT(I21,"m-d-yy"),"\",H21))=0,"YES", "NO"))

And this is the VBA function I am using(path details omitted)

Function CREATEDIR(dateId) If Len(Dir("Z:\pathname\" & dateId, vbDirectory)) = 0 Then MkDir "Z:\pathname\" & dateId End If End Function:

Aucun commentaire:

Enregistrer un commentaire