Return cell count based on multiple values (and data validation)

Hi all, long time lurker and first time poster - this site has helped me a lot, but on this one I am stumped. I feel like I know what I need to do but my brain is fizzling out thinking about it. So if I could please ask a bit of a long winded question?

We run a 24/7 manufacturing plant, with 4 shifts, 7 days a week, with 8 manufacturing lines. These shifts are A,B,C and D, which each have a 'long week' and a 'short week'. The spreadsheet I have works out how many people are away, per line, per day. This works great but I want to make it more dynamic. To start this I have used data validation so that a user could select 'Week 1' and it shows the date, and what shift was working on that day (this works fine). I can't think of a formula to use to dynamically change the result depending on the week/shift.

I've attached the sheet, I hope it works - it is company sensitive information so I have only included the sheet I am working on and if I can get an answer I should be able to manipulate it.

Basically, the user selects the week (In the spreadsheet, week 2 is selected, which is shift A and shift D's long week):
Cell B50 needs to return the value in cell I4
Cell B51 needs to return the value in cell I5 etc etc

Cell C50 needs to return the value in cell I37
Cell C51 needs to return the value in cell I38 etc etc

If the user then selects the week to 3, then cell B50 should return the value from cell P15, etc etc.

Sorry, I know I have probably complicated this more than it needs to be, I'll be greatful for any help! Please let me know if you need any more info!

Originally Posted by hemesh

Hello Reapz and Welcome to the forum !

Please find attached ! Is this What you are looking for ?


Attachment 288889

Hemesh, you are an absolute god among men. Thank you so much! This forum already impressed me before I asked this, so I am blown away now. Thank you once again!