Help with formula to pick up a file path to a workbook from a cell in the same worksheet

Hi everyone,

I have a bit of a problem. I am trying to make a series of workbooks which contain different stages of a fairly complicated report. There are over 60 reports in total so having multiple workbooks is the only way to separate it to a level that can be usable by anyone.

What I am trying to do is get my Index function to work across workbooks. I can link the workbooks manually but this is time consuming and is prone to mistakes and more problems.

This is my Formula atm with some test names and such put in just for examples:

{=INDEX('C:\Users\Ian Stubbs\Documents\Jason Work Experience\[1392_39_JS02216S1R.xlsx]Sheet1'!$C$1:$C$2500,MATCH(1,($A6='C:\Users\Ian Stubbs\Documents\Jason Work Experience\[1392_39_JS02216S1R.xlsx]Sheet1'!$B$1:$B$2500)*($E6='C:\Users\Ian Stubbs\Documents\Jason Work Experience\[1392_39_JS02216S1R.xlsx]Sheet1'!$E$1:$E$2500),0))}

The formula works absolutely fine but has to be manually linked for each new report as they all have different numbers for the file name. I am wondering if it is at all possible to replace the filepath links in this formula with a cell in the same worksheet. And then within that cell it contains the filepath to the required file. However every time I have attempted to do this it simply won't load the filepath properly and returns an error.

Can anyone help at all?

The function you need is INDIRECT. But... it only works on OPEN workbooks, a little quirk of Excel. If you can download and use 3rd party software, Morefunc (google it - it's free) has a neat function (INDIRECT.EXT) which will allow you to reference a filename from a cell and return a value from a cell in that worksheet, even if it's closed.

can you attach a mock-up in an Excel sheet or two to show us exactly what you need, just in case I've misunderstood you? Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

May be you can make list in what folder your file and used t on formula so you no need type too long on every formula?

Hi, thanks for the quick response.

I think I have seen the INDIRECT Formula before but as it could only be used on open workbooks was never really of any use.

I have attached an example sheet though the actual sheet is considerably larger than this. The data sheet is actually in a completely different workbook from the filtered report. The Data sheet contains lots of lines with data which is of no use but in the same format. This is why I am using the Index function to identify the data that I need.

However because each line of reported data includes the hole title in 2 places the index function required a second parametre to identify the correct line to pull the information from.This was the Axis which is nicely always in the same Column.

All Files are kept in the same Folder but each File has a different name.Example Data Report.xlsx

Yup. sorry... INDIRECT.EXT is what you need. Its syntax makes it a bit clunky to set up, but it does work a treat.

You can find the Morefunc.xll add-in here:

http://www.ashishmathur.com/tag/morefunc/

I use it in 32bit Excel 2002 and 2007. I don't think it works in 64bit Excel.

I have it working in Excel 2010, but you need t move some files around. Ashish Mathur put up an article about it
http://www.ashishmathur.com/extract-...d-excel-files/

i used the same workaround on Excel 2013 on a very puny Asus notebook, but I haven't had any need to use it yet...

I'll try to use Morefunc.xll, but when the file open on other computer the function not work, I think it need to be install on that computer too.

So make sure about that if you want to use Morefunc.xll and share with others.

You're not using INDIRECT.EXT correctly. Unfortuinately, I'll be away for about 2-3 hours. if no-one has replied by then - i'll sort it out for you.

Drag these two onto the desktop. Open target. Adjust the formula in C2 in two places to reflect YOUR situation on your PC in respect of the bit in bold in E-G. Put any number between 1 and 5 into B2. Watch. then open Fred.xlsx to check. Adapt the syntax to your EXACT requirements.

Any problems, shout...