Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
producing a report via a keyword - filtering and macros not suitab
help needed please........ I am not a formula expert so bear with me please.
I know filtering is available but I am trying to save effort as data goes into a seperate report. Basically I have a skill matrix spread worksheet I need to extract data from using a keyword search so: the dbase sheet is designed as: Col a Col B through to Col Z Row 1 Skill title i.e. Excel Row 2 Name Knowledge level between 1 to 5 i.e. 2 Note there is about 100 rows of names. example therefore is: Excel word Colin 2 3 Sarah 1 5 Pete 0 3 What I need to do is on a seperate worksheet is from a drop down list i.e a1, select a title (listing the titles from col b through to col z) in b3 and c3 downwards, List the names and that persons knowledge value i.e. Drop down selection is [excel] return in the report is Colin 2 Sarah 1 You notice Pete is not reported on. Can anyone help please...... Cheers UKMAN1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
producing a report via a keyword - filtering and macros not suitab
Hi
Array Entered* in B3: =INDEX(dbase!$A$2:$Z$100,SMALL(IF(OFFSET( dbase!$A$2:$Z$100,,MATCH($A$1,dbase!$B$1:$Z$1,0),, 1)<0,ROW(dbase!$A$2:$Z$100)-CELL("ROW", dbase!$A$2:$Z$100)+1),ROW()-ROW($B$3)+1),1) Array Entered in C3: =INDEX(dbase!$A$2:$Z$100,SMALL(IF(OFFSET( dbase!$A$2:$Z$100,,MATCH($A$1,dbase!$B$1:$Z$1,0),, 1)<0,ROW(dbase!$A$2:$Z$100)-CELL("ROW", dbase!$A$2:$Z$100)+1),ROW()-ROW($B$3)+1), MATCH($A$1,dbase!$B$1:$Z$1,0)+1) Copy B3:C3 down as far as required. It is possible to do this without Array Entering, but the formulae become twice as long... In B3: =INDEX(dbase!$A$2:$Z$100,SMALL(INDEX((OFFSET(dbase !$A$2:$Z$100,,MATCH($A$1,dbase!$B$1:$Z$1,0),,1)<0 )*(ROW(dbase!$A$2:$Z$100)-CELL("ROW",dbase!$A$2:$Z$100)+1),),COUNTIF(OFFSET( dbase!$A$2:$Z$100,,MATCH($A$1,dbase!$B$1:$Z$1,0),, 1),0)+COUNTIF(OFFSET(dbase!$A$2:$Z$100,,MATCH($A$1 ,dbase!$B$1:$Z$1,0),,1),"")+ROW()-ROW($B$3)+1),1) in C3: =INDEX(dbase!$A$2:$Z$100,SMALL(INDEX((OFFSET( dbase!$A$2:$Z$100,,MATCH($A$1,dbase!$B$1:$Z$1,0),, 1) <0)*(ROW(dbase!$A$2:$Z$100)-CELL("ROW", dbase!$A$2:$Z$100)+1),),COUNTIF(OFFSET( dbase!$A$2:$Z$100,,MATCH($A$1,dbase!$B$1:$Z$1,0),, 1),0)+COUNTIF(OFFSET(dbase!$A$2:$Z$100,,MATCH($A$1 , dbase!$B$1:$Z$1,0),,1),"")+ROW()-ROW($B$3)+1), MATCH($A$1,dbase!$B$1:$Z$1,0)+1) *After typing the formula use Ctrl+Shift+Enter instead of just Enter. HTH Steve D. "UKMAN" .(donotspam) wrote in message ... help needed please........ I am not a formula expert so bear with me please. I know filtering is available but I am trying to save effort as data goes into a seperate report. Basically I have a skill matrix spread worksheet I need to extract data from using a keyword search so: the dbase sheet is designed as: Col a Col B through to Col Z Row 1 Skill title i.e. Excel Row 2 Name Knowledge level between 1 to 5 i.e. 2 Note there is about 100 rows of names. example therefore is: Excel word Colin 2 3 Sarah 1 5 Pete 0 3 What I need to do is on a seperate worksheet is from a drop down list i.e a1, select a title (listing the titles from col b through to col z) in b3 and c3 downwards, List the names and that persons knowledge value i.e. Drop down selection is [excel] return in the report is Colin 2 Sarah 1 You notice Pete is not reported on. Can anyone help please...... Cheers UKMAN1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
filtering dates in a Pivot Table Report Filter | Excel Discussion (Misc queries) | |||
Filtering with keyword | Excel Discussion (Misc queries) | |||
PIVOT - Reference a cell outside the table for report filtering | Excel Discussion (Misc queries) | |||
Filtering an Excel Report | Excel Worksheet Functions | |||
filtering and macros | Excel Discussion (Misc queries) |