Fomulas with variable # of worksheets

[Dave Burns]

I am building a workbook that is intended to capture information on new
feature requests for a system. The workbook will then consolidate
information from each new feature request and perform different kinds of
analysis to facilitate portfolio management (ranking views of each
feature to make a determination as to which features will be funded for
the next release). Analysis (and input for each feature) will include
things like value to the customer, value to the company, development
cost, marketing risk, development risk, deployment/support risk, company
strategy supported, etc.

I think I would like to use 3-D references to copy the individual
worksheets into the workbook that are created by the person requesting
the feature. The format of these will be standardized so that
everything used by the rest of the workbook is in the same relative
location.

I am looking for ways that once the information (feature worksheet) is
copied into the workbook that I can:

1) automatically populate a separate summary worksheet that shows a row
for each feature request and columns that have the required data items
from the new feature sheet. Ideally, all someone has to do is add the
new feature worksheet in the 3D range and the rest of the workbook
adjusts. I don’t know how to establish the formulas for getting this
data from a variable amount of worksheets that may be changing. I do
know how to calculate three D from the sheets that are added, but I need
a sheet that has all of the feature requests, to perform other analysis
and sorting.

2) Once I have the summary sheet built that will have a variable amount
of rows, is there a way (or would it be the same as request 1) that I
can build formulas in other sheets that reference row 2 through x?

Any suggestions will be appreciated?


** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Dave Peterson]

I wouldn't separate my data.

I think if you had a bunch of fields:

Category
category number
Title
description
Requestor
developer
date entered
date reviewed
status
(and all those other columns...)

Put each request into a single row. Use Alt-Enters to force linewrap in the
description column.

Then you can apply Data|filter|autofilter to show/hide anything you want.

You can sort the list whenever you want.

(I like to use a custom filter, "contains" to isolate anything assigned by me/to
me.)

Once you start populating other worksheets, it becomes a real bear to keep them
in sync.

If you ever _have_ to split them up, you can split them and then never use those
split up versions again. Go back and update the real master worksheet.

When you decide that you want to split them up automatically, I'd look at Debra
Dalgleish's sample files and try to steal some code from there:
http://www.contextures.com/excelfiles.html

Look for:

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- creates a list of unique items,
creates a sheet for each item, then replaces old data with current.
AdvFilterCity.xls 46 kb

and

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb