Building Solutions Using: Tables, VLOOKUP, INDIRECT & VBA

Today we weave together a few Excel features to build out a dynamic solution.
We have Offices, Reps and their Percent of Quota.
Using dependent dropdown lists, we solve a problem of changing the Office selection but the Rep name stays next to the wrong Office.

With the help of Excel MVP Jordan Goldmeier, we use VBA to solve that.

And then we look at the ease of adding data from a new City. By using tables, INDIRECT and VLOOKUP, this is very easy to do.

The blogpost and download link are here:

Thanks for watching the video. Please send any questions. Please subscribe to the channel and let's work together to keep our data clean!

[OzduSoleilDATA]

Weaving together several Excel features to build a dynamic solution. We use Tables, INDIRECT, VLOOKUP and a little VBA.
Thanks to Jordan Goldmeier for his help.
And for my favorite Hungarians, Krisztina Szabó and X Szil, Budapest is used in an example. 

[ZackBarresse]

Nice video. :)

You could do this with a single table for housing your data and an array formula for your % Quota formula. The formula would look something like this...

=INDEX(Table1[% of Quota], MATCH(B2&C2, Table1[Office]&Table1[Name], 0))

This assumes your table is named "Table1", input cells are B2 for the Office and C2 for the Name. Of course the only thing this wouldn't give you is a unique list of both Office and/or Name. So if you wanted those as separate tables it would be a max of 3 needed. It also assumes there will only be one match (because it'll only ever grab the first match).

Or if you didn't want to use an array formula you'd just need another column in your table to concatenate the values together to get a unique index.