Display rows meeting criteria on another sheet & summarize them

[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

 

[JLatham]

Without more info I can't get real specific, but I think that possibly a
simple SUMIF() formula on the client sheets may work, or if you need multiple
criteria to be evaluated then you could probably use a variant of the
SUMPRODUCT() function, using the client name field on each sheet as the basic
test for a match from the General Entry spreadsheet. There is a lot written
about SUMPRODUCT available - a search for just that word will result in lots
of hits with info about it.

Consider this: on the GeneralEntry sheet you have client names in column A,
some numeric values in column B. (the use of A:A and B:B as ranges does away
with the need to know how many rows on the GeneralEntry sheet have data in
them).

The client sheets have the client's name in E1.

On a client sheet you could use either of these formulas to achieve the same
goal:

=SUMIF(GeneralEntry !A:A,E1,GeneralEntry !B:B)
or
=SUMPRODUCT(--(GeneralEntry !A:A=E1),--(GeneralEntry !B:B))

The SUMPRODUCT() will give the same result under these circumstances, but if
you need to go further and choose from the GeneralEntry sheet based on
multiple entries, it can be adapted for that also, whereas the SUMIF()
cannot. If you're wondering about the -- (double negative) in the
SUMPRODUCT() formula, it coerces results of logical tests to 0 when result is
false, 1 when result is true which allows it to be used in math operations,
as is needed at times like this.

Actually, as pointed out to me once by my good friend PapaDos, any math
operator in it will do the coercement necessary and sometimes makes it more
readable and understandable, consider:
=SUMPRODUCT((GeneralEntry !A:A=E1) * (GeneralEntry !B:B))
comes up with the same result, and what it says is
If a value in a row of column A on Sheet1 matches the entry on this sheet in
E1, return TRUE , otherwise it will return FALSE ... but the * symbol is
going to coerce TRUE to 1 and FALSE to zero. At the same time, return the
value on the same row on Sheet1 and multiply that times result of the other
test (1 x anything = anything) and when all is done, add the results of all
that multiplication together, which yields a total of all entries on sheet 1
with a match for E1 in column A. Change E1 and you change what's returned
from your GeneralEntry sheet.

 

[Showbear]

JLatham, thanks very much for your detailed assistance. Most helpful.

SUMIF has satisfied the most pressing requirements in this workbook to let
us add data from the client's rows. However it certainly doesn't do
everything we wanted to do.

What we needed for this task was a DSELECT function, which would pull rows
from the GeneralEntry worksheet based on criteria and insert them into a
client worksheet such that the sheet contains just the client's rows rather
than all rows, filtered for display, as we've had to do. Then on the client
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 excellent help and generosity.