Forum Discussion
New User - Referencing Calculated Data from another Worksheet via Part IDs
No problem! Just wanted to get things clear.
It will be easiest if both workbooks are in the same folder, but it is not essential.
Before you create the XLOOKUP formulas, open both workbooks in Excel. You can then start creating the formula in the destination workbook, and switch to the source workbook and use the mouse to select the relevant ranges in that workbook.
When you are done, first save and close the source workbook. Excel will automatically add its folder path in the XLOOKUP formulas. Then save the destination workbook.
XLOOKUP does work between two different workbooks, even if the workbook containing the lookup_array and return_array is closed. But the formula must specify the full path of those ranges.
The easiest way to do this is as follows:
- Open both workbooks in Excel.
- Click in the destination cell.
- Type =XLOOKUP(
- Point to the cell with the lookup_value.
- Type a comma.
- Switch to the source workbook and point to the lookup_array range.
- Type a comma.
- Point to the return_array range.
- Type ,"")
- Press Enter.
- Copy or fill down as needed.
- Switch to the source workbook.
- Close the source workbook. Save it if it has been modified.
- Excel will automatically add the full path of the source workbook to the XLOOKUP formula in the destination workbook.
- Save the destination workbook.
About the following line in your latest reply:
HansVogelaar stated: "Excel will automatically add the full path of the source workbook to the XLOOKUP formula in the destination workbook."
Assumption: In the following two questions, the SOURCE workbook filename will not change. The folder name containing the SOURCE workbook will change.
- What changes or precautions, if any, are required when moving the files from the Test folder structure to the Production folder structure?
- Will the XLOOKUP functionality be preserved when copies of the DESTINATION workbook (template) are distributed to individual customer subfolders?
Otherwise, my plans are to go through your latest XLOOKUP steps within this week.
Thanks for your patience and sharing your expertise here!
Clint
If you want to change the path of the source workbook, you have two options:
- Use Replace (Ctrl+H) to replace the 'old' path with the 'new' path throughout the destination workbook, or
- Open both the source workbook and the destination workbook.
- Activate the source workbook.
- Use Save As to save it to the new path.
- Close the source workbook.
- Save the destination workbook.
If all users can access the folder containing the source workbook, you should be able to distribute the destination workbook without problems.
It never hurts to test thoroughly, of course.
- Your suggestions and tips provide much-need piece of mind. User access shouldn't be an issue. Setup and testing commences over the next few days!