so my question regards the functionality of a dynamic named range. I've done some research, and everything I've seen seems to involve solving specific issues, none of which are the issue I'm encountering. So, I'm going to state what I'd like to do, and hopefully, one of you will be able to help me understand why I'm getting the results I'm getting!
I'm trying to use a dynamic named range to create a list for data validation. I'd prefer to do this macro-free so I can create a more user-friendly interface for my coworkers, who aren't computer savvy. The issue I have is that when I use formulas to pull from the range (which is on a separate, hidden sheet), instead of pulling all items in the range, the formula pulls one item from the same row.
For example, If I have "apples" in $J$6 as part of my named range fruits, the formula:
`=SEARCH(fruits, "I ate fifteen bananas today.")`
would compile as
`=SEARCH("apples", "I ate fifteen bananas today.")`
instead of
=SEARCH({"pears";"bananas";"mangos";"grapes";"apricots";"apples";"oranges";"raspberries"}, "I ate fifteen bananas today.")
I've tried entering the formula as an array to no avail. I am inconsistently getting my formulas to run through each item in the DNR as opposed to just one and I'd like some clarification on how they function in Excel. Is there some pattern I'm missing? I'm going to include some other formulas I've tried for additional information; I don't know if any of it will help, but more information is more information.
I'm pulling these from a separate worksheet I'm working on because I don't want to post student information. This is from a game I'm designing, but it's the exact same issue (I'm actually using one to help build the other - tee hee hee).
Thanks in advance to anyone that can offer some insight! I've learned most of what I know through you guys online, and this is my first time asking questions on Excel. Cheers!
`=SUMPRODUCT(--ISNUMBER(SEARCH(keywords, INDEX(skillsActive, MATCH(B3, OFFSET(skillsActive, 0,2),0)))))`
I found this formula on this site (I don't remember the thread, however, so sorry to the programmer!). It searches every item in the keywords DNR, but converts everything to a 0 or 1, and what I'd like it to do is prepare the results as strings and create a list based on those results.
`{=IF(B2="","",IF(ISERROR(SEARCH(B2,skillsActive))=FALSE, OFFSET(skillsPassive, 0, 1), FALSE))}`
The range skillsActive also expands how I'd like it to, but it is not a DNR; it is named range whose cell values are raw strings. B2 is a list of keywords using Data Validation. This formula only returns the first iteration of skillsActive - it will either return Soldier if B2="Blinded", or FALSE for any other result.
Using SEARCH, that would need to be treated as an Array Formula, ideally with an AGGREGATE. We can use SUMPRODUCT to force Array-formula calculation in a normal formula.
=SUMPRODUCT(AGGREGATE(15,6,SEARCH(fruits, "I ate fifteen bananas today."),1))
SEARCH(fruits, "I ate fifteen bananas today.")
Within aSUMPRODUCT, this evaluates as an Array formula:SEARCH({"pears";"bananas";"mangos";"grapes";"apricots";"apples";"oranges";"raspberries"}, "I ate fifteen bananas today.")
and becomes:
{#VALUE!;15;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
AGGREGATE(15,6,{#VALUE!;15;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!},1)
The second Argument is6, so we discard all error values:
AGGREGATE(15,6,{15},1)
The first argument is15, so we count from the smallest to the largest, and the fourth argument (1) tells us which element to retrieve, in this case the smallest:
15
=SUMPRODUCT(15)
TheSUMPRODUCTmostly just forces this to evaluate as an array formula. It multiplies the corresponding elements in each argument together, then sums the results:
=15