Automate data validation with vlookup

Topic

New Reply

Hi All
I need to format a Region sheet which will use a data-validation drop-down box for the user to select an address. The addresses are on a second worksheet [Address] and the Region data-validation drop-down is populated by the “range” of addresses on the Address sheet [see attached].

Is there a way to look up the ID on the Region sheet and return the range of addresses in the drop-down box?

Looked through the other postings but could not find this particular need. Any help fully appreciated; will save hours [days] of work!

Meleia

Reply | Quote

Replies

While clicked into cell A2 on the Region sheet, create the following named range formula
=INDIRECT(ADDRESS(MATCH(Region!$A2,Address!$A:$A,0),2,,,”Address”)&”:”&ADDRESS(MATCH(Region!$A2,Address!$A:$A,0)+COUNTIF(Address!$A:$A,Region!$A2)-1,2))

Reference the named formula, like you did the other range names in the Data Validation.

Reply | Quote

Thanks. I am not sure how this would work. I would like the formula in Region B2 to access the ID in region A2, then look up all of the addresses in Address B2 that had the matching ID and present them in a drop-down list for user selection.

I am trying to get away from the range names as they would have to be changed in the formula with each ID change. That is what the original drop-down is doing now and it causes a lot of manual work, changing the range name in the validation box. Unless I’m missing something?
Thanks

Reply | Quote

Thanks. I am not sure how this would work. I would like the formula in Region B2 to access the ID in region A2, then look up all of the addresses in Address B2 that had the matching ID and present them in a drop-down list for user selection.

I am trying to get away from the range names as they would have to be changed in the formula with each ID change. That is what the original drop-down is doing now and it causes a lot of manual work, changing the range name in the validation box. Unless I’m missing something?
Thanks

Yes, this will avoid having to create multiple range names.
What you are doing is creating a formula that will pull the range of addresses appropriate the the ID in question. Now your Addresses & ID’s will need to be sorted so that all addresses with the same ID are grouped together (but you are doing this already with your range names).
You will be using a formula to lookup the ID and then reference the range of cells in the address sheet with addresses with the matching ID.
The formula you are creating will be stored as a Named Range. Then you will reference the named range in your Data Validation in the same way you are referencing Named Ranges that refer to cell references right now.
You can create formulas as Named Ranges, the real “trick” of the whole process is that you should have cell A2 selected when you build the formula.
I am attaching a sample file. I can break the formula down and explain it if you desire.

Reply | Quote

This is great! I can’t thank you enough. And, yes, can you break the formula down and explain the steps? I think I understand it, but will be applying it to many sheets and want to make sure I can do my own error-checking. Any further illustration you wish to add will be greatly appreciated.

Meleia

Reply | Quote

You can simplify a bit to:
=OFFSET(Address!$B$1,MATCH(Region!$A2,Address!$A:$A,0)-1,0,COUNTIF(Address!$A:$A,Region!$A2),1)

Reply | Quote

Just wanted to add a big thank you to Catherine and Rory for their replies. I’ve used Catherine’s response and my spreadsheet manual processing time has dropped from about two hours per sheet to about 10 minutes! Thanks again!

Reply | Quote

I’ll start by breaking down my formula (Rory’s is definitely more compact )
=INDIRECT(ADDRESS(MATCH(Region!$A2,Address!$A:$A,0),2,,,”Address”) &”:”&ADDRESS(MATCH(Region!$A2,Address!$A:$A,0)+COUNTIF(Address!$A:$A,Region!$A2)-1,2))
The formula uses the following functions: INDIRECT, ADDRESS, MATCH, and COUNTIF. Oh and concatenation using the & symbol

The MATCH function finds the position of an item in an array (range).
In this case, it looks at the ID number and finds its first occurrence in Column A in the address sheet. This is the starting row for the range.
The COUNTIF function counts how many times an item occurs within a range.
In this case, it looks for the ID number and counts how often it occurs within Column A in the address sheet. After subtracting 1 from this number, this will be the ending row for the range.
The ADDRESS function converts row and column numbers into a cell address using the syntax: (row number, column number, absolute/relative, reference style, Sheet Name)

So this portion of the formula:
(ADDRESS(MATCH(Region!$A2,Address!$A:$A,0),2,,,”Address”)
evaluates as follows:
ADDRESS(2,2,,,”Address”) = Address!$B$2 or the start of the range
The second part of the formula:
ADDRESS(MATCH(Region!$A2,Address!$A:$A,0)+COUNTIF(Address!$A:$A,Region!$A2)-1,2))
evaluates as follows:
ADDRESS(2+2-1,2) = $B$3 or the end of the range

The two addresses are glued together using the concatenation symbol and the colon is added within double quotes – this will indicate a range.
Address!$B$2&”:”&$B$3 resulting in the text string “Address!$B$2:$B$3″
Now this text string needs to be converted to a cell reference, and this is what the INDIRECT function does – reads Address!$B$2&”:”&$B$3 as a cell reference, rather than text.

Rory’s use of the OFFSET function, uses Address,Match and Countif as well. But since the address is never written as a string, it doesn’t need to be converted.
Offset returns a “block” or range of cells from a specified starting point, using the following syntax:
OFFSET (starting point, rows from starting point, columns from starting point, height of range returned,width of range returned)

=OFFSET(Address!$B$1,MATCH(Region!$A2,Address!$A:$A,0)-1,0,COUNTIF(Address!$A:$A,Region!$A2),1)
or
=OFFSET(Address!$B$1,2-1,0,2,1)
or from cell $B$1 on the address sheet, move down 1 row, remaining in the same column , (=Address!$B$2) return a block sized 2 rows by 1 column (=Address!$B$2:$B$3)

While Rory’s is more compact, my formula has the advantage that you can actually work it out step by step.

Reply | Quote

Thank you, Catherine, for all of the time you spent on this. I really appreciate the formula breakdown — it’s how we learn best!
Meleia

Reply | Quote