Select rows meeting criteria into another worksheet?

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 


0
nospam7515 (2084)
3/10/2007 2:39:52 PM
excel 39879 articles. 2 followers. Follow

3 Replies
348 Views

Similar Articles

[PageSpeed] 46

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 


0
jg8091 (25)
3/10/2007 3:36:51 PM
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.


"jan" <jg@releerf.nl> wrote in message 
news:OI60tnyYHHA.4552@TK2MSFTNGP05.phx.gbl...
> 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
> 


0
nospam7515 (2084)
3/11/2007 8:24:15 AM
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

-- 
Regards

Roger Govier


"Showbear" <nospam@nospam.com> wrote in message 
news:umklha7YHHA.1296@TK2MSFTNGP02.phx.gbl...
> 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.
>
>
> "jan" <jg@releerf.nl> wrote in message 
> news:OI60tnyYHHA.4552@TK2MSFTNGP05.phx.gbl...
>> 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
>>
>
> 


0
roger5293 (1125)
3/11/2007 2:34:31 PM
Reply:

Similar Artilces:

make colour in rows stay
How can I lock the fill colours in a row even when I change around the data or sort alphabetically? Thanks and God Bless, Harley Use CF http://www.cpearson.com/excel/banding.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Crowraine" <Crowraine@discussions.microsoft.com> wrote in message news:ED158D7D-62AB-4DA8-A948-2A6556D8047C@microsoft.com... > How can I lock the fill colours in a row even when I change around the data > or sort alphabetically? > > Thanks and God Bless, > Harley ...

Update one table from another
I am trying to update one table that has one record for each employee(table 1) with available vacation time. The other table records every time off request(table 2) and how much time they want off. I have the update query and it works fine. The problem is that everytime it is ran every requested time off amount(from table2) is subtracted from the available time(table1) again and again. I want the records for requested time(table2) to update the employee available time off(table1) only once, but keep the records on the table as that is the basis for a report. Thank You, Brett ----=...

The Sum from 1 worksheet cell to another worksheet cell
the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula qwerty: To sum the value on Sheet1, cell A10 with the cell value Sheet2, cell B20, enter =Sheet1!A10 + Sheet2!B20 (or you can enter '=' sign and click on A10, then enter the plus sign and click on B20) jeff >-----Original Message----- >the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula >. > ...

Copying data from one chart to another
I have many graphs - all plotting on similar scales but using different data. Is there any way I can simply copy one set of data from one graph and paste it into another graph so that I can avoind going through all the hassle plotting each curve again? I want to have graphs showing different combinations of the same data and have hundreds of curves to plot so this could be a huge timesaver... Cheers. -- Alan_Partridge ------------------------------------------------------------------------ Alan_Partridge's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29295 V...

Forms & Worksheets
I want to create a form in word that will automatically add entered info into an excel worksheet? Is this possible Charlene, Setup you data in ordered columns, with headings in row 1, then menu Data>Form. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Charlene" <anonymous@discussions.microsoft.com> wrote in message news:A8A03DD4-85DB-4F80-B94A-C568FB95531E@microsoft.com... > I want to create a form in word that will automatically add entered info into an excel worksheet? Is th...

