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.
HansVogelaar , thank you!
Please accept my apologies for this serious error. I meant case two:
Two worksheets (tabs) in separate Excel workbooks (files)?
Since all users have Microsoft 365, XLOOKUP will be studied and utilized.
C.
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.
- Post Testing Comment: (a beginning Excel customizer in FUNCTION use but learning quickly)
GOAL:
To maintain cost data in a single SOURCE workbook. DESTINATION workbook templates will reference the unique item code and associated cost data for each item.
Currently, the user must edit cost data line by line that is now stored in each of a dozen templates.
Question: Is there a best method to link data between two workbooks in two separate folders?
Attempt One - XLOOKUP between TWO WORKBOOKS:
XLOOKUP appears to require that the SOURCE WORKBOOK be open - undesired result.
Attempt Two Plan -
XLOOKUP to refer to the DESTINATION workbook and set up a relationship between the CALCULATIONS worksheet's values and use those values in the PO worksheet.
SOURCE workbook is located in another folder and includes two worksheets with tables of cost data. Two columns will be referenced from this SOURCE workbook:
Column 1 - Item code (unique)
Column 2 - Item cost
OUTPUT
DESTINATION workbook includes a 3-page worksheet. One area of the DESTINATION workbook contains an area where data from the customer workbook's fields are PASTED into a preformatted area of the DESTINATION workbook that contains, among others, an item code and a calculated item cost field. To the right of this sections PRINT AREA are internally-stored calculations and cost data fields. This DESTINATION PDF file is then sent to the customer as a purchase order form for his signature/approval.
Since the DESTINATION workbook quantities, item types, and quantities vary for each job, I am adding a separate CALCULATIONS worksheet.
An item code and item cost column will be stationary.
On the CALCUATIONS worksheet, a MIXED REFERENCE (where COLUMNS are ABSOLUTE and ROWS are RELATIVE ) seems appropriate.
In the DESTINATION workbook CALCULATIONS worksheet:
1.) Item cost cells will be linked to specific cells/tables in the the SOURCE workbook.
2.) The SOURCE workbook item cost data will be used in calculations.
Attempt Two Plan - XLOOKUP WITHIN DESTINATION WORKBOOK
I will setup XLOOKUP to read values from the internal CALCULATIONS worksheet and insert them into the purchase order items section based on the item code.
Any input related to LINKS between workbooks and/or worksheet-based XLOOKUP is appreciated!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
- Using your suggestion will be a game-changer, streamlining purchase order processing as I begin my career at a dynamic startup company. Many thanks!