Formula error/ attempting to update another workbook

B

bsullins

I have a formula that is somewhat large, i copied it into a new book to
reduce the size of the original book. after making sure it is not
linking to the old book it was in, i paste the formula into a new cell
and get a file not found error. here it is....

=SUBSTITUTE(IF($A2="","",(INT(IF(ISERROR(MID(VLOOKUP($A2,FMLA_VAC!$A$2:$V$1588,19,0),3,2)+(RIGHT(VLOOKUP($A2,FMLA_VAC!$A$2:$V$1588,19,0),2)/60)),0,(MID(VLOOKUP($A2,FMLA_VAC!$A$2:$V$1588,19,0),3,2)+(RIGHT(VLOOKUP($A2,FMLA_VAC!$A$2:$V$1588,19,0),2)/60)))+IF(ISERROR(MID(VLOOKUP($A2,UT_VAC!$A$2:$V$1588,19,0),3,2)+(RIGHT(VLOOKUP($A2,UT_VAC!$A$2:$V$1588,19,0),2)/60)),0,(MID(VLOOKUP($A2,UT_VAC!$A$2:$V$1588,19,0),3,2)+(RIGHT(VLOOKUP($A2,UT_VAC!$A$2:$V$1588,19,0),2)/60))))+ROUND(((MOD(IF(ISERROR(MID(VLOOKUP($A2,FMLA_VAC!$A$2:$V$1588,19,0),3,2)+(RIGHT(VLOOKUP($A2,FMLA_VAC!$A$2:$V$1588,19,0),2)/60)),0,(MID(VLOOKUP($A2,FMLA_VAC!$A$2:$V$1588,19,0),3,2)+(RIGHT(VLOOKUP($A2,FMLA_VAC!$A$2:$V$1588,19,0),2)/60)))+IF(ISERROR(MID(VLOOKUP($A2,UT_VAC!$A$2:$V$1588,19,0),3,2)+(RIGHT(VLOOKUP($A2,UT_VAC!$A$2:$V$1588,19,0),2)/60)),0,(MID(VLOOKUP($A2,UT_VAC!$A$2:$V$1588,19,0),3,2)+(RIGHT(VLOOKUP($A2,UT_VAC!$A$2:$V$1588,19,0),2)/60))),1)/100)*60),2))),".",":")

it apparently is looking for a file named UT_VAC.xls
while UT_VAC is a sheet in the book
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Vlook-up in report with column Expansion 5
IF ERROR FORMULA 2
Index / Hlookup 1
Creating copy of sheet into new file results in #REF errors 1
Want To Shrink Formula 1
nest functions in CELL function 1
indirect w/ vlookup = #REF error 1
Transfer Text 9

Top