One Entry to Multiple Rows
I have data that looks like this: X1 | Y1 Y2 Y3 Y4 X2 | Y4 Y5 Y6 Y7 And I need to get to: X1 | Y1 X1 | Y2 X1 | Y3 X1 | Y4 X2 | Y4 ...... etc. I can change the 2nd row's entries to more columns, but that doesn't seem to get me much closer to the needed format (and there are thousands of lines so I'd rather not do it manually). Any ideas? should do it. change mc to suit '===== Option Explicit Sub lineemup() Dim mc As Long Dim mr As Long Dim i As Long Dim lc As Long mc = 3 'col c mr = 1 For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row lc ...

selecting a cell
I seem unable to select a single cell, or a single row--click on one in the normal manner, and the two below also highlight, then delete or whatever command is given. If I input a number/text, that just goes into the one cell. tapping F8 increases this to two wide and three high automatically selected. Also, very slow to do almost anything. Thanks Pat, Are you by any chance using Excel 2007? If so there is a known bug that causes multiple cell selection and I understand this has been reported to Microsoft. If you take the zoom level up and down this is reported to cl...

Compare entries in 2 worksheets and list what does not match
Good Day All; I have 2 Excell works sheets with approx 14000 rows each. What I would like to do is compare both lists and get a 3rd list that shows what entries do not match. Is there a simple way to do ythis in Excel Thanks All Chomp Assuming A1 should equal A1 in the other sheet... =IF(Sheet1!A1=Sheet2!A1,"",Sheet!A1&" does not match "&Sheet2!A1) Auto-Filter for non-blanks. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "The Chomp" wrote: > Good Day All; > > I have 2 ...

Importing Messages From Another Mail Program
I just set up outlook express 6.00.2900.2180 for XP SP2 and wish to import my messages and folders from Netscape 7.0 mail and when I follow the instructions and click on the netscape Local Folder inbox file, I get a message from outlook that says there aren't any messages in the file. I tried to mail the files as attachments but that didn't work. Is there some other way to export these messages .to outlook express. Ask in an Outlook Express forum. This is an Microsoft Office Outlook forum. Outlook Express is family of Internet Explorer and Outlook of the Office family. Here is...

Cross corporation meeting invites
Hi Got a little query regarding sending meeting requests from one company to another. The example: Corporation A has a user who needs to invite someone from Corp B to a meeting. He sets up a standard meeting invite and sends. The question: Should the user in Corp B see the standard Accept/Tentative/Decline options as currently these messages appear to be delivered as standard mail messages and have regular Reply/Reply-All/Forward options. I'm not certain whether SMTP can actually translate this or whether it's normal behaviour. I also don't know whether this is an outlook ...

Somehow I created a Macro in a worksheet.
I created a macro in an Excel worksheet somehow. I didn't try to, it just happened. Now everytime I open that workbook, it asks me if I want to run the macro, disable it, etc. How the hell do I get rid of the macro? It doesn't show up under tools, macros. And it apparently doesn't do anything either because I can disable it and nothing different happens. Who invented this system anyway? Thanks, V When you record a macro, a module is created to store the macro code. There are instructions here for removing the module that is causing the prompt to appear: http://www.c...

Excel ask duplicate NAMES when duplicate a worksheets
I have added a NAME called "Above" where point to the cell just above the current cell. The formula is "=INDIRECT("R[-1]C",)" In some workbook, when I duplicate a worksheets, this name will remain silent and work ok. But in some workbooks, when I first duplicate a worksheets, the same name ABOVE will be duplicate and a new local name (belongs to that new worksheet) will be created. If I further duplicate that new worksheets in to a new worksheets, the third worksheets will be warned that a dupicate NAME is existed and ask whether refer to another name or use a ne...

first time linking worksheets need some info and help
I'm trying to link 4 cells with dates in them to be inputted into the second sheet for calculation. BUT the info is in a cycle. So basically I need it to link and stay. But when I do the next cycle, I need the info to change. I do have a column that is counting the cycles. So can I make it so it'll link the info until the cycle number changes? (a Formula???) if so how do i put a link and formula together. Also if this is figured out,can you possibly link a cell to two different worksheets within a workbook? Thank You Bryan Bloom Hi, Can you give an example as to what you are...

How do add another code to a current one?
I have this following code to make the rows changed based on the critea in column 16, and I need add A "Red, Yellow, Green" for status to only one column 30 at the end of the spreadsheet. How do I add another code? I keep getting an error.. Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range, clr As Long For Each c In Target.Cells If c.Column = 16 Then Select Case c.Value Case "Analyze": clr = RGB(204, 255, 255) Case "Build ": clr = RGB(204, 255, 255) Case ...

How do I import data from lotus123 & maintain formulas/worksheets
I am trying to convert several complex Lotus 123 workbooks with formulas into Excel 2003. How do I do this and maintain my formulas and the individual tabs (worksheets). hi, if the lotus file is a wks version or earlier, xl should open it and let you save it as an xl file. if the lotus file is a 123 version or higher, you can open the file in lotus and save it as an xl file. if you don't have lotus, find someone who does. "Ineluctable" wrote: > I am trying to convert several complex Lotus 123 workbooks with formulas into > Excel 2003. How do I do this and maintai...

Create individual files from a row
I have an excel file with several thousand entries, which contain data in several columns. I would like to be able to create an individual xml or html file for each row, but with predifined formatting around so Mr A bloggs, A street, A town, AA1 1AA Could become Abloggs.html <head></head> <name>Mr A bloggs</name> <street>A street</street> Any other info here as well </html> etc. Is this possible and any suggestions how? thanks, Graham. -- GrahamN ------------------------------------------------------------------------ GrahamN's Profile: ...

Determine number of rows with data
Hi I am using the macro below to pull some data from an external workbook. The 2 issues I need to sort are: 1. The number of rows in the external workbook can vary. How do I amend this code to pull all of the rows with data? 2. The number of rows in the autofill also may vary. How do I autofill only the number of rows required? i.e the number of rows in column A that contain data. 'Lookup Previous Month Sales Columns("K:K").Select Selection.NumberFormat = "General" Range("K4").Select Selection.FormulaArray = _ "=S...

Remote access to another company's Outlook calendar?
Hello! I have one client on Exchange 2003 that wants to access the calendar of an employee (consultant?) at another company running Exchange (version not known yet). How can the remote company share this person's calendar with my client? He would need to access it and add appointments to it. Thank you for the help! Gregg Hill On Mon, 30 Aug 2004 22:15:24 -0700, "Gregg Hill" <bogus@nowhere.com> wrote: >Hello! > >I have one client on Exchange 2003 that wants to access the calendar of an >employee (consultant?) at another company running Exchange (version no...

Copy/paste range of rows between 2 dates...
Hi! I have a sheet called data which act as a database. The column A has the dates. In order to create customized chart in a userform, for different range of data(i.e from column D, G and M...), I'd like to select a range of rows that are between 2 dates and create the charts accordingly. Or copy to range to another sheet and then create the charts. I am not so advanced in VBA and any help would be greatly appreciated. Thanks! Greg ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi ...

Highlight color for selected items
How can I find out what color is used for highlighting selected items (for example in Windows Explorer)on a users computer? Normally it is a blue color, but a user can change that. "Urban Olars" <anonymous@discussions.microsoft.com> wrote in message news:071c01c3c941$3db2c890$a401280a@phx.gbl... > How can I find out what color is used for highlighting > selected items (for example in Windows Explorer)on a users > computer? > Normally it is a blue color, but a user can change that. See if it's GetSysColor/COLOR_HIGHLIGHT. -- Jeff Partch [VC++ MVP] Take a ...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

Routing Restrictions button unavailable (gray) no matter what options selected in IMS-->Routing tab
I am running Exchange 5.5 SP4. In Connections-->IMS-->Routing Tab, my "Routing Restrictions" button is unavailable (greyed-out) no matter what options I select. Any suggestions? Thank you. Try installing Exch Admin prog on other machine, apply SP4 and see if you can change it from there. Post back with details. ryanadmin wrote: > I am running Exchange 5.5 SP4. In Connections-->IMS-->Routing Tab, my > "Routing Restrictions" button is unavailable (greyed-out) no matter > what options I select. Any suggestions? Thank you. ...

Can you link 2 worksheets together?
Say i have one worksheet and on my second one I want to reference cells from the first one? is there a formula for that>? To create a simple link: Select a cell in the second worksheet Type an equal sign Select the first sheet Click on the cell that you want to link Press the Enter key. Alesha wrote: > Say i have one worksheet and on my second one I want to reference cells from > the first one? is there a formula for that>? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Copy of all in/out mail in another mailbox
My management in intrested to receive all the in/out mails of all the mailnboxes in another mail box to check what other emplyees are sending and receive. Is it possible in MS Exchange 2000 or 2003, if yes how? Yes, it's possible. See this: http://www.msexchange.org/tutorials/MF011.html -- Neil Hobson Exchange MVP For Exchange news, links and tips, check: http://www.msexchange.co.uk "Jaffar Ali Sayyed" <jaffar@maruetechnologies.com> wrote in message news:045f01c3c862$f2d436d0$a401280a@phx.gbl... > My management in intrested to receive all the in/out mails > o...

underline in row not repeating
It seems as if my bottom of the cell outline is not repeating on subsequent pages. I have a few header rows on a sheet. The bottom row has a bottom line. I'm talking about cell outlines, not underlining of words in the cell. Anyway, I've told the print option to repeat the first 3 rows on following pages but the bottom outline does not seem to repeat. How can I fix this? Thanks, Keith Never mind. This seems to be working. Not sure what was going on the first time. "Keith G Hicks" <krh@comcast.net> wrote in message news:OM82rj8rIHA.2068@TK2MSFTNGP05.phx.gbl... >...