Forum Discussion
EXCEL ASSIS
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.
- https://1drv.ms/x/s!AmU4zfB4So9SiIwe5kSJkcyDJcZLTg?e=FTbk5O
Thank you for your advice, I am completely new on this,!! the worksheet doesn't have any pswd, there is not lock the workbook!! thank youOK, I've modified the Tracker sheet so the dates at the top are no longer in the (unnecessarily) merged cells.
And the INDEX(MATCH formula in column E of Order Setup is working. This is what the formula looks like om the top row.
=INDEX(Tracker,MATCH('Order Setup'!D6,Tracker[Product Name],0),MATCH('Order Setup'!$C$2,Tracker!$C$1:$AJ$1,0))You can see how very much shorter it is than the one you started with. And therefore easier to understand and maintain. I do commend to you, for your own understanding of those functions, that you refer to the links I posted last time.
If there are more areas in that sheet needing revision, let me know.
- I hope this message finds you well. I would like to offer my sincere apologies for the delay in responding to your previous email. I recently experienced a family emergency, which required my immediate attention and time away from work.
I am now back in the office and will promptly review the matters you have brought to my attention. Should I have any questions or require further clarification, I will not hesitate to reach out to you.
Thank you for your understanding and patience during this time.
Warm regards,