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