Following is my data: A B C 1 Oranges 6 2 Oranges 5 3 Oranges 7 4 Pomegranates 19 5 Pomegranates 16 C3 should be 18, and C5 should be 35. Next week there will more more or fewer categories with a variable number of entities in each. A macro to accomplish this will save me a lot of time (and errors ??). -- “Doubt is uncomfortable, certainty is ridiculous.” (Voltaire)

0 |

4/7/2010 6:55:01 AM

Subtotal feature or pivottable based of dynamic named range; http://www.ozgrid.com/Excel/subtotal.htm http://www.ozgrid.com/Excel/excel-pivot-tables.htm http://www.ozgrid.com/Excel/DynamicRanges.htm -- Regards Dave Hawley www.ozgrid.com "bud i" <ibud.no-spam@bellsouth.net> wrote in message news:8E02A42E-60D6-4A1B-BD93-CDDCB163ED40@microsoft.com... > Following is my data: > A B C > 1 Oranges 6 > 2 Oranges 5 > 3 Oranges 7 > 4 Pomegranates 19 > 5 Pomegranates 16 > > C3 should be 18, and C5 should be 35. > Next week there will more more or fewer categories with a variable number > of > entities in each. > > A macro to accomplish this will save me a lot of time (and errors ??). > -- > “Doubt is uncomfortable, certainty is ridiculous.” (Voltaire)

0 |

4/7/2010 7:42:52 AM

Give this macro a try... Sub SubTotals() Dim X As Long, LastRow As Long, LastSubTotal As Long, Fruit As String Const StartRow As Long = 1 LastRow = Cells(Rows.Count, "A").End(xlUp).Row Fruit = Cells(StartRow, "A").Value LastSubTotal = StartRow For X = StartRow + 1 To LastRow + 1 If Cells(X, "A").Value <> Fruit Then Cells(X - 1, "C").Value = WorksheetFunction.Sum(Range(Cells( _ LastSubTotal, "B"), Cells(X - 1, "B"))) Fruit = Cells(X, "A").Value LastSubTotal = X End If Next End Sub -- Rick (MVP - Excel) "bud i" <ibud.no-spam@bellsouth.net> wrote in message news:8E02A42E-60D6-4A1B-BD93-CDDCB163ED40@microsoft.com... > Following is my data: > A B C > 1 Oranges 6 > 2 Oranges 5 > 3 Oranges 7 > 4 Pomegranates 19 > 5 Pomegranates 16 > > C3 should be 18, and C5 should be 35. > Next week there will more more or fewer categories with a variable number > of > entities in each. > > A macro to accomplish this will save me a lot of time (and errors ??). > -- > “Doubt is uncomfortable, certainty is ridiculous.” (Voltaire)

0 |

4/7/2010 8:07:01 AM

Hi Bud, Is it really necessary to have the formula in column C adjacent to the last record of the particular type? If not, you could set up a little table either on the same worksheet on on another worksheet like the following with a list of unique values of your column A. Col E Col F Item Total Oranges 18 Pomegranates 35 You can then use SUMIF. See Help for more info on this. Your formula in F2 in the above would be =SUMIF(A:A,E2,B:B) You only need to copy the formula down and you can add or delete items as required. If you decide to have your table in another worksheet then the formula would be like the following. (Assuming the table is in columns E and F) =SUMIF(Sheet1!A:A,E2,Sheet1!B:B) -- Regards, OssieMac "bud i" wrote: > Following is my data: > A B C > 1 Oranges 6 > 2 Oranges 5 > 3 Oranges 7 > 4 Pomegranates 19 > 5 Pomegranates 16 > > C3 should be 18, and C5 should be 35. > Next week there will more more or fewer categories with a variable number of > entities in each. > > A macro to accomplish this will save me a lot of time (and errors ??). > -- > “Doubt is uncomfortable, certainty is ridiculous.” (Voltaire)

0 |

4/7/2010 8:36:01 AM

Is there any way of getting excel to generate subtotals by page? these should update automatically when pagination changes. ...

I have an application with MFC extension DLL's with dialog's both in the main app and in the DLL's. Apart from care in the resource Id specification it works fine using classwizard in Vc 6, allowing me to define member variables in the min app and the dll's However on converting to Vc.Net the Add Member Variable wizard is unavailable for the dialogs in the DLL's, but seems to work normally in the main app. Why is this so ? and is there a work-around ? Is this related to resource issues. All the code and variables generated by Vc 6 work fine,I just can't see or modify t...

I have been trying to come up with a formula that will read each cell in a column and copy & paste only the cells that contain numbers with .50. I can seem to properly configure the formula to read any whole number as long as it contains .50 in it. I want these vnumbers pasted into a seperate column. =IF(D21="*"+"0.50"),(D21),("") Thank you very much for your assistance. =3DIF(D21-INT(D21)=3D0.5,D21,"") If you truly want the numbers copied and pasted you would have to use = VBA. Gord Dibben MS Excel MVP On Wed, 2 Jun ...

