Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
Quote:
|
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I insert a reference to lookup and insert a name | New Users to Excel | |||
Simultaneouly insert of rows or col. - Ajit | Excel Discussion (Misc queries) | |||
insert picture | Excel Discussion (Misc queries) | |||
Insert Line Macro | Excel Discussion (Misc queries) | |||
Challenging Charting | Charts and Charting in Excel |