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
John	IB	IB	B	IB	IB	IB	L
Jack	IB	IB	B	IB	IB	IB	L
Amber	IB	IB	B	IB	IB	IB	L
Nicole	IB	IB	IB	B	IB	IB	IB
Maggie	IB	IB	IB	B	IB	IB	IB
Jonah	IB	IB	IB	B	IB	IB	IB
Time	IB	B	L
Nicole	John
Maggie	Jack
Jonah	Amber

<tbody>
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]09:00[/TD]
[TD="align: right"]09:15[/TD]
[TD="align: right"]09:30[/TD]
[TD="align: right"]09:45[/TD]
[TD="align: right"]10:00[/TD]
[TD="align: right"]10:15[/TD]
[TD="align: right"]10:30[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]09:30:00[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B11[/TH]
[TD="align: left"]{=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)))," ")}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C11[/TH]
[TD="align: left"]{=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)))," ")}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D11[/TH]
[TD="align: left"]{=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)))," ")}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B12[/TH]
[TD="align: left"]{=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)))," ")}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C12[/TH]
[TD="align: left"]{=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)))," ")}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D12[/TH]
[TD="align: left"]{=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)))," ")}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B13[/TH]
[TD="align: left"]{=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)))," ")}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C13[/TH]
[TD="align: left"]{=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)))," ")}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D13[/TH]
[TD="align: left"]{=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)))," ")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]





Excel 2010

	A	B	C	D
Time	IB	B	L
Nicole	John
Maggie	Jack
Jonah	Amber

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]09:30:00[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=Sheet1!B10[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=Sheet1!C10[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]=Sheet1!D10[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=Sheet1!B11[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=Sheet1!C11[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=Sheet1!D11[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]=Sheet1!B12[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]=Sheet1!C12[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]=Sheet1!D12[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=Sheet1!B13[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]=Sheet1!C13[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]=Sheet1!D13[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A1[/TH]
[TD="align: left"]=Sheet1!A10[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]=Sheet1!A11[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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