1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

INDEX & MATCH From Two Worksheets

Discussion in 'Ask an Excel Question' started by Chris Turner, Apr 14, 2018.

  1. Chris Turner

    Chris Turner New Member

    Messages:
    19
    I have a trying to create a formula using the INDEX and MATCH functions to do a daily update of a worksheet.

    I want the formula to INDEX the most recent Part Numbers in Column C being run on a specific Molder in Column B in the OEE Report worksheet and to MATCH them with the Molders in Column A of the Master Downtime Analysis 2018 worksheet. I keep getting a #NAME? response.

    I have tried several variations of this formula but not success so far:

    =INDEX(OEE Report!$C:$C,MATCH($A4,OEE Report!$B:$B,0))

    I was just trying to get the part numbers to load into the Master Downtime Analysis 2018 worksheet before I attempted to get the formula to insert the Part Numbers for Yesterdays date in Cells D1:D2

    Appreciate any help with this.

    Attached Files:

  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,615
    Hi ,

    I assume this is the formula you have tried :

    =INDEX(OEE Report!$C:$C,MATCH($A4,OEE Report!$B:$B,0))

    Here the highlighted part is the worksheet tab name. When ever a worksheet tab name contains a space or any other special character , the entire name has to be enclosed in single quotes. Thus , your formula should be :

    =INDEX('OEE Report'!$C:$C,MATCH($A4,'OEE Report'!$B:$B,0))

    Narayan
  3. Chris Turner

    Chris Turner New Member

    Messages:
    19
    Narayan, Thank you for your help. The formula works great. I am now trying to add to the formula to only index and match data on a date range
  4. Chris Turner

    Chris Turner New Member

    Messages:
    19
    I inserted the formula that Narayan provided and it gave me the part numbers for each molder, however I want to ensure that I am seeing the latest part numbers in Column B in the Master Downtime Analysis 2018 based on yesterday's date in the same worksheet in cells D1 and D2. I have tried amend the formula by inserting an IF statement but have been unable to get it to work. I would truly appreciate any help with this.

    =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))

    Attached Files:

  5. p45cal

    p45cal Well-Known Member

    Messages:
    1,115
    Attached is a very different approach. It's not finished; Column B (Part Number) needs a formula that updates itself - currently it doesn't. Note that this column can only have part numbers from yesterday, since that's what the data in column D (Yesterday downtime) represents. If there was no part number processed by say Molder 9 yesterday, but you had a part number processed on a previous day by that same molder showing in column B, it would show zero downtime for that part number yesterday which isn't strictly true since that part number wasn't processed at all yesterday! That's why there are some #N/As in there.
    It needs 3 pivot tables to grab the information from (on the same sheet at the moment).
    The intention is:
    Not to have the pivot tables visible (they can be on a hidden sheet)
    For them to update themselves and adjust their source range as the data on the OEE Report sheet changes.
    They already adjust themselves for Last week and Yesterday (see note below).

    Notes:
    • Because your data doesn't include any data from yesterday (I realise it did when you posted it) I've temporarily changed the date filter in the 3rd pivot to be Equals… to the 13th April 2018, just so that something shows in that pivot; you need to change it to simply Yesterday.
    • All molders for all the dates in your sample data worked on just one part number, so I changed some data on the OEE Report sheet so that they worked on different part numbers on different dates. These are highlighted in yellow on that sheet.
    • The list in column A was manually entered (not a formula) and remains so.

    This approach uses the built in pivot table data processing which is quite fast. If this is a route you think you might like to take I'll write a few lines of code for the necesssary updating to take place and move the 3 pivot tables to a hidden sheet.

    Over to you.

    Attached Files:

  6. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,615
    Hi ,

    In case you still want to go with your formula approach , see the attached file.

    When your data is in the form of an Excel table , always make use of structured references.

    Narayan

    Attached Files:

  7. p45cal

    p45cal Well-Known Member

    Messages:
    1,115
    Narayan, Molder 2's downtime for 13th April is a bit low isn't it (cell D4)?
  8. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,615
    Hi ,

    I have changed the formulae as follows :

    1. $D$1 and $D$2 will both contain the same date.

    2. Column C will contain the downtime for the date in $D$1 / $D$2.

    3. Column D will contain the downtime for the date previous to the date in $D$1 / $D$2 ; I am using ($D$1 - 1) for yesterday.

    4. Column E makes use of the 7 day period , the dates for which are anyway derived in $E$1 and $E$2.

    Narayan
  9. Chris Turner

    Chris Turner New Member

    Messages:
    19
    Thank you for your help and wonderful feedback.

    p45cal - The Master Downtime Analysis 2018 is a table that pulls data from the OEE Report. I have pivot tables pulling and sorting values from this Master Downtime Analysis 2018 table for yesterday, past 7 days and overall totals.

    Narayank991 - I am not sure I understand how you are changing the formulae without seeing an example.

    Regards
  10. p45cal

    p45cal Well-Known Member

    Messages:
    1,115
    Gosh, so you have pivot tables based on the Master Downtime Analysis 2018!
    So the aim is to produce these pivot tables and charts, and the Master Downtime Analysis 2018 is just a helper or intermediate table to that end?!
    If that's the case, I suspect you might not need the intermediate table at all, you can get the same (or very, very similar) pivot tables and charts by pivoting the data in the OEE Report directly.
    In the attached I've added some pivot tables and chart beneath each one next to yours which seem to give the same results (notwithstanding that your pivots can't be refreshed because the headers are different - did you alter the source for your pivot without refreshing?).
    I can't find your pivot for overall totals, so I've added a pivot and chart for that on Sheet1.
    Again, instead of choosing Yesterday, I've temporarily chosen Equals… in the Yesterday pivot date filter.

    Or have I missed the point?

    Attached Files:

    Last edited: Apr 16, 2018
  11. Chris Turner

    Chris Turner New Member

    Messages:
    19
    p45cal, Thank you for your time and feedback. I incorporated the improvements and my workbook is working much better and now achieving what I wanted it to do

Share This Page