How do you reference other workbooks directly in Excel?

I use Excel a lot and love it but there you go I'm pretty sad!! When I do use it I do so pretty much exclusively with one workbook and multiple sheets. Now using Excel today I wanted to use an external sheet and perform a VLOOKUP, nothing revolutionary I know.


The thing was I wanted to type my formula in by hand, and the truth was I couldn't - it was a dreadful feeling!! lol. Ok, so I used the parameter editor to get some help. you know the little fx button next to the formula bar and learnt a few interesting facts:

Ok so initially I had the spreadsheet I wanted to link to open, I clicked the tab and range for the VLOOKUP and hey presto, thanks Excel its all fine and dandy. When you look at this formula, while the linked spreadsheet is open, you will notice that the reference is along the lines of [workbook.xls]tabname!cell:range. What is interesting to note if you then close you linked spreadsheet, Excel modifies the formula to 'drive:\path\of\workbook\[workbook.xls]tabname'!cell:range - which in my mind would be what I needed to type had I manually created the formula - and my goal might I add!


I did want to take this a little further and dynamically create the path since I knew I would always have the lookup source in the same folder. So I was going to use CELL("filename") strip off the filename and concatenate this path to my workbook reference. This didn't work, so I thought I would use the INDIRECT() function but googling more other people mentioned this does not work with closed workbooks. So I failed here, and since some of the work I am doing is using Excel automation I am instead passing this full path to the .Formula property and it does then work. Apparently, and I haven't investigated this, there is an add on called something like indirect.ext which will allow indirect to work with closed workbooks... I will try to have another look soon!