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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Watch MrExcel Video

Forum statistics

Threads
1,122,476
Messages
5,596,380
Members
414,063
Latest member
N_Bates

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