I want to produce a workbook-based report that others can use to populate a
list of risks. I have up to 30 rows of data in 10 columns in a risk databank.
Each row describes a risk and associated management data. I would like to be
able to produce a subset list of risks for a new report for a particular
activity by selecting specific rows from the databank. The subset list should
occupy consecutive rows without gaps. I would need to be able to extend the
databank as new risks are identified.
A simple method of selection is required, e.g. ticks in checkboxes.
Some way of sorting the resulting list might be helpful, e.g. to put most
severe risks first.
Can this be done in Excel 2003? It's possible to use AutoFilter but setting
and adapting custom filters is fiddly and beyond the experience of many who
will be using this.
Any suggestions will be gratefully received.
--
Richard

Search the web and raise money for charity at www.everyclick.com

Sounds like you need to provide users with a button and a macro.

See Ron de Bruin's site for copying the results of an autofilter to a new
worksheet.

http://www.rondebruin.nl/copy5.htm

Add an inputbox to allow users to type in the criterion to filter on.

mycrit = InputBox("Enter a criterion")
rng.AutoFilter Field:=1, Criteria1:=mycrit

You can add more code to sort the new sheet after copying is done to that
sheet.


Gord Dibben MS Excel MVP


On Mon, 9 Mar 2009 09:03:02 -0700, RichUE
wrote:

I want to produce a workbook-based report that others can use to populate a
list of risks. I have up to 30 rows of data in 10 columns in a risk databank.
Each row describes a risk and associated management data. I would like to be
able to produce a subset list of risks for a new report for a particular
activity by selecting specific rows from the databank. The subset list should
occupy consecutive rows without gaps. I would need to be able to extend the
databank as new risks are identified.
A simple method of selection is required, e.g. ticks in checkboxes.
Some way of sorting the resulting list might be helpful, e.g. to put most
severe risks first.
Can this be done in Excel 2003? It's possible to use AutoFilter but setting
and adapting custom filters is fiddly and beyond the experience of many who
will be using this.
Any suggestions will be gratefully received.

Thanks, Gord. I can see how Ron's solutions might be useful, but in my
application the user would need to be able to inspect the risks in the
databank in order to select them. For example, if a column in the databank
contained a checkbox, the user could tick those risks that were relevant. An
"Apply" function could then be invoked using a button to copy the marked
risks to a target workbook.
I am not a programmer - could one of Ron's solutions be adapted for this
purpose?
--
Richard

Search the web and raise money for charity at www.everyclick.com


"Gord Dibben" wrote:

Sounds like you need to provide users with a button and a macro.

See Ron de Bruin's site for copying the results of an autofilter to a new
worksheet.

http://www.rondebruin.nl/copy5.htm

Add an inputbox to allow users to type in the criterion to filter on.

mycrit = InputBox("Enter a criterion")
rng.AutoFilter Field:=1, Criteria1:=mycrit

You can add more code to sort the new sheet after copying is done to that
sheet.


Gord Dibben MS Excel MVP


On Mon, 9 Mar 2009 09:03:02 -0700, RichUE
wrote:

I want to produce a workbook-based report that others can use to populate a
list of risks. I have up to 30 rows of data in 10 columns in a risk databank.
Each row describes a risk and associated management data. I would like to be
able to produce a subset list of risks for a new report for a particular
activity by selecting specific rows from the databank. The subset list should
occupy consecutive rows without gaps. I would need to be able to extend the
databank as new risks are identified.
A simple method of selection is required, e.g. ticks in checkboxes.
Some way of sorting the resulting list might be helpful, e.g. to put most
severe risks first.
Can this be done in Excel 2003? It's possible to use AutoFilter but setting
and adapting custom filters is fiddly and beyond the experience of many who
will be using this.
Any suggestions will be gratefully received.