Log In
Register
Home
Active Member
Excel Analysis Toolpak
February 13, 2018
Offline
I am using this formula =INDEX('OEE Report'!$C:$C,MATCH($A4,'OEE Report'!$B:$B,0)) to see which part numbers are running on a specific molder. I want to ensure that I am seeing the most recent part number based on yesterday's date.
The formula is in the Master Downtime Analysis 2018 worksheet in Column B - Part Numbers. I was trying to insert an IF statement to only show part numbers on each molder from yesterday's date in Cells D1 and D2. Not having much success, so any help would really be appreciated. I have tried several iterations of the formula with an IF Statement but now I'm lost.
=INDEX(('OEE Report'!$C:$C,MATCH(IF($A4="","",$A4,'OEE Report'!$A:$A,">=D$1,'OEE Report'!$A:$A,<=$D$2,($A4,'OEE Report'!$B:$B,0))
Any help would be truly appreciated.
VIP
Trusted Members
June 25, 2016
Offline
Hi Chris
What is it that is not working? Can you give an example of which cell is not correct and what is the expected value?
I noted that you are using TODAY()-1 to get yesterday's date.
If you open your file today (16/04/2018), yesterday's date will be 15/04/2018.
Your formula win not be able to extract any data as your data's last date is only up till 13/04/2018.
Could this be the problem?
Sunny
Active Member
Excel Analysis Toolpak
February 13, 2018
Offline
HI SunnyKow,
I appreciate your valuable feedback. Yes, the OEE report table is updated on a daily basis with data from yesterday production runs, so the formulae work as soon as the data is entered and I refresh the pivot tables for yesterday, past 7 days and overall totals.
I was just trying to amend the part number formula to make sure that molders running specific part numbers were being updated if any changes occurred during the previous 24 hours when yesterdays data is added to the OEE report worksheet.
VIP
Trusted Members
June 25, 2016
Offline
Hi Chris
Give this a try. The formula is in column F. It will extract the part number based on the Molder (column A) and yesterday's date (cell D1)
It is an array formula so you will need to press CTRL+SHIFT+ENTER instead of a normal ENTER to complete the entry.
The formulas are currently showing errors but once you have the correct data, it will show the part number.
Hope this helps.
Sunny
Active Member
Excel Analysis Toolpak
February 13, 2018
Offline
SunnyKow,
Thank you so much for your help. The array formula works perfectly. I am able to now see the latest product number for each molder. I really appreciate you taking the time to help me with this issue.
Chris
1 Guest(s)
