how do i create a formula for selected range?

Y

Yin

I have a spreadsheet that contain data by category. Example:

Foundation ----------- cost 1 -----------cost 2
Foundation - guy 1 ---- $100 ---------- $150
Foundation - guy 2 ---- $120 ---------- $160 -- X
Foundation - guy 3 --- $130 ---------- $190

If I put an "X" or maybe "highlight" on column Foundation - guy2 for
example, I would like to populate his information to a different sheet.

Do you know how to go about it?
 
B

Biff

Hi!

Assume this data is in Sheet1 A1:D4

Row 1 are the headers:
Foundation ----------- cost 1 -----------cost 2
Click to expand...

Column D contains the "X".

On another sheet, say, in A1, enter this formula and copy across to C1:

=INDEX(Sheet1!A2:A4,MATCH("X",Sheet1!$D2:$D4,0))

Biff
 
M

Max

One way ..

Assume source table is in Sheet1 cols A to D, data from row2 down, and col E
is where you'll be marking the "X"

Using an adjacent empty col, col F?
Put in F2: =IF(E2="X",ROW(),"")
Copy F2 down to say, F100,
to cover the max expected data range in the source table

In Sheet2
---------
Paste the same col headers into A1: D1
Foundation ----------- cost 1 -----------cost 2
Click to expand...

Put in A2:
=IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,
MATCH(SMALL(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0)))

Copy A2 across to D2, fill down to D100
(cover the same range as was done in col F in Sheet1)
Format the cost cols as currency

Sheet2 will return the desired results, i.e. only those lines marked with an
"X" in col E in Sheet1, all neatly bunched at the top
 
M

Max

The suggested set-up assumes there could be multiple lines with "X"'s marked
in col E in Sheet1, and you want to return all these lines in Sheet2
 
Y

Yin

Thank you.. it's really helpful.

Max said:
One way ..

Assume source table is in Sheet1 cols A to D, data from row2 down, and col E
is where you'll be marking the "X"

Using an adjacent empty col, col F?
Put in F2: =IF(E2="X",ROW(),"")
Copy F2 down to say, F100,
to cover the max expected data range in the source table

In Sheet2
---------
Paste the same col headers into A1: D1

Put in A2:
=IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,
MATCH(SMALL(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0)))

Copy A2 across to D2, fill down to D100
(cover the same range as was done in col F in Sheet1)
Format the cost cols as currency

Sheet2 will return the desired results, i.e. only those lines marked with an
"X" in col E in Sheet1, all neatly bunched at the top
--
Rgds
Max
xl 97
Click to expand...
 
Top