Needing some LOOKUP, MATCH, INDEX assistance

TheJayB

New Member
Joined
May 13, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I’m certainly no super expert when it comes to Excel.. but I can usually work my way around things and put together formulas that end up working with a bit of common sense. I have now come across a lookup that I just cant seem to workout. Ive tried messing around with H and VLOOKUPS, INDEX, MATCH… and all I can seem to get back is #REF. Now me offering to help to try and automate a spreadsheet that my team use seems like something I maybe should have thought twice about.

I have a feeling what I am trying to achieve is a 2 stage process… ill try to explain the best I can in word and ill also attach a stripped down version of the spreadsheet to assist (or at least some pics as I cant attach a spreadsheet). I do have a copy of the workbook available if required.

So I have 3 worksheets:

14 Day Breakdown Tab – This sheet just provides a report to the user based on the information contained in the other tabs. Ultimately, it’s this tab that im looking to populate with some data from other tabs… mainly around counting different severity Defects logged on that specific day.

Defects tab – Manual or dropdown entry worksheet, user will add to this list if any defects to log. The date is not captured as part of the manual entry. I believe the first task I have is to put a hidden formula at the end of each of the rows (M) that goes to the schedule tab, finds the name of the test in cell C9 and provides the date that test was scheduled (by looking at the column header?). Alternatively.. I could add another manual entry column that puts it onto the user to capture the date.. but then im not really automating like I would like to.

Schedule Tab – Contains a list of all the tests due to be run in a nice fancy schedule. I believe the only use for this sheet is to look up the Test name from the Defect tab and provide back the date that test is scheduled.

Once the date is then available on the Defects tab.. on the Summary Page.. I need another lookup to populate the defect table. So any Sev 1 defects that were raised on Monday are counted and entered in the relevant field. Likewise for the other sevs… and then continuing down the dates until complete.

I now have myself so confused I don’t know whats the right way or wrong way to do this. Any help that anyone can give would be very much appreciated. Ive hunted on the net and find similar things to this but nothing that seems to help. I have left all the working on my worksheets visible as ive done a number of other lookups, etc. that do work. Its just this one causing me grief. I am not a fan of asking for help.. but for my sanity.. this time I will.

Thanks everyone
 

Attachments

  • 14 Day Breakdown.png
    14 Day Breakdown.png
    31.2 KB · Views: 3
  • Schedule.png
    Schedule.png
    42.5 KB · Views: 3
  • Defects.png
    Defects.png
    33.6 KB · Views: 3

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

TheJayB

New Member
Joined
May 13, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hi All... just to add a bit to this. I worked out how to pull the correct date the defect was raised from the Schedule into the Defect Tab... so I now have the date in Column M (which will be hidden).

Now I just need to work out what formula I need to use on the 14 Day Breakdown to query how many of a certain severity of defect were raised on a certain date. I am thinking it will be something COUNTIF related... but if anyone has any pointers feel free to advise.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,127,318
Messages
5,623,974
Members
416,002
Latest member
Neshx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top