Hello, I'm trying to extract numbers from a string stored in an Acces table field. What is the best method to do this or the correct function to use? Thanks. "fiaola" <fiaola@mail.com> wrote in message news:eXm39F4YIHA.5028@TK2MSFTNGP04.phx.gbl... > Hello, > > I'm trying to extract numbers from a string stored in an Acces table > field. What is the best method to do this or the correct function to use? That will depend on how the numbers are stored in the string, and to what extent the string format is consistent. What do your field values look ...

After adding a sales note to a transaction with a reference number, how do I pull up a list of all the transactions with that reference number? I haven't seen any reports out there (custom reports in customersource) that are based on the transaction table....if you know how to use SO Administrator then a query will work...(replace XXX below with your reference number but keep the single quotes) select transactionnumber, time, referencenumber from [transaction] where referencenumber = 'XXX' order by time Hope this helps... "Michael" wrote: > After adding a sa...

I am setting up a spreadsheet to keep track of my students. I want to use excel if possible to keep track of lates, left early, attendance, etc, as my grades are kept there already. I was wondering if you can assign values to variables to accomplish this and how to define them. Thanks Why do you want to assign to variables, why not just use worksheet cells? -- HTH RP (remove nothere from the email address if mailing direct) "BigRon" <BigRon@discussions.microsoft.com> wrote in message news:D246057F-E2D9-474F-85A2-A31440E48142@microsoft.com... > I am setting up a s...

Hello, I am creating a database to replace our excel to record our church membership. Our last number is 580 on membership table, and on address table is 193. During the construction I did not realize that if we delete autonumber for the testing data, it will not continue with the next number of 581... There are 5 records testing that I deleted and when I see now it shows 586. I have finished testing, I want it to show the next number is 581. The same problem with the Address autonumber Please help what should I do. -- H. Frank Situmorang In some versions of Access, all you have to ...

could anybody tell me how to set Excel to write out a number in English? I need a way for Excel to refer to a number in one cell/worksheet and turn it into a checkbook type nuber in another (e.g., "112.19" becomes "One Hundred Twelve and 19/100" or "One Hundred Twelve Dollars and 19 cents" ). Please Help! see your post in misc, please do not post to more than one group at a time -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated!...

In a worksheet of mine I have a bunch of dates in column A in the format dd/mm/yyyy. I also have data in between the dates in that column and I was wondering if there was a formula that will count the number of dates. Thanks Aaron If the other data is not numbers you can use =SUMPRODUCT(--ISNUMBER(A1:A100)) if there could be other numbers, then if you know the start and end dates you could use =SUMPRODUCT(--(A1:A100>=DATE(2005,1,1)),--(A1:A100<=DATE(2005,12,31))) -- HTH RP (remove nothere from the email address if mailing direct) "aaronwexler" <aaronwexler@disc...

Is it possible to filter out a subtotal row? If I have a matrix as follows, is there a way to remove the Subtotal3 row/line? Total Labor 75.00 Overhead 50.00 Subtotal1 125.00 Training 50.00 Travel 50.00 Subtotal2 100.00 G&A 75.00 Subtotal3 75.00 <<---remove? Total All 300.00 Hopefully this came out right, sorry if it did not. Is there a way to attach an image? You can do the row visibiliy to false, You need to have an appropriate expression set he...

Hi I have a colleague who wishes to filter across a row and not a column. As I understand it the filter functions on Excel are specific to columns. Any suggestions appreciated. Peter You could Transpose to Columns. If you have fewer than 256 rows to filter in this manner, you could: Copy the range to filter. Go to a new sheet. Edit | Paste Special | Transpose Then, you will have Columns instead of rows, and you can use the AutoFilter. tj "Peter W" wrote: > Hi > I have a colleague who wishes to filter across a row and not a column. > > As I understand it the fil...

Hi, Is there a way to extract the numbers, remove them and place them in an adjacent column from the cells in the example below? The LEFT function does not do the entire trick, since the amount of numbers differs per cell and I still need to delete the number in the original cell. 1bla 1.1blabla 1.1.1blablabla 1.1.1.1blablablabla Thanks ...

I have a spreadsheet of shortpayment amounts and their invoice numbers on one spreadsheet with a column of credit amounts with their invoice numbers on another spreadsheet. I need to match the credits written with the shortpayments. kinda like this spreadsheet 1 spreadsheet 2 inv # amount date Credit # Amount Date 122334 15.00 11-25-09 675555 15.00 12-10-09 223345 22.00 10-20-09 754444 22.00 111-25-09 naturally...

Hello. I am currently using the following formula, but want to improve it: =HLOOKUP(C3,'Drivers'!$C$4:$CT$34,5) Currently it is retrieving the number in the fifth row, but I am concerned that the number will change if the reference worksheet is ever altered. Is there a way to have Excel look up a value that is in a row with a certain label (eg, "Average Size") instead of the fifth row? Thanks! You can, using the MATCH() function on the row with the labels. Look in HELP for details. But of course it would have been better if Excel could do it the way you describe, at lea...

