Create a table with 1 column, that is dynamically updated from other multiple columns.

[marcusduton]

I have this senario

I have Column A, B, & C
Each Column is a Single Column Data Table
The First Line in Each Column A, B, & C have a Data Record that Identifies the column. I want that record and all of the records as well from each of A, B, C to update Column F Which is a single column data table as well. So that I can create a drop down data validation list from it. Any Suggestions of how I would go about this.
But I need Column A records to come first. Column B Records to come 2nd, and Column C Records to come last.
See Attachment for Example

[FDibbins]

Are you OK with using helper columns? (They can be hidden)

[marcusduton]

Helper Columns will be fine

[FDibbins]

OK, this got a bit messy, other members may have a better approach, but this works, see the attached

[marcusduton]

I need the data from a2,b2,c2 to show into the list as well as any other data or text that I may put into them. a2:c2 are just header text that I want in the list, to act as a section seperator

[FDibbins]

ooops sorry, forgot to adjust teh formulas I did it that way to make sure I was getting the correct references - gimme a sec

[FDibbins]

Change the formula to this...
=IFERROR(IFERROR(IFERROR(INDEX($A$3:$A$17,MATCH(SMALL($H$3:$H$17,ROW(A1)),$H$3:$H$17,0)),INDEX($B$3:$B$17,MATCH(SMALL($I$3:$I$17,ROW(A1)-COUNT(H:H)),$I$3:$I$17,0))),INDEX($C$3:$C$17,MATCH(SMALL($J$3:$J$17,ROW(A1)-COUNT(H:I)),$J$3:$J$17,0))),"")

Format as time (or just use the format painter)

[marcusduton]

How do I get this to stop skipping the first row. I tried to modify the formula to instead of using $A$3 to use $A$2 instead. But that really buggered it up.

[FDibbins]

so tour data starts in A2, not A3?

[marcusduton]

that is correct

[FDibbins]

OK, you need to make the changes in the helper columns

copy H3:J4 to H2

[marcusduton]

I get ya. and I can make this range dynamic as well if I place the helper columns in a data table correct?

[FDibbins]

probably, try it and find out

[marcusduton]

I have tried, it everything is working as I wanted it to except for 1 thing. I keep getting 0 and the end of my input columns. See Attached file for example. Other than this, it is nice and tight and just what I was looking to do.

[marcusduton]

Any Suggestions as to what is causing this?

[FDibbins]

In H4, I4 and J4, you are referencing A3, B3 and C3, when you should be referencing A4, B4 and C4...you are already referencing them in H3, I3 and J4. Fix that and copy down, the 0's will go away

[marcusduton]

Still not working after making the changes you suggested.

[FDibbins]

It worked ok for me...

	F	G	H	I	J
2	All Times		D	N	O
3	--STD -- AM Times--		1	14	19
4	5:30am-4:00pm		2	15	20
5	6:00am-4:30pm		3	16	21
6	6:30am-5:00pm		4	17	22
7	7:00am-5:30pm		5	18	23
8	7:30am-4:00pm		6		24
9	7:30am-6:00pm		7		25
10	7:30am-6:00pm		8		26
11	8:30am-1:30pm		9		27
12	8:30am-5:00pm		10		28
13	8:30am-7:00pm		11		29
14	9:00am-7:30pm		12		30
15	9:30am-8:00pm		13		31
16	--STD -- PM Times--				32
17	6:00pm-4:30am				33
18	7:00pm-5:30am				34
19	7:30pm-6:00am				35
20	8:00pm-6:30am
21	--Other Times--
22	--Waitlist--
23	11:30am-12:30pm
24	12:00am-6:30am
25	12:00pm-7:00pm
26	4:00pm-4:30pm
27	4:30pm-5:30pm
28	4:30pm-5:30pm
29	4:30pm-6:00pm
30	4:30pm-9:30-pm
31	5:30pm-6:30pm
32	5:30pm-9:30pm
33	6:00pm-7:00pm
34	6:00pm-7:30pm
35	6:30pm-7:30pm
36	7:00pm-3:00am
37	8:30am-12:00pm

Check to make sure H only goes to 13, I to 18 and J to 35?

After you changed the references in H4, I4 amd J 4, did you copy them all the way down?

[marcusduton]

Ok I got it. Thanks.

[FDibbins]

good stuff