How to apply an IF condition when referencing another workbook and sheet

[defender]

Hi all,

i would like to know how to apply an IF condition when referencing another workbook and sheet.

so far i have:

=('[running2020.xlsx]method'!C2:50)

the cells in column C from the spreadsheet 'running' and sheet 'method' start AB.00001.20, CD.00001.20, EF.00001.20, GH.00001.20. I would like all references starting EF and GH to be extracted only.

thanks for any help on this

 

[defender]

Now if you said that earlier I could have given you the answer earlier, I can write formulas but not read minds.

=IF('[running2020.xlsx]method'!C2="","",'[running2020.xlsx]method'!C2)

apologies, and thank you for your help.

 

[defender]

apologies, and thank you for your help.

=IF(OR(C2="",D2=""),"",NETWORKDAYS(C2, D2))

I am using the above, but wish to include another condition if C2='N/A', D2='N/A

 

[jasonb75]

It's easier to use an error trap, networkdays will throw up a #VALUE! error if either cell contains N/A.

=IFERROR(IF(OR(C2="",D2=""),"",NETWORKDAYS(C2, D2)),"")

If C2 and D2 contain formulas that show "" when there is no data (the formulas from earlier in this thread for example) then you don't need to check if they're blank, the error trap will do that as well. This only works with a formula that shows "", a blank cell with no formula will need the one above.

=IFERROR(NETWORKDAYS(C2,D2),"")