Extracting information with conditional format to creat a smaller table

[ARamos2]

I am trying to create a table by pulling information out of a much larger table. In my column D, i have close to 400 rows with conditional formatting attached to them. I only want to pull information that conditional formats cells ">7". I also want to pull out the corresponding name from column A to create this smaller table. The catch of this though is the information can be constantly changing. The information currently in the large table is linked to another worksheet, and that worksheet information is linked to multiple other worksheets in the same workbook. Can this be done, and if so how??

I have attached a substantially scaled down version of a similar document I am working on. The large table can be found on the "Individual Control Count" tab. For the smaller table I am looking to make, the information would come from column D but also referencing the name that comes from column A. So the new table will should only have the control name (column a) and the number that is extracted from column d (whether it is 2 or 3 in test example). Anything highlighted in red is what I need to pull out and make a smaller table. This table will go into another worksheet within the workbook. I just need to make sure that if anything changes from the any of the 3 test tabs that affect column D in the Individual control count tab, that the small table is updated with this information.

[JohnTopley]

It is difficult (if not impossible!) to answer without seeing a sample file (not image) , showing expected results.

To upload a file, click "Go Advanced" then "Manage Attachments"

[Tony Valko]

Sounds like you want something like this.

Data Range

	A	B	C	D
1	Name	Value		Name
2	Pete Zahutt	10		Pete Zahutt
3	Sue Blue	1		Adam Zapple
4	Justin Case	3		Frank Zappa
5	Rusty Steele	5		Rick O'Shea
6	Adam Zapple	10
7	Frank Zappa	10
8	Rick O'Shea	8
9	Brooke Rivers	6
10	Ben Dover	3
11	Doris Shutt	1
12	------	------	------	------

This array formula** entered in D2:

=IFERROR(INDEX(A:A,SMALL(IF(B$2:B$11>7,ROW(B$2:B$11)),ROWS(D$2:D2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

[ARamos2]

That is pretty close to what I want Tony Valko. but I would still need the value from column b next to the name in column d. I uploaded a small sample similar to the Workbook I am working in.

[Tony Valko]

I would still need the value from column b next to the name in column d.

Ok, just add this formula to E2 and copy down:

=IF(D2="","",VLOOKUP(D2,A$2:B$11,2,0))

Data Range

	A	B	C	D	E
1	Name	Value		Name	Value
2	Pete Zahutt	10		Pete Zahutt	10
3	Sue Blue	1		Adam Zapple	10
4	Justin Case	3		Frank Zappa	10
5	Rusty Steele	5		Rick O'Shea	8
6	Adam Zapple	10
7	Frank Zappa	10
8	Rick O'Shea	8
9	Brooke Rivers	6
10	Ben Dover	3
11	Doris Shutt	1
12	------	------	------	------	------

[JohnTopley]

Worksheet "Extract"

in A2

=IFERROR(INDEX('Individual Control Count (2)'!$A$5:$A$100,SMALL(IF(('Individual Control Count (2)'!$D$5:$D$100=2)+('Individual Control Count (2)'!$D$5:$D$100=3),ROW($A$5:$A$100)-ROW($A$5)+1,""),ROWS($A$5:A5))),"")

or

=IFERROR(INDEX('Individual Control Count (2)'!$A$5:$A$100,SMALL(IF(('Individual Control Count (2)'!$D$5:$D$100={2,3}),ROW($A$5:$A$100)-ROW($A$5)+1,""),ROWS($A$5:A5))),"")

Enter with Ctrl+Shift+Enter

Copy down until you get blank cells

in B2

=IF($A2="","",VLOOKUP($A2,'Individual Control Count (2)'!$A$5:$I$100,4,0))

[ARamos2]

Thank you so much! I was able to do it that way!

[Tony Valko]

You're welcome. Thanks for the feedback!