Aug 07 2023 07:45 AM - edited Aug 07 2023 07:56 AM
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:
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?
Aug 07 2023 08:07 AM
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)?
Aug 07 2023 08:19 AM
@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.
Aug 07 2023 08:59 AM
SolutionNo 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.
Aug 07 2023 10:47 AM
Aug 14 2023 11:05 AM
Aug 14 2023 12:18 PM
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:
Aug 14 2023 12:42 PM
Aug 16 2023 07:40 AM - edited Aug 16 2023 07:41 AM
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.
Otherwise, my plans are to go through your latest XLOOKUP steps within this week.
Thanks for your patience and sharing your expertise here!
Clint
Aug 16 2023 08:11 AM
If you want to change the path of the source workbook, you have two options:
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.
Aug 16 2023 08:25 AM
Aug 07 2023 08:59 AM
SolutionNo 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.