Linking Sheet 2 data validation to Sheet 1 data validation with all formulas.

[ambepat]

Hey guys,

Will really appreciate some help here.

I have in Sheet 1 as seen below Time which is linked with data validation and the cells from B10:D13 are linked with formulas. I am trying to create a dashboard in Sheet 2 where I linked all of the those formulas to Sheet 2 with the same format. But I am a little stuck as unable to carry over and linked sheet 1 to sheet 2 with the data validation.

Here is what I mean

Excel 2010

	A	B	C	D	E	F	G	H
2		09:00	09:15	09:30	09:45	10:00	10:15	10:30
3	John	IB	IB	B	IB	IB	IB	L
4	Jack	IB	IB	B	IB	IB	IB	L
5	Amber	IB	IB	B	IB	IB	IB	L
6	Nicole	IB	IB	IB	B	IB	IB	IB
7	Maggie	IB	IB	IB	B	IB	IB	IB
8	Jonah	IB	IB	IB	B	IB	IB	IB
9
10	Time	IB	B	L
11	09:30:00	Nicole	John
12		Maggie	Jack
13		Jonah	Amber

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula B11 {=IFERROR(INDEX($A$3:$A$8,SMALL(IF(INDEX($B$3:$H$8,,MATCH($A$11,$B$2:$H$2,0))="IB",ROW($A$3:$A$8)-ROW($A$3)+1," "),ROWS(A$3:A3)))," ")} C11 {=IFERROR(INDEX($A$3:$A$8,SMALL(IF(INDEX($B$3:$H$8,,MATCH($A$11,$B$2:$H$2,0))="B",ROW($A$3:$A$8)-ROW($A$3)+1," "),ROWS(B$3:B3)))," ")} D11 {=IFERROR(INDEX($A$3:$A$8,SMALL(IF(INDEX($B$3:$H$8,,MATCH($A$11,$B$2:$H$2,0))="L",ROW($A$3:$A$8)-ROW($A$3)+1," "),ROWS(C$3:C3)))," ")} B12 {=IFERROR(INDEX($A$3:$A$8,SMALL(IF(INDEX($B$3:$H$8,,MATCH($A$11,$B$2:$H$2,0))="IB",ROW($A$3:$A$8)-ROW($A$3)+1," "),ROWS(A$3:A4)))," ")} C12 {=IFERROR(INDEX($A$3:$A$8,SMALL(IF(INDEX($B$3:$H$8,,MATCH($A$11,$B$2:$H$2,0))="B",ROW($A$3:$A$8)-ROW($A$3)+1," "),ROWS(B$3:B4)))," ")} D12 {=IFERROR(INDEX($A$3:$A$8,SMALL(IF(INDEX($B$3:$H$8,,MATCH($A$11,$B$2:$H$2,0))="L",ROW($A$3:$A$8)-ROW($A$3)+1," "),ROWS(C$3:C4)))," ")} B13 {=IFERROR(INDEX($A$3:$A$8,SMALL(IF(INDEX($B$3:$H$8,,MATCH($A$11,$B$2:$H$2,0))="IB",ROW($A$3:$A$8)-ROW($A$3)+1," "),ROWS(A$3:A5)))," ")} C13 {=IFERROR(INDEX($A$3:$A$8,SMALL(IF(INDEX($B$3:$H$8,,MATCH($A$11,$B$2:$H$2,0))="B",ROW($A$3:$A$8)-ROW($A$3)+1," "),ROWS(B$3:B5)))," ")} D13 {=IFERROR(INDEX($A$3:$A$8,SMALL(IF(INDEX($B$3:$H$8,,MATCH($A$11,$B$2:$H$2,0))="L",ROW($A$3:$A$8)-ROW($A$3)+1," "),ROWS(C$3:C5)))," ")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Excel 2010

	A	B	C	D
1	Time	IB	B	L
2	09:30:00	Nicole	John
3		Maggie	Jack
4		Jonah	Amber

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula B1 =Sheet1!B10 C1 =Sheet1!C10 D1 =Sheet1!D10 B2 =Sheet1!B11 C2 =Sheet1!C11 D2 =Sheet1!D11 B3 =Sheet1!B12 C3 =Sheet1!C12 D3 =Sheet1!D12 B4 =Sheet1!B13 C4 =Sheet1!C13 D4 =Sheet1!D13 A1 =Sheet1!A10 A2 =Sheet1!A11 <tbody> </tbody>

<tbody>
</tbody>

My question is how do I link sheet 2 to sheet 1 to make it look exactly the same with the data validation from sheet 1 which is centered around the time. Because as I change the time it automatically updates and I need that to do the same on sheet 2?

Thanks again in advance