Populating one sheet from another in the same workbook

[flaren]

In the first sheet, where the data is entered, I want to populate the second sheet. Column "H" in the first sheet there is different data entered, and I want to find specifically STD-210, and then populate the associated sample number in the first sheet, which is in the "I" column, into the second sheet in the "B" column, but I keep encountering an issue that it's populating all data.

Below are examples of what I'm working with.

First sheet (below) in workbook, named: HG_2018_Sample_Tracking_MASTER
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]Type[/TD]
[TD]From[/TD]
[TD]To[/TD]
[TD]Area[/TD]
[TD]Level[/TD]
[TD]Heading[/TD]
[TD]Round[/TD]
[TD]String name[/TD]
[TD]Sample#[/TD]
[TD]By[/TD]
[TD]Date[/TD]
[TD]Shift[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]170S[/TD]
[TD]N[/TD]
[TD]R60[/TD]
[TD]170S-R60[/TD]
[TD]5555[/TD]
[TD][/TD]
[TD]Jan22[/TD]
[TD][/TD]
[TD]5.7[/TD]
[/TR]
[TR]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]170S[/TD]
[TD]N[/TD]
[TD]R58[/TD]
[TD]170S-R58[/TD]
[TD]20635[/TD]
[TD][/TD]
[TD]Jan23[/TD]
[TD][/TD]
[TD]5.4[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]STD[/TD]
[TD][/TD]
[TD]210[/TD]
[TD]STD-210[/TD]
[TD]456456[/TD]
[TD][/TD]
[TD]Jan24[/TD]
[TD][/TD]
[TD]5.23[/TD]
[/TR]
[TR]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]STD[/TD]
[TD][/TD]
[TD]210[/TD]
[TD]STD-210[/TD]
[TD]1234[/TD]
[TD][/TD]
[TD]Jan27[/TD]
[TD][/TD]
[TD]5.1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]STD[/TD]
[TD][/TD]
[TD]210[/TD]
[TD]STD-210[/TD]
[TD]6969[/TD]
[TD][/TD]
[TD]Jan28[/TD]
[TD][/TD]
[TD]5.15[/TD]
[/TR]
</tbody>[/TABLE]

Second sheet (below) in workbook, named: QAQC - STD 210
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]STD-210[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Sample#[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]456456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]456456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]456456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1234[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6969[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Please note: Above in the tables the alphabet is representing the cells in excel, and the rows are the numbers, so for example, A1 in the second sheet is the STD-210 cell.

The formula that I have inserted in the second sheet, in column "B", is as follows:

=IFERROR(VLOOKUP(A$1,HG_2018_Sample_Tracking_MASTER!H2:I5246,2,FALSE),"")


How should I alter the formula to only find the STD-210 in the first sheet, and then populate the sample# associated with STD-210, into the "B" column in the second sheet?

Any insight would be greatly appreciated.

Thank you in advance.

 

[Scott T]

Try

Excel 2010
	A	B	C
1	STD-210
2	Date	Sample#	Results
3		456456
4		1234
5		6969
6
7
8
9
10
11
QAQC - STD 210

Cell Formulas
Range	Formula
B3	{=IF(ROWS(B$3:B3)>COUNTIF(HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,$A$1),"",INDEX(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6,SMALL(IF($A$1=HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,ROW(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6)-ROW(HG_2018_Sample_Tracking_MASTER!$I$2)+1),ROWS(B$3:B3))))}
B4	{=IF(ROWS(B$3:B4)>COUNTIF(HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,$A$1),"",INDEX(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6,SMALL(IF($A$1=HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,ROW(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6)-ROW(HG_2018_Sample_Tracking_MASTER!$I$2)+1),ROWS(B$3:B4))))}
B5	{=IF(ROWS(B$3:B5)>COUNTIF(HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,$A$1),"",INDEX(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6,SMALL(IF($A$1=HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,ROW(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6)-ROW(HG_2018_Sample_Tracking_MASTER!$I$2)+1),ROWS(B$3:B5))))}
B6	{=IF(ROWS(B$3:B6)>COUNTIF(HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,$A$1),"",INDEX(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6,SMALL(IF($A$1=HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,ROW(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6)-ROW(HG_2018_Sample_Tracking_MASTER!$I$2)+1),ROWS(B$3:B6))))}
B7	{=IF(ROWS(B$3:B7)>COUNTIF(HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,$A$1),"",INDEX(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6,SMALL(IF($A$1=HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,ROW(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6)-ROW(HG_2018_Sample_Tracking_MASTER!$I$2)+1),ROWS(B$3:B7))))}
B8	{=IF(ROWS(B$3:B8)>COUNTIF(HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,$A$1),"",INDEX(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6,SMALL(IF($A$1=HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,ROW(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6)-ROW(HG_2018_Sample_Tracking_MASTER!$I$2)+1),ROWS(B$3:B8))))}
B9	{=IF(ROWS(B$3:B9)>COUNTIF(HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,$A$1),"",INDEX(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6,SMALL(IF($A$1=HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,ROW(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6)-ROW(HG_2018_Sample_Tracking_MASTER!$I$2)+1),ROWS(B$3:B9))))}
B10	{=IF(ROWS(B$3:B10)>COUNTIF(HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,$A$1),"",INDEX(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6,SMALL(IF($A$1=HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,ROW(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6)-ROW(HG_2018_Sample_Tracking_MASTER!$I$2)+1),ROWS(B$3:B10))))}
B11	{=IF(ROWS(B$3:B11)>COUNTIF(HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,$A$1),"",INDEX(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6,SMALL(IF($A$1=HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,ROW(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6)-ROW(HG_2018_Sample_Tracking_MASTER!$I$2)+1),ROWS(B$3:B11))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[flaren]

You sir, are a stellar individual... It appears to be working... I cannot express how grateful I am!!

 

[flaren]

Try

Excel 2010

	A	B	C
STD-210
Date	Sample#	Results

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

QAQC - STD 210​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]{=IF(ROWS(B$3:B3)>COUNTIF(HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,$A$1),"",INDEX(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6,SMALL(IF($A$1=HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,ROW(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6)-ROW(HG_2018_Sample_Tracking_MASTER!$I$2)+1),ROWS(B$3:B3))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]{=IF(ROWS(B$3:B4)>COUNTIF(HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,$A$1),"",INDEX(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6,SMALL(IF($A$1=HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,ROW(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6)-ROW(HG_2018_Sample_Tracking_MASTER!$I$2)+1),ROWS(B$3:B4))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]{=IF(ROWS(B$3:B5)>COUNTIF(HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,$A$1),"",INDEX(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6,SMALL(IF($A$1=HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,ROW(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6)-ROW(HG_2018_Sample_Tracking_MASTER!$I$2)+1),ROWS(B$3:B5))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B6[/TH]
[TD="align: left"]{=IF(ROWS(B$3:B6)>COUNTIF(HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,$A$1),"",INDEX(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6,SMALL(IF($A$1=HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,ROW(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6)-ROW(HG_2018_Sample_Tracking_MASTER!$I$2)+1),ROWS(B$3:B6))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B7[/TH]
[TD="align: left"]{=IF(ROWS(B$3:B7)>COUNTIF(HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,$A$1),"",INDEX(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6,SMALL(IF($A$1=HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,ROW(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6)-ROW(HG_2018_Sample_Tracking_MASTER!$I$2)+1),ROWS(B$3:B7))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B8[/TH]
[TD="align: left"]{=IF(ROWS(B$3:B8)>COUNTIF(HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,$A$1),"",INDEX(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6,SMALL(IF($A$1=HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,ROW(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6)-ROW(HG_2018_Sample_Tracking_MASTER!$I$2)+1),ROWS(B$3:B8))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B9[/TH]
[TD="align: left"]{=IF(ROWS(B$3:B9)>COUNTIF(HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,$A$1),"",INDEX(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6,SMALL(IF($A$1=HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,ROW(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6)-ROW(HG_2018_Sample_Tracking_MASTER!$I$2)+1),ROWS(B$3:B9))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B10[/TH]
[TD="align: left"]{=IF(ROWS(B$3:B10)>COUNTIF(HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,$A$1),"",INDEX(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6,SMALL(IF($A$1=HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,ROW(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6)-ROW(HG_2018_Sample_Tracking_MASTER!$I$2)+1),ROWS(B$3:B10))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B11[/TH]
[TD="align: left"]{=IF(ROWS(B$3:B11)>COUNTIF(HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,$A$1),"",INDEX(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6,SMALL(IF($A$1=HG_2018_Sample_Tracking_MASTER!$H$2:$H$6,ROW(HG_2018_Sample_Tracking_MASTER!$I$2:$I$6)-ROW(HG_2018_Sample_Tracking_MASTER!$I$2)+1),ROWS(B$3:B11))))}[/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]

Good day,


The formula that you suggested appears to work, but only to an extent. I should have added in my original post that this data is going to be on going, perhaps with a thousand plus entries, and how it is formulated now it only does the first four values that are STD-210. Is there a way to manipulate the formula to account for on going data entries?

Again, thank you in advance.