Path Not Being Recognized With Variable Folders-VLOOKUP

I am trying to import data from another worksheet. I've tried the VLOOKUP with specific folders names and it worked, but now I have some variable folders names I'm entering and it won't figure out the solution. I continue to get the VALUE error, but from what I can tell, everything is in quotes the way it needs to be, and it should be good to go. I'll take all the help I can get, thanks guys!

Please Login or Register  to view this content.

You need to use the INDIRECT function to turn what is essentially a text string that represents a range reference into the range reference itself so that Excel can use it.

However, the INDIRECT function does not work with closed workbooks. The only way around that is to download and install the free add-in called morefunc (do a Google search to find sites where you can download it from). This has a function INDIRECT.EXT which does work with closed workbooks.

Hope this helps.

Pete

So does the INDIRECT.EXT otherwise work the exact same way as the INDIRECT function? I got the INDIRECT function to work by saying #=VLOOKUP($C$252,INDIRECT("'U:\Corp\Material\Paper\White\Numbers\Certs\"&$C$246&"\"&$C$247&"\models\[models_"&$C$249&" - final - corp.xlsb]"&$C$250&"'!"&"$B:$Q"),16)#. So now will I just do INDIRECT.EXT('What I have in the previous code')? Finally, could you explain to me what the INDIRECT function in doing in this instance? I got it to work, but I still don't understand what's happening in the process. Thanks!

Let's keep it simple to start with - suppose you want to get data from the same cell (eg B15) in different worksheets of the same workbook, where the sheet names are listed in column A of a Master sheet starting with A2. You can do this:

=INDIRECT("'"&A2&"'!B15")

and then copy it down. Although the phrase "'"&A2&"'!B15" will equate to something that looks like a cell reference, eg. 'January'!B15, this is just a text string, and Excel needs to convert it into the memory location represented by that string - this is what the INDIRECT function does. Notice that as B15 is stored within the text string then it does not change as the formula is copied down, so the formula will bring the contents of B15 from the sheet name given in column A.

If you want to bring data from another workbook then you can build up the string in the same way, although you need to include the filename (including the extension) surrounded by square brackets, and the apostrophes now need to encompass that path, rather than just the sheet name. The second workbook needs to be open for INDIRECT to work, and in this case you don't need to have the complete path including folders to the file (although it doesn't matter if it is included).

If you are using INDIRECT.EXT then you have to include the full path to the file, as you have shown. So, your formula will become:

=VLOOKUP($C$252,INDIRECT.EXT("'U:\Corp\Material\Paper\White\Numbers\Certs\"&$C$246&"\"&$C$247&"\models\[models_"&$C$249&" - final - corp.xlsb]"&$C$250&"'!"&"$B:$Q"),16)

Hope this helps.

Pete

Thanks for all the help! I think I've got the INDIRECT function down now.

You're welcome - glad to be able to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

There is an optional parameter that can be used with INDIRECT - the full syntax for the function is:

INDIRECT(ref_text,[type])

where type is optional and is a logical value that specifies what type of reference is contained in the cell ref_text.

If type is TRUE or omitted, ref_text is interpreted as an A1-style reference.

If type is FALSE, ref_text is interpreted as an R1C1-style reference.

You will need to add ,FALSE at the end of your INDIRECT expression in VBA because you are using R1C1 notation.

Hope this helps.

Pete

So this is my code now, and I'm getting the error "Compile error: Expected: line number or label or statement or end of statement"
Thanks for all the help by the way, it's frustrating cause I feel like I'm so close to getting this

This is what I'm trying to create in VBA

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Found a simpler way to get the job done! Thanks for all the help Pete!