Building a table with formulas rather than a macro

[Daniel Bonallack]

In column A, I have a list of names (Abbott, Addas, Amling, Benner...)
In column B, I have the sector they are in (Media, HealthCare, Media,
Telecom...)
In column C, I have their sales (24.5, 26.4, 30.0, 12.1...)

On another sheet, when I select a sector (say Media, in cell D1), I want to
be able to recalculate and show Name (Col A) and Sales (Col B), just for the
Media bankers

Is this possible? Also, Media might have 8 bankers, but Technology might
have 4 bankers, so the formula would need to be able to handle that variance
too.

Thanks in advance for any help

Daniel Bonallack

 

[Jason Morin]

If Sheet1 holds the data and C1 on the new sheet contains
the sector, then place this in A1 of the new sheet, press
ctrl/shift/enter, and fill down:

=INDEX(Sheet1!$A$1:$A$12,SMALL(IF(Sheet1!
$B$1:$B$12=$C$1,ROW(Sheet1!$A$1:$A$12)),ROW()))

Put this in B1 and fill down:

=VLOOKUP(A1,Sheet1!A:C,3,0)

HTH
Jason
Atlanta, GA

 

[Arvi Laanemets]

Hi

On first sheet, add a column left to your table (so names will be in column
B, etc.). I assume 1st row contains column headers, so actual data start at
row 2.

Into cell A2 enter the formula
=IF(AND(B2=Sheet2!$D$1,B2<>""),COUNTIF(B$2:B2,Sheet2!$D$1),"")
and copy down at least for same number of rows, as in your table. You get
all records from selected sector numbered.

On Sheet2, starting from some row, use VLOOKUP function to look for 1st,
2nd, etc row values in table on sheet 1. Like:
A4=IF(ISERROR(VLOOKUP(ROW()-3,Sheet1!$A$2:$D$100,2,0)),"",VLOOKUP(ROW()-3,Sh
eet1!$A$2:$D$100,2,0))
B4=IF(A4="","",VLOOKUP(ROW()-3,Sheet1!$A$2:$D$100,3,0))
C4=IF(A4="","",VLOOKUP(ROW()-3,Sheet1!$A$2:$D$100,4,0))
and copy down for enough number of rows.

My advise is to define the table (added column included) as dynamic range,
p.e. MyTable. Then formulas on second sheet will be like this:
A4=IF(ISERROR(VLOOKUP(ROW()-3,MyTable,2,0)),"",VLOOKUP(ROW()-3,MyTable,2,0))
B4=IF(A4="","",VLOOKUP(ROW()-3,MyTable,3,0))
C4=IF(A4="","",VLOOKUP(ROW()-3,MyTable,4,0))

 

[Daniel Bonallack]

Thanks Jason,
Works just fine.

Daniel
(Thanks also to Arvi for replying).