Drop-down list showing only a desired range of a sheet.

[H.Gabor]

Hey guys,

I have a problem processing data, that looks like as shown in the attachment. (Sheet "Munka1")
I was thinking of having a drop-down list of the dates on another sheet, and when selected, the correct range of data shows up based on what date I selected.

I included a plain idea on sheet "Munka2" of how I imagined it would work.

Do you have any suggestions?

[Glenn Kennedy]

With suitable headers in E3 & F3, an array formula copied across and down:

=IF(OR($E3="Average:",$E3=""),"",INDEX(Munka1!A1:N1,,MATCH(Munka2!$B$11,INT(Munka1!$A$1:$N$1),0)))

Check the sheet, as you will probably need to use : intead of ,

Format as desired.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[H.Gabor]

It seems to be working as I wanted it.
Now because there will be new data imported to sheet "Munka1" following the scheme, I would like to widen the range of this function to cover a lot bigger range of data. (Dates expected are presented in sheet "Munka2")

Just to be clear, I added new data with date "2018.07.23" and I would like it to appear on sheet "Munka2" when selecting this date from the drop-down list. And same with the other dates.

[Glenn Kennedy]

Minor tweak... still an array formula.

=IF(OR($E3="Average:",$E3=""),"",INDEX(Munka1!1:1,,MATCH(Munka2!$B$11,INT(Munka1!$1:$1),0)+COLUMNS($A:A)-1))

[H.Gabor]

Thank you so much, it is now doing exactly what I imagined. You're amazing.

[Glenn Kennedy]

You're welcome!!