| Home |
| Search |
| Today's Posts |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rows to Named Sheets
Thankyou
"Harlan Grove" wrote: Steved wrote... .... I've a summary sheet called Starttrip, In Col A I've got names "Peter", "Stan", "Maranda", "Stewart", "Carol". I've got sheets named "Peter", "Stan", "Maranda", "Stewart", "Carol". =IF(Summary!A6="Peter", ROW(),"") This is in Sheet named "Peter" So this should really be =IF(Starttrip!A6="Peter",ROW(),"") instead? What is required in the formula below to reconize "Peter" please, or what is required . =IF(ISNUMBER(SMALL(IF($B$2:$B$106="","",ROW($B$2: $B$106)), ROW(1:1))),INDIRECT("Starttrip!"&CHAR(COLUMN()+64 ) &SMALL(IF($B$2:$B$106="","",ROW($B$2:$B$106)),ROW (1:1))),"") At the very least additional details about where this formula would be (presumably it's in worksheet 'Peter') and what it's supposed to do (looks like it's fetching values in the ROW()'th record for Peter from worksheet 'Starttrip'). If the top-left cell containing such formulas were C2, you could use C2: =IF(ROWS(C$2:C2)<=COUNTIF($B$2:$B$106,"<"),INDEX( Starttrip!C:C, SMALL(IF($B$2:$B$106<"",ROW($B$2:$B$106)),ROWS(C$ 2:C2))),"") You can fill this right then down as far as needed. Rule of thumb: nearly all INDIRECT(x&COLULN(..)&..&ROW(..)) constructs can be replaced with simpler INDEX constructs. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do you insert rows into multiple sheets in a workbook? | Excel Discussion (Misc queries) | |||
| (Unsuccessfully!) Unhiding Rows in Excel 2003 | Excel Worksheet Functions | |||
| AHHHH-Get Data from Multiple Excel workbooks | Excel Discussion (Misc queries) | |||
| Exact rows, multiple sheets | Excel Discussion (Misc queries) | |||
| Adding Rows to Master Sheet | New Users to Excel | |||
Threaded Mode
