Forum Discussion
Solved
Calculated Cell and Manual Entry Override
A newbie asks how to programmatically allow a manual entry as an override in a cell that otherwise contains a XLOOKUP formula that pulls an item cost from another worksheet?
Thanks!
If the worksheet has not been protected, the user can simply enter a value in a cell with a formula. It will overwrite the formula. Once other changes have been applied, the formula cannot be recovered except by entering it again.
I would prefer to use a separate cell for the manual override:
Column B contains XLOOKUP formulas to look up the price for each product in a list on another sheet.
The user can enter a price in column C. If so, that price is used, otherwise the lookup price is used.
The formula in D2 is =IF(C2="",B2,C2)
4 Replies
If the worksheet has not been protected, the user can simply enter a value in a cell with a formula. It will overwrite the formula. Once other changes have been applied, the formula cannot be recovered except by entering it again.
I would prefer to use a separate cell for the manual override:
Column B contains XLOOKUP formulas to look up the price for each product in a list on another sheet.
The user can enter a price in column C. If so, that price is used, otherwise the lookup price is used.
The formula in D2 is =IF(C2="",B2,C2)
- Excellent Hans! Your suggested approach will be used.
The goal is to use data from an imported workbook populating a purchase order table with a cost column each item calculated as folllows.
This data in this cost column will refer to two other worksheets: an item cost data worksheet and a calculations worksheet. The calculations worksheet may include the XLOOKUP and suggested manual entry column.
The ultimate goal is to use either the XLOOKUP result (or the manual entry) to calculate the item cost and display it in the purchase order's item table.
THE BIG QUESTION and MY NEWBIE STICKING POINT
How involved can an embedded calculation be as far as XLOOKUP criteria or "series" of XLOOKUPs(?) and/or IF statements to select items and costs?
REASON
The source workbook and number of items change per import,
I would like to perform most of the calculations in the calculations worksheet.
<Whew!>
Thanks,
ClintFormulas in Excel can become very complicated - you can nest one function in another function.
Recent versions of Excel have some tools to make formulas more readable:
The LET Function makes it possible to assign names to parts of a formula.
The more advanced LAMBDA function makes it possible to create your own custom functions that can be reused in multiple formulas.
