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

ambepat

Board Regular
Joined
May 4, 2014
Messages
127
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
ABCDEFGH
209:0009:1509:3009:4510:0010:1510:30
3JohnIBIBBIBIBIBL
4JackIBIBBIBIBIBL
5AmberIBIBBIBIBIBL
6NicoleIBIBIBBIBIBIB
7MaggieIBIBIBBIBIBIB
8JonahIBIBIBBIBIBIB
9
10TimeIBBL
1109:30:00NicoleJohn
12MaggieJack
13JonahAmber

<tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
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
ABCD
1TimeIBBL
209:30:00NicoleJohn
3MaggieJack
4JonahAmber

<tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,188
Messages
5,835,891
Members
430,392
Latest member
bongouni553

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top