how do i create a formula for selected range?
 

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?

Hi!

Assume this data is in Sheet1 A1:D4

Row 1 are the headers:

Foundation ----------- cost 1 -----------cost 2

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

"Yin" wrote in message
...
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?

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

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
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Yin" wrote in message
...
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?

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Thank you.. it's really helpful.

"Max" wrote:

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

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
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Yin" wrote in message
...
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?

You're welcome !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Yin" wrote in message
...
Thank you.. it's really helpful.