New User - Referencing Calculated Data from another Worksheet via Part IDs

[Clint_E_Hill]

Two Goals:

Goal One: Streamline filling out an itemized purchase order pricing table or "Worksheet One"

Goal Two: Refer to data in a separate, single spreadsheet, or "Worksheet Two", containing a cell value representing (3) three types of pricing data including:

1.) Price per pound

2.) Price each

3.) Mark up value

Worksheet One contents:

Column A = Part ID (standard Part IDs)

Column B = Calculated Price data includes:

• Price data based on Part ID (obtained from referenced Worksheet Two) via XLOOKUP, other ???
• Internal Price data: Part quantity

Use the referenced pricing data, Worksheet Two, and internal pricing data, Worksheet One, to calculate part subtotals.

Worksheet Two contents:

Column A = Part ID

Column B = Actual price of part ID (per pricing data type shown below)

Column C = Two pricing data types: Price per pound or Price each

Column D = Mark up value (not used on all parts)

QUESTIONS:

Q1: Which Excel tools do I use in associating Column A (Part ID) with Column B (its specific price data cell) ? Example: XLOOKUP or other???

Q2: What are things to keep in mind when referencing worksheets as far as file location, etc. are concerned?

[#2]

@Clint_E_Hill 

You'd use VLOOKUP or, if all users have Microsoft 365 or Office 2021, XLOOKUP.

By "Worksheet One" and "Worksheet Two", do you mean:

1) Two worksheets (tabs) within a single Excel workbook (file), or

2) Two worksheets (tabs) in separate Excel workbooks (files)?

[#3]

@Hans Vogelaar , 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.

[#4 · Accepted solution]

@Clint_E_Hill 

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.

[#5]

Using your suggestion will be a game-changer, streamlining purchase order processing as I begin my career at a dynamic startup company. Many thanks!

[#6]

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!

[#7]

@Clint_E_Hill 

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.

[#8]

I was about to give up on multiple workbook XLOOKUP (due to my present basic skill level which is my motivation in learning much more about Excel customization!) You detailed step-by-step is needed and your valuable time is appreciated!

I will follow it and report a successful result, I am sure!

[#9]

About the following line in your latest reply:

@Hans Vogelaar 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.

1. What changes or precautions, if any, are required when moving the files from the Test folder structure to the Production folder structure? 
2. 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

[#10]

@Clint_E_Hill 

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.

[#11]

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!