Forum Discussion
Solved
New User - Referencing Calculated Data from another Worksheet via Part IDs
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?
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.
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)?
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.
