Checking cell contents for wildcard-suffixed values without hardcoding the search term

Hello All,

In attempting to port an Access database/report to Excel, I would like to come up with a way to replicate the logic of the following Access query, but without actually hardcoding the values that are being checked for (in a field/column).

Currently, during the many organization restructures/department name changes, the services of a tech-savvy (SQL-aware) user is required to maintain/change these queries, but I would like for an ordinary user to make changes in a table, without having to touch/change an Excel formula when changes occur.

Please Login or Register  to view this content.

So, in the code above, [Business Group] would be a column in Excel, and I would like to check the value of each cell in that column (for all rows of course) to see if it begins with the word "Renewable", but without actually hardcoding the "Renewable*" into the formula.

Likewise, cells in the column named [Division] have to be checked to see if they begin with words such as "Southwest", "Northwest" etc., but again, those wildcard-suffixed terms must not be embedded in the formula, but rather, be looked-up in a user-maintainable table.

Is this do-able, and if yes, what would the formula be, and how would I setup the table?

Thanks

@dflak,

Much thanks for your reply...and you know what? I guess you could say "great minds think alike!"....since I too had started going down the path of using VBA (not knowing how to achieve it using just formulas and a list/table...and not getting a reply here, and on another Excel forum).

So, even though I would prefer to only achieve this using formulas (which obviously are much quicker, and not much of a payload), I guess I'd have to do it using an UDF instead.

I haven't seen your code/workbook as yet, but what I've started my code with is the following:

Please Login or Register  to view this content.

Right now, I do have it hard-coded in the code above, but I should easily be able to reference a wildcard-suffixed cell value (from a list/table) instead.

What do you think?

I'll take a look at your suggestion as well...perhaps it look better than my solution.

Oh, one more thing:

That last part that you suggested and offered (i.e. getting all fancy with 'begins with", "ends with", "contains" etc.) would indeed be great to have...so would appreciate if you could add that to your code and re-attach.

Unfortunately, I will not be able to download this file while I'm in the office, and will have to do it once I get home. Will have to ask any further questions on Monday.

Thanks again!