Building a table with formulas rather than a macro
 

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

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

-----Original Message-----
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
.

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,Shee t2!$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))

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Daniel Bonallack" wrote in
message ...
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

Thanks Jason,
Works just fine.

Daniel
(Thanks also to Arvi for replying).

"Jason Morin" wrote:

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

-----Original Message-----
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
.