I have a column of numbers which represent a change in value over the previous year. I want to have a + in front of the number if it's a positive change. Let's say my first number is 500. My problem is that I can write something like, if(a1>0,"+"&a1), but when then I can't format the number. If the number is in the millions I can't provide a comma as a thousand separator and I cannot tell it how many decimal places I want to show. Is there a really easy way to do that, or do I have to write a huge formula to precisely spell out the formatting within...

Hi all we are using gp 9.0 and would like to add bin numbers to the sop blank invoice form and are having some problems does anyone know how this can be done ? Hi Steve, Due to a report writer limitation of only being able to have one 1 to many relationship from the SOP_LINE_WORK table, it would take a fair amount of work to add the bin. Currently the Serial/Lot information links to the SOP_LINE_WORK table and that is a 1 to many relationship. If it so happened you do not use serial/lots it would be easy because you could remove the serial/lot information and table that is used in ...

On the Account List page I wish there were sub-totals under Favorite Accounts and Other Accounts. Anyone know of a setting or hack to do this? Future functionality? ...

How Can I Insert Rows as per requirement? I am working on an Expense Template. I have spared around 20 rows for the User but generally only 5-10 rows required. Now Is it possible that I start with 3 rows and if user needs more space then those hidden rows Unhide one by one as per the need arise. I will protect the workbook. You could give the users a macro that would: ask how many rows to unhide unprotect the worksheet unhide those rows reprotect the worksheet Kind of like: Option Explicit Sub testme() Dim wks As Worksheet Dim myHiddenRows As Range Dim HowManyToShow As Lo...

I have written a program for work that is basically a daily cash u spreadsheet, however I would like to save each days report as a backup Ideally I would like to save the worksheet as that days date by us of a command button/macro to reduce the chance of user erro( ie savin under wrong name) The date is on the worksheet, but I can't figure ou a way to use a range as a save name. I would really appreciate some help with this problem. Cheer -- Message posted from http://www.ExcelForum.com Hi Lippa73 One way to save a copy in C:\ with a date/time stamp Sub test() Dim strdate As ...

I'm trying to set out a worksheet for payments out for my company. The only thing that I'm having trouble with is the record number field. I could type the incremental number at the beginning of each record myself but I was wondering if it was possible to get Excel to do that automatically. If possible, I'd like to have it enter the next number up in the field below when a new record is added. Is this possible? TIA -- Alex01 ------------------------------------------------------------------------ Alex01's Profile: http://www.excelforum.com/member.php?action=getinfo&use...

Hi, I posted this question before, but I cannot find the thread of it anywhere, so I am posting it again. If it is duplicated, please forgive me. I have 90 000 rows of data that is in the following format: CW001P01-SH-C: Time Duration Partition Utilization % 20/03/2010 23:56 900 86.3521441 21/03/2010 00:11 901 86.35425916 21/03/2010 00:26 899 86.35738494 21/03/2010 00:41 902 86.3596435 21/03/2010 00:56 901 86.36061494 21/03/2010 01:11 901 86.33145463 21/03/2010 01:26 912 86.33382161 CW001P12-SH-F: Time Duration Partition Utilization % 20/03/2010 23:56 900 2.55062256 21/03/...

I need to assign a sequential number to a serial number. The serial number is the YEAR and a LETTER for the Month. Then followed by a sequential number for the that month and year. The sequential number starts over each month. The number must start with a 01. How could I automate this and insure I don't duplicate it? Thanks Matt -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200703/1 Hi Matt, Here is a function that will return an incrementing number which will start over on the 1st...

Hi, I have forms which display in PivotTable view. One column is a percentage of the row total. I have set the number format in the properties sheet to #0% to take the percentage back from two decimal places to none. This works fine while the form is still open. If I click save and close the form the next time I open it the formating is back to two decimal places. How do I retain the number formating, please? John -- John Whyte ...

I need a header in the top row of my excel worksheet so that even when I sort the data the top row remains the same. 2003:- Data>>Sort>>My Data Range As>> Check the “Header Row” Option Button. 2007:- Press Alt+D+S the Sort Dialog Box will appear Press Alt+H which will select the “My Data has headers” check box. -- Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "willyd" wrote: > I need a header in the top row of my excel worksheet so that even when I sort > the data the top ro...

Morning all. I need to do a name check to match with a subtotal function. Is that possible? I was thinking that subtotal(Func_Num,match(....)) might handle it, but I don't see anything that'd allow for that in the list of function numbers for subtotal. My goal is to perform a subtotal type operation, to compare names in a field range from one worksheet to another. I'm already performing a subtotal operation on one field, and wanted to do an if test on the names that show up with each operation. With the false response, I'll then be changing the name eleme...