LinkBack URL
About LinkBacks
In the attached file I want to create a list by selecting a year from a dropdown list. The list will have between 8 and 12 names depending on the year.
Just looking for a simple way to produce a variable range list.
Jim O
In the attached file I want to create a list by selecting a year from a dropdown list. The list will have between 8 and 12 names depending on the year.
Just looking for a simple way to produce a variable range list.
Jim O
Last edited by JO505; 08-20-2013 at 08:45 PM.
hi Jim. maybe this array formula in H4:
Formula:
Please Login or Register to view this content.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you might get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
1) Create a named range called YEARS of the cells C1:O1 on the List sheet.
2) Use that named range in the Data Validation on List cell B3.
3) The array formula in List cell D4 would be:
=IFERROR(INDEX(Table2[Name], SMALL(IF(OFFSET(Table2[Name],,MATCH($B$2, Years, 0),,)<>"", ROW(Table2[Name])-1), ROWS($A$1:$A1))), "")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
4) When "Name3" appears in D4 successfully, copy D4 downward to get the rest of the list.
Last edited by JBeaucaire; 08-19-2013 at 10:42 PM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
JBeaucaire,
Thanks for your time. The sample is just what I was looking for, however I don't seem to understand it formula as well as I thought I did as witnessed in my attached file. I think my problem is in the "Rows" section of the formula.
I can get the formula to display a list of names but not the correct ones. The attachment is a clearer example of my goal. I like the formula but I just cant quite get my brain around it fully.
Thanks for the help.
Jim O
You added more columns amidst the table between the names you want to return and the data table, 3 columns to be exact. So we have to adjust the OFFSET() part of the formula to move over those additional 3 columns.
THe FIRST formula is:
=IFERROR(INDEX(TeamUSATbl[USA Player], SMALL(IF(OFFSET(TeamUSATbl[USA Player],,MATCH($B$2, Year, 0)+3,,)<>"", ROW(TeamUSATbl[USA Player])-2), ROWS($A$1:$A1))), "")
JBeaucaire,
Thanks for the help. It's working fine now. One of these days I'll figure this stuff out. In the mean time it's nice to have a place to go get answers.
Thanks again for your time.
Jim O
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks