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

A

B

C

D

E

F

G

H

I

J

K

L

M

Type

From

To

Area

Level

Heading

Round

String name

Sample#

By

Date

Shift

Result

C

170S

N

R60

170S-R60

5555

Jan22

5.7

M

170S

N

R58

170S-R58

20635

Jan23

5.4

C

STD

210

STD-210

456456

Jan24

5.23

M

STD

210

STD-210

1234

Jan27

5.1

C

STD

210

STD-210

6969

Jan28

5.15

<tbody>
</tbody>

Second sheet (below) in workbook, named: QAQC - STD 210

A	B	C
STD-210
Date	Sample#	Result
	456456
	456456
	456456
	1234
	6969

<tbody>
</tbody>

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
1	STD-210
2	Date	Sample#	Results
3		456456
4		1234
5		6969
6
7
8
9
10
11

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

QAQC - STD 210​

Array Formulas Cell 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))))} <thead> </thead><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>

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.