Forum Discussion
Solved
Formula or Function???
I have an excel spreadsheet that is huge (over 400 names and over 30 courses) and I need an easy way for a person to see their specific course requirements. Not sure if they would click on their name...
=IFERROR(INDEX($AG$16:$BQ$16,SMALL(IF(OFFSET($G$1,MATCH($BU$21,$G$23:$G$435,0)+21,26,1,37)=1,COLUMN($A:$AK)),ROW(1:1))),"")You are welcome. You can try this formula which works in my file. The formula has to be entered with ctrl+shift+enter if one doesn't work with Office 365 or Excel 2021.
Quadruple Pawn,
Thanks so much for your response! It worked perfectly when I utilized in a table as above but when I tried to duplicate it in the actual table it didn't work for some reason?? My names are in G23 to G435 and my course title headers are in AG16 to BQ16. I am attempting to make BU21 my input box and added formula in BU24:BU50 with no results. Here is how I rewrote the formula (would you mind looking at it and see if you see any errors??):
=IFERROR(INDEX($AG$16:$BQ$16,SMALL(IF(OFFSET($G$21,MATCH($BU$21,$G$22:$G$535,0),1,1,7)=1,COLUMN($G:$BQ)),ROW(1:1))),"")
Thanks again!
Alicia
Thanks so much for your response! It worked perfectly when I utilized in a table as above but when I tried to duplicate it in the actual table it didn't work for some reason?? My names are in G23 to G435 and my course title headers are in AG16 to BQ16. I am attempting to make BU21 my input box and added formula in BU24:BU50 with no results. Here is how I rewrote the formula (would you mind looking at it and see if you see any errors??):
=IFERROR(INDEX($AG$16:$BQ$16,SMALL(IF(OFFSET($G$21,MATCH($BU$21,$G$22:$G$535,0),1,1,7)=1,COLUMN($G:$BQ)),ROW(1:1))),"")
Thanks again!
Alicia
=IFERROR(INDEX($AG$16:$BQ$16,SMALL(IF(OFFSET($G$1,MATCH($BU$21,$G$23:$G$435,0)+21,26,1,37)=1,COLUMN($A:$AK)),ROW(1:1))),"")You are welcome. You can try this formula which works in my file. The formula has to be entered with ctrl+shift+enter if one doesn't work with Office 365 or Excel 2021.
- WORKS!!! Thank you so much!! I just hid all the columns except the drop down list for people to choose their names alphabetically and they get their own list! Glorious!! Thank you!!
Alicia