User Defined Function is static

[zoudazou]

Hello all,

I am trying to create a function that looks for text in a string, and returns it.
Example:

List of Cake types in Column A:
Cool Cake
Monster Cake
Portal Cake

Strings:
I love Monster Cake!
Super Mega Cool Cake is delicious!
The Portal Cake is a Lie!

I need the function to return which Cake Type it is based on types listed in column A

I also need the list of cake types to be updateable, so I have about 15 cells dedicated to Cake Types.

I currently have this:

Please Login or Register  to view this content.

The problem is that I when I add or remove Cake Types, the formula doesn't return the new type even if I save, or close and re-open the book. It will only update if re-enter the formula.

Basically, Let's say I delete one of the Cake Types from the list. The Function should return "Regular Ol' Cake" since the type no longer exists. However, it just stays the same, even though that type is no longer in the range specified.

This was kind of a crappy example, so props to whoever can figure this out.

[rylo]

Hi

Can you please attach an example workbook.

Make sure you include the function code, and an example of it in use.
rylo

[zoudazou]

Sure thing, thanks!

Just try to delete anything in the highlighted range, and the function won't reflect the change. It also won't change if you add "Super Cake" to the list.

Best Regards,
zoudazou

[shg]

Your UDF doesn't recalculate because Excel sees no dependency on the data in Macro Sheet.

UDF notwithstanding, if this the logic?

Please Login or Register  to view this content.

The array formula in C2 and copied down is

=IFERROR(INDEX($A$2:$A$4, MATCH(TRUE, ISNUMBER(SEARCH($A$2:$A$4, B2)), 0)), "Regular ol' cake")

[zoudazou]

That did it! That's the formula that I needed. When I was attempting Excel's functions, I kept going for an array, I just couldn't find the right combination. :/
Thanks for your help!

Just for learning's sake, is there a (somewhat easy) way that I could make the UDF dependent on the data in Macro Sheet? Or is that super complicated?

[shg]

Pass it as a range:

Please Login or Register  to view this content.

If its length will vary, use a dynamic range. See http://www.contextures.com/xlnames01.html

[zoudazou]

Awesome! Thanks again for helping me to learn more!