Collate data from another sheet depending on month selected in dropdown list

Hi all,

Sorry to cover something that has probably been done multiple times and is probably quite basic, I have searched long and hard but when I search anything to do with dropdown lists, I get results of how to create them.

So... looking at the Sample attached, for this sample all data in cells are fixed numbers and references.

In the original file, all data in cells sheet2!H6:L22, sheet2!N6:R22, sheet2!T6:X22, etc, will be auto collated from other worksheets so that all 17 reports for all 5 samples are collated to one sheet. Sheet2 is setup to be the 'helper sheet'.

I am looking for a formula for sheet1!J4:N20 that when month in sheet1!M2 is selected it matches the month in sheet2 row 4 and returns the correct range of cells from sheet2?

So when...
sheet1!M2 = February it returns sheet2!H6:L22
sheet1!M2 = March it returns sheet2!N6:R22
sheet1!M2 = April it returns sheet2!T6:X22 ...etc

I'm open to achieving this by formula or VBA, I was only looking for a formula based answer in case I use this via the web version of Excel.

TIA

Thanks HansDouwe for the quick reply

Unfortunately this doesn't seem to be working on your uploaded sample or when I put the formula in to my sample on my hard drive.

All months except April, September, October & November are returning results for August. April, October & November are the only ones returning the correct results and September is returning all 0 results

Yes that's perfect, exactly what I was looking to achieve. Thanks you

Sorry but need to reopen this thread.

As stated above this works faultless on my home computer where I designed it. Sample file is a stripped out version of my working doc.

I've tried both my working doc and the sample file at work and it doesn't work. Overview!J4:N20 comes back with errors, unfortunately I'm not at work now to provide sample of error but my question is, I'm running 64bit at home but we have 32bit at work. Would this cause the formula in said range to not work properly?

I'm now thinking VBA might be the way to go to eliminate this problem

You are a legend, thanks for your reply again.

Is it the older version that would return the #NAME-error cause I have Microsoft 365 at home and work I think is older? It was definitely the #NAME error that was showing.

I'll try it tomorrow when back in work and let you know how I get on

This seems to have worked on both my version at home and the older at work, so seems to be fixed.

Thank you again, not sure why I thought it might be a bit version issue when it makes more sense being a older/newer issue... one to remember for me