Index, match, large ? results based on user input for week number

Hello,

Hopefully it is an easy one for someone/anyone who is willing to help!

Attached is the clean xls file

Firstly, I would like to be able to add additional weeks into the table capturing the data so it can be a useful tool moving forward without too much manual editing as time marches on.. not entirely sure how to go about that whilst maintaining the integrity of the "Top 5 Failures" table below

The coding part...

The user would enter the week number into Cell D2 for which they are interested in

That would then populate the "Top 5 failures table" with the highest 5 values from that week (D2) but from the corresponding column in Row 6 and the dataset from rows 7:29... (i guess using the "large" function?), the "defect description" which is taken from corresponding cell from the range D7:F29

And if that was not complicated enough

Cells B39:43 would show the 4 week average too

I can get the various bits to to work as standalone bits but can not get it to be dynamic on User input... which is a must really, as well as being able to add future weeks into the table

I had hoped

=INDEX($D$7:$F$29, MATCH(LARGE($K$7:$K$29,A39),$K$7:$K$29,0))

in F39 would do it but no joy...

Please do say if anything is unclear and I will try to elaborate further

Any and all help will be gratefully received!

Happy New Year!

1. Get RID of all the merged cells in the results area and in the top part of column D.

2. Delete everything in the area set aside for results.

3. use this in D39.

=LET(A,E7:R29,B,E6:R6,C,HSTACK(FILTER(A,B=D2),D7:D29),TAKE(SORT(FILTER(C,INDEX(C,,1)<>0),,-1),5))

the results spill across and down. Format D39 downwards as %

Ah, Thanks that sorts out one aspect! Cheers. I did wonder whether it was the merged cells which was causing the issue

Any idea on the other aspects?

I see that you have made "B" reference the week numbers. Would this continue to work if future weeks were added in? or should this data be incorporated into a dashboard from separate tabs in your opinion?

By "other aspects" do you mean:

Cells B39:43 would show the 4 week average too

if so... with week 2 selected... which four weeks?

And for that matter, the 4 week average of WHAT?

Perfect! Back soon (I hope)...

Formula:

Please Login or Register  to view this content.

see file...

Originally Posted by BA_Abacus

It doesnt seem to work if I enter "3" into D2

Oh Pooh to that!!!

Formula:

Please Login or Register  to view this content.

It could (probably) be made shorter, but my "pre-fab" approach makes it easier to troublesheet (IMHO). The bit in red was at fault. It used to read 5.

Putting nice round numbers into the sample enabled me to track the problem down reasonably quickly.

The formula exists only in one cell. B39. It looks greyed out until you place the cursor in B39 & click in the formula bar... then it's black. If you do that elsewhere, the formula simply vanishes.

the results "spill" out from that cell. If there's anything else in a cell that needs to return a result you'll get a #SPILL error.

TBH, I'm still learning how to use these so-called dynamic arrays, myself. The Excel Forum is lucky to have a few helpers whe really know what they're doing!!


You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

Formula:

Please Login or Register  to view this content.

Maybe this will also help: refer to the file. I've moved the formula to B47. If you enter any of the letters defined at teh left in the formula at th epoint indicated, you can see exactly what the formula returns.

I've also corrected a minor cosmetic error in J that I just spotted.

My last post for the day. Use the formula here, in this file, for your real stuff. It's in the right place and has the cosmetic error fixed.

I'll be around from time-to time tomorrow... maybe... if there are issues.

I have two intercontinental flights to get through. Harrumph.

There may a small misunderstanding here. If I select Wk 49... C39, C40, D39, D40, etc, populates correctly. The 4 wk average column shows an error for some cells. I intended it to be entirely blank... because I assumed that the 4wk average was the average of the PREVIOUS for weeks i.e. 45-48.

What EXACTLY do you want it to show 45-48... 46-49 or what?