Vlookup inside IF function not working

Hi, in Excel 2007 'T:\FILE\FILE\FILE' is not a valid sheet name...



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Is 'T:\FILE\FILE\FILE' the sheet name? Normally the reference would look like:
'[T:\FOLDER\FOLDER\FILE.xls]Sheetname'!$B:$E

If you replace file with your actual folder and file name, it is still not a valid reference.

For example, if I wanted to look at the file "test.xls" in the folder "myfolder" and the sheet "mysheet" I would do:
'[T:\myfolder\test.xls]mysheet'!$B:$E
not
'T:\myfolder\test.xls'$B$E

i.e. you don't have a valid sheet reference

Sorry yudlugar please see my edited post;

Here's What it looks like when both files are open at the same time (below) sorry I didn't realise I'd taken out the [] in my paste.

=IF(H9>0,(I9*H9)+((I9*H9/100)*(VLOOKUP(B9,'[FILE.xlsx]FILE'!$B:$E,4,FALSE)))+0.25,0)

This is what it looks like when the lookup file is closed.

=IF(H9>0,(I9*H9)+((I9*H9/100)*(VLOOKUP(B9,'T:\FOLDER\FOLDER\[FILE.xlsx]FILE'!$B:$E,4,FALSE)))+0.25,0)

I've checked the VLOOKUP on it's own and it works fine just not combined with the IF function.

What does the vlookup return and what is I9 and H9?

I9 is a weight, H9 is a quantity and the lookup is to add a yield% to the original quantity.

Can you describe "no longer works" ??

What exactly happens?
Does it result in an error message?
Do you get the wrong result?
What result did it actually return, and in what way is that different from the result you were expecting?

I'd suggest using the Formula Evaluation tool to watch each step as the formula is calculated.

What is the actual values? Not what they represent...

Perhaps you could upload example workbooks, it would make things a lot easier!