Forum Discussion
EXCEL ASSIS
I hope this message finds you well. When selecting a date, the system should display the available products in our inventory for that specific day. The order quantities will be entered manually, The system will then calculate the maximum value based on the order price tracker and determine the total costs by multiplying the order quantity with the unit costs.
Your assistance in this matter is greatly appreciated. Thank you for your cooperation and support.
Best regards,
Jeng Chi Order Guide 2024 - Copy.xlsm
8 Replies
First of all, that deeply nested IFERROR(IF.... formula is a nightmare waiting to happen. I hope that's why you've come here. Yesterday I came across this "Rule of Thumb" for formulas in a blog by one of the true experts in this forum. I saved it for use at times like this:
Second, what's the relationship between the date in cell $C$2 and the available inventory? I ask because there are no dates (or no dates visible) in the data table itself, so no dates to compare with $C$2. Perhaps it's just the OnHand column, but if that's the case, you don't really need a date as reference in the formula.
Third, rather than an image, would you be so kind as to post an actual copy of the spreadsheet itself. Images don't lend themselves to formula creation.
If you use the "Open Full Text Editor" feature, you should see an area for dragging and dropping a file. If that doesn't work for you, post it on OneDrive or GoogleDrive and paste a link here that grants access.
https://1drv.ms/x/s!AmU4zfB4So9SiIwe5kSJkcyDJcZLTg?e=gF6uQN
In ORDER setUP, will help me because is when I will place the order with my vendor, Order ( i will add manually how many items do I need for every item. and the OH will show me how many I have for this specific day. thank you
Thanks for making that available. I've looked at it. My initial observation is this: Instead of that many-layers-deep IFERROR(IF formula, you should be able to use a relatively simple combination of INDEX and MATCH, but I find myself stymied in creating the formula.
Some of that difficulty I would trace to another design error in your approach here, and that is that you've spent too much energy in the TRACKER sheet--which should be a simple flat file--in making column headings look pretty by the use of color and (worst of all) merging the cells containing the dates. And it would appear that you've locked the sheet so it can't changed. Another general rule on good workbook design is to minimize graphic enhancements altogether--including use of colors and merging of cells--on the input and other back-end sheets. It's fine to prettify the output sheets where all you're doing is displaying the output, but keep the basic data used for input (and that would include your daily inventory) clean of efforts at beautification.
I'd be happy to help in the writing of an INDEX and MATCH formula, but I'd ask that you unlock the workbook first. Or, since you clearly are far more than a beginner in Excel, you can research the use of INDEX and MATCH at the links in this sentence.
I'm attaching your file to this message so others can take a look.
