PDA

View Full Version : Report from Excel



NeilM
02-06-2007, 03:49 PM
I have a spreadsheet the first column of which contains ID numbers the second column contains numeric values e.g. ?1? or ?5? or ?27? etc. I would like to run a macro which reports in the form of a list into another worksheet all the ID numbers in column A above a specified value and displays the value from column B relating to that ID number alongside. Any ideas?:help

Bob Phillips
02-06-2007, 04:05 PM
=IF(ISERROR(SMALL(IF($B$1:$B$20>20,ROW($B$1:$B$20),""),ROW(B1))),"",
INDEX($A$1:$A$20,SMALL(IF($B$1:$B$20>20,ROW($B$1:$B$20),""),ROW(B1))))

This is an array formula, so commit with Ctrl-Shift-Enter, not enter, and copy down.