References across workbooks: help with file paths & table references

Good afternoon!

I have two workbooks that I need to link: Workbook A (which is the main workbook), and Workbook B. Workbook B has four huge tables that I only reference some of the time, and I decided it would be best to move these tables out of the main workbook to speed things up. I am having trouble in two areas:

First, I want to cross reference the tables in B using INDEX/MATCH, but I am unsure what the syntax or verbiage is for referencing the whole table (not just a colum). I want this formula to be dynamic (hence referencing the table instead of the cell ranges), so that when B needs to be updated, it won't affect the formulas in A.

Second, when the main workbook is done it will be used by several other people on their laptops. I need a generic way to list the file path of Workbook B so that it can be used by anyone on any computer. Communication between these workbooks is one-way only, and the two workbooks will be saved together, so I was thinking of having the file path for Workbook B reference the file path for Workbook A. Is this possible?

I hope I have been clear enough, but if clarification is needed just ask!

Thank you!

Hello! I'm sorry for the delay in posting. I had a long weekend away from work (and it was wonderful). Posting sample books is a problem. I have two computers at work that are independent of each other. The computer with my project has Excel 2007, but does not have access to the internet. This computer, which does have access to the internet, has Excel 2003. The problem is that 2003 does not have the same table features as 2007, and so I can't make an accurate sample book to upload. I tried to frame my query as generic as possible so that a sample book would not be needed.

Is there anyway you can tell me how to reference a full table in another workbook without a sample book? Or if there is a way to create a generic filepath that will be compatible with any computer?

Originally Posted by noboffinme

Hi NamiSama,

I'd suggest a 'Named Range' that uses the OFFSET function to dynamically change the range to any new data in your tables.

A generic filepath for other users can be done using a 'UNC' path.

One way to create the UNC path is to Open Outlook >> check the format text is set to 'Rich Format' >> go to Insert File/Attachment & browse to the workbook on your Network Drive >> Insert the file as a Hyperlink.

Once you have the Hyperlink in your e-mail body, right click & select 'Edit Hyperlink' >> now copy the link you find in the 'Address' field - This is an 'UNC' path & any users can access the file as it's not mapped as any drive letter etc etc.

Let me know how you go.

I will consider your advice one I have a working formula. This problem is two-fold, and first I need a way to match my criteria. I uploaded a sample book with several examples, and the formula I was planning on using. Do you have any insight into how I can make that part of this problem work?

Originally Posted by noboffinme

Hi, I can't see any attachment?

My apologies, it was a very busy weekend. This thread seemed to be going nowhere, so I started a new thread with more emphasis on the partial text match. I totally forgot to link the threads (just in case). Here is the new thread:

http://www.excelforum.com/excel-form...variables.html