Bit of LOOKUP advice in a multi-tab worksheet - How do I do this?

[TheJayB]

Hi there you wonderful people.

Like quite a few people, I decided to try to learn some new skills while under lockdown… and crazily offered to try to help automate my small teams testing documentation. The learning had not been going too bad… and then I stumbled across LOOKUPs and INDEX and suddenly I have ground to a halt. I am one of those type of people that once they have been shown once… I can usually work my way around the rest. So I am hoping this will be nice and simple for someone out there… rather than making my poor brain hurt any longer.

My Version - EXCEL 2016

I have attached a stripped down version of the spreadsheet im working on (but left all my working visible). All the relevant tabs are shown in the worksheet. Just to explain what each of them are… which may help to decipher how I am able to achieve what im looking to do.

Test Case List tab – This will be manual entry.. picking from drop downs, etc. I will call this the 1st tab in the flow.
Test Schedule tab – Mostly manual entry. Drop down list of the Test IDs from the Test Case List tab (ListTestID). This would usually be completed 2nd in the flow.

Resource Schedule tab – This is where I have got a bit stuck. I’ve tried to get my head around LOOKUPs or VLOOKUPs or INDEX… but I think I might be oversimplifying what I am trying to do. The idea for this is to automate the information in the columns C to H from the information already entered into the other 2 tabs. The first 2 columns are easy.. just referring to the relevant fields on the Test Case List tab. It’s the E to H columns that have me scratching my head. I basically need it to take the value from the C7 field (Test ID).. look for that in the Test Schedule tab and if it’s there to display the date, time, etc. that it has been booked under on the schedule tab. If there is no Test ID with that name on the Schedule.. it should just show a blank field.

I know the formula I have in the E7 field will never achieve what I am attempting for the date, I have just left the most recent version of the formula that actually displayed something instead of it just erroring.
I hope that I have explained the issue in sufficient detail, but do please let me know if anything else is required. Much appreciation to anyone who takes the time to read my post… and fingers crossed that someone can explain this to me so even I can do similar lookups in the future.

Thanks for your time everyone.

Jason

[XLent]

the layout of your Test Schedule tab really isn't conducive to easy analysis, I'm afraid.

without changing anything you could, in theory, do the following:

Please Login or Register  to view this content.

so, if I add Test 1 to I58 on Test Schedule the above would return:

27/04 - 12:00 PM - 06/05 - 05:30 PM

obviously you can change the formats of E:H per your own requirements - all values are stored as Date/Time.

[XLent]

edit: the above will need to be tweaked actually for the date fields given the way the month headers are being stored in the Test Schedule tab (I jumped to an incorrect conclusion).
I'll revert in a bit, unless someone provides you with something in the interim.

[XLent]

just revisited your file - in retrospect you're making life a little harder for yourself than need be - so I'd suggest doing the following:

Please Login or Register  to view this content.

the above will persist your current display but now means you're storing the actual date serial in the cell itself -- which makes calcs on Resource Schedule much simpler

Please Login or Register  to view this content.

the data storage still isn't great but the above should generate the requested values.

[TheJayB]

Thank you so much matey. I will hold my hands up that my data collection is a bit shocking and just cobbled together as I am trying to learn more. Works like a charm. Thanks again