Variable Data Validation for LIST

[gharwood]

I have created a list through Data Validation for a Drop Down menu based on the below table with the following formula:
=OFFSET(TEST!$A:$A,0,0,COUNTA(TEST!$A:$A),1)

On another sheet in a cell (example A2) the user can select from a dropdown containing: AF, AG, AW, BA, BC, BV, CG, CK (based on the above formula)

A cell below (example A3) I wish to present them with another dropdown that gets populated based on their earlier choice:
EXAMPLE 1:
In cell A2 they choose: BC
In cell A3 the dropdown should contain: Bob, William
I am expecting that the formula that would work will have somewhat that following format:
=Offset(TEST$!$5:$5,0,1,1,counta(TEST!$5:$5)-1)

In cell A2 they choose: AG
In cell A3 the dropdown should contain: George, Peter, Stuart
I am expecting that the formula that would work will have somewhat that following format:
=Offset(TEST$!$2:$2,0,1,1,counta(TEST!$2:$2)-1)

In cell A2 they choose: BA
In cell A3 the dropdown should contain: Carl, Chris, Fred, Jeff, Richard
I am expecting that the formula that would work will have somewhat that following format:
=Offset(TEST$!$4:$4,0,1,1,counta(TEST!$4:$4)-1)


I know that the above formulas work, but how do I make them work with the value in A2?

The following formula can find the ROW
=MATCH($A$2,TEST!$A:$A,0)

How do I work this in the Below formula?
=Offset(TEST!$2:$2,0,1,1,counta(TEST!$2:$2)-1)

I have tried replacing (in the above example) the 2 for MATCH(TEST!$A$2,TEST!$A:$A,0) but no luck.

Can someone help?

	A	B	C	D	E	F
1	AF	John
2	AG	George	Peter	Stuart
3	AW	Pierre	Oscar
4	BA	Carl	Chris	Fred	Jeff	Richard
5	BC	Bob	William
6	BV	Hans
7	CG	Jack
8	CK	Alan	Joe

SHEET: TEST

[MarvinP]

Hi GHarwood and welcome to the forum,

See the example where I've changed your data a little so I could do Advanced Filters on it. Then*using some VBA code to rebuild the name list it will do what you want. Look at the VBA code behind the sheet and in the module.

[AliGW]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).