Select rows meeting criteria into another worksheet?

[Showbear]

Is this possible?

Users will enter records of work into a GeneralEntry spreadsheet. Each
record includes a Client field, which they fill using a dropdown. Records
are entered as work is completed, so client rows are not grouped together.

A set of additional worksheets, one for each client, automatically display
only the rows from GeneralEntry which contain the client's name in the
Client field. Each of the client sheets should perform summary calculations
on just the rows appearing in the client sheet, and display the results
above the detail rows.

I have tried using filtered lists on the client sheets. That works fine for
displaying only the desired client's rows from GeneralEntry.

However I can't figure out how to create summary calculations that will use
only the displayed rows. The number of rows varies, so summary calculations
on a range of cells which might display data are required. But using
filtered lists, summary calculations on the hours range, for example,
produce the total of all hours in GeneralEntry rather than the total of the
visible rows in the client sheet.

Thanks in advance for any ideas, advice, etc

 

[jan]

Showbear,

Is this what you are asking for:

=SUBTOTAL(9,A2:A50)

The 9 is about the Sum of the range.
Using 3 instead will give the Count of the cells that are not empty .
Look into the help file for other SUBTOTALs.

Jan

 

[Showbear]

Thanks Jan. SUBTOTAL also sums the entire range, rather than just the
visible rows in the range. Using SUMIF, which scans the range for rows
matching criteria before adding the value, I've been able to get part of
what we wanted.

What I needed for this task was a DSELECT function, which would select rows
from a list based on criteria and insert them into another worksheet. Then
on the other worksheet we could define ranges large enough to accommodate
the largest set of data which might be inserted there, and use existing
sheet capability and functions to summarize, analyze, and report on the
data.

Thanks again for your help.

 

[Roger Govier]

Hi

What Jan suggested to you is correct.
Subtotal does only deal with VISIBLE rows in a range, not all rows.
If, of course, no filter is applied, then all rows will be visible and
Subtotal will return either the Sum or Count or whatever other measure
has been requested from the entire range.

The moment a filter is applied, then the value returned by Subtotal will
adjust to deal only with the visible rows