Insert row if "september"
 

HI all

I need some help with this one.

I have a long list with investments which look like this:

A B C

Cars:

A6 40.000 September
Megane 35.000 September
A4 30.000 Oktober

Other:

Tool 10.000 December

and so on....

In a new sheet i want to write "september" in A1 and then all the rows with
"september" in the C column shall appear.

Is this possible

Insert row if "september"
 

Hi Morten

Why not use DataFilterAutofilter and use the dropdown on column C to
select September.

Regards

Roger Govier


Morten wrote:
HI all

I need some help with this one.

I have a long list with investments which look like this:

A B C

Cars:

A6 40.000 September
Megane 35.000 September
A4 30.000 Oktober

Other:

Tool 10.000 December

and so on....

In a new sheet i want to write "september" in A1 and then all the rows with
"september" in the C column shall appear.

Is this possible

Insert row if "september"
 

One play which would materialize your wish <g ..

Sample construct at:
http://cjoint.com/?leoWWIsJzS
Morten_misc.xls

Assume source data in Sheet1, cols A to C, from row2 down
(month is in col C)

Using an empty adjacent col, say col D

Put in D2: =IF(C2="","",IF(C2<Sheet2!$A$1,"",ROW()))
Copy down to say, D20, to cover the max expected data in col C
(Leave D1 empty)

In Sheet2
-----------
The month of interest will be input in A1, e.g.: September

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

Copy across to B2, fill down to B20
(cover the same range size as done in Sheet1's col D)

Sheet2 will return only the rows for the month input in A1,
with all results neatly bunched at the top

Adapt to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Morten" wrote in message
...
HI all

I need some help with this one.

I have a long list with investments which look like this:

A B C

Cars:

A6 40.000 September
Megane 35.000 September
A4 30.000 Oktober

Other:

Tool 10.000 December

and so on....

In a new sheet i want to write "september" in A1 and then all the rows
with
"september" in the C column shall appear.

Is this possible

hello morton/ answer is yes/ at present using public machine/ but will send reply with copy formula soonest/

Insert row if "september"
 

Hey Max

Thanks a lot, it really helped me out.

best regards

Morten

"Max" skrev:

One play which would materialize your wish <g ..

Sample construct at:
http://cjoint.com/?leoWWIsJzS
Morten_misc.xls

Assume source data in Sheet1, cols A to C, from row2 down
(month is in col C)

Using an empty adjacent col, say col D

Put in D2: =IF(C2="","",IF(C2<Sheet2!$A$1,"",ROW()))
Copy down to say, D20, to cover the max expected data in col C
(Leave D1 empty)

In Sheet2
-----------
The month of interest will be input in A1, e.g.: September

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

Copy across to B2, fill down to B20
(cover the same range size as done in Sheet1's col D)

Sheet2 will return only the rows for the month input in A1,
with all results neatly bunched at the top

Adapt to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Morten" wrote in message
...
HI all

I need some help with this one.

I have a long list with investments which look like this:

A B C

Cars:

A6 40.000 September
Megane 35.000 September
A4 30.000 Oktober

Other:

Tool 10.000 December

and so on....

In a new sheet i want to write "september" in A1 and then all the rows
with
"september" in the C column shall appear.

Is this possible

Insert row if "september"
 

Glad to hear that !
Thanks for the feedback
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Morten" wrote in message
...
Hey Max

Thanks a lot, it really helped me out.

best regards

Morten