Subtotal Spreadsheet That Already Has Level Of Subtotals On It?

Mar 22, 2013

Is it possible to subtotal a spreadsheet that already has a level of subtotals on it?

View 2 Replies


ADVERTISEMENT

Have Two Levels Of Subtotals In Spreadsheet?

Oct 9, 2013

I have a report of employees' hours reported for the week, which I process payroll from.

I have sorted the spreadsheet first by employee number, and then by date. I have subtotaled each day's hours (with a formula rounding each day's hours to the nearest quarter hour).

I now need to add a subtotal of weekly hours, per employee number, and I cannot figure out how to do this.

Here is an example of what I want to do. I have highlighted the second subtotal I want to add to the spreadsheet, but have been doing the =sum(xxx) formula for each employee because I can't figure out how to subtotal again.

David L 9/30/2013 0005 360 6:00 A.M. 12:00 P.M.
David L 9/30/2013 0005 112 12:30 P.M. 2:22 P.M.
David L 9/30/2013 0005 115 2:22 P.M. 4:17 P.M.
David L 9/30/2013 0005 13 4:17 P.M. 4:30 P.M.

[Code].....

View 2 Replies View Related

Command Button (submit) Spreadsheet Level

Jun 2, 2009

I have made a userform with some code in it that I would like to be on the spreadsheet itself instead of a user form? How is this done? The problem is getting the below code to work at spreadsheet level since I did not write the code below. I have the button the spreadsheet, but I don't know how to see the name of the button or assign the below command to the button.

I need a macro to copy data from the two combo boxes that appear on the spreadsheet and place the data into cell A for the first combo box and cell B for the second combo box.

View 4 Replies View Related

Sort By Subtotal, Make The Subtotal Stand Out

Feb 5, 2007

way to do this but i have a sheet that is into 5 - 6 thous rows, in one of the columns (names) i sort it by names and then order it by subtotal for certain values.

What i need to know, is there anyway i can take just the subtotal values out and put onto another spreadsheet without copying and pasting it all as there are lots of subtotals and this would help alot as the other info is not nec. just the subtotal'd info. either that or is there anyway i can highlight the subtotal'd row info in yellow/bold text anything like that that would make it stand out without having to do it manually?

View 12 Replies View Related

Subtotal Formula - To Show Up In The Subtotal Value Column

Oct 23, 2008

I’m trying to get my sheet so that at each change in month it creates a sum of the value but I want to sum to show up in the subtotal value column.....

View 10 Replies View Related

SUMIF With Subtotals

Jan 5, 2009

I am having a problem with a formula I think I should be able to get correct but not sure if I am able to do so. On the attached file I have a filter on the TIER's for "Is grater than or equal to 5". What I need is a SUMIF formula that will take into account the filters. This formula needs to separate out between "GER", "IRE" and "UK" in cells C37, C38 and C39.

I have a subtotal in cell C35 which gives me the subtotal of all countries but I'd like to be able to have the subtotal separated out between the 3 countries and also still have the ability to manipulate the data so I could select different TIER's or a range of TIER's and Cells C37 - 39 automatically update themselves.

View 3 Replies View Related

Compound Sum Or Subtotals

Sep 15, 2009

Have a look at the attache example. I have inventory items which have a fixed value. I have several quantity columns to reflect various inventory positions. (OnOrder, WIP, ATP, ATS, OnHand, PAB as at date()...)

At the top of the sheet I need to show sum of quantities and sum of values. In order to compute the values currently I need to hide columns to the extreme right to do the math on a row by row level and then sum the rows and copy the the value to another cell. In this case the cells with yellow background.

Is there a way to be able to write a formula that would return the sum of the qty multiplied by the value without adding an additional column. I would need to function like with filters as well (like =subtotal())

View 3 Replies View Related

Select Only One Row Or More Using SubTotals

Aug 28, 2012

I am using subtotals to create groups. Sometimes there is only one row but more often there are two or more rows. I am trying to run a macro that needs to select either one row (copy into memory) of which there will always be a blank row below that, given the space where the subtotal does its calculations. I have tried a couple things i.e.

ActiveCell.Select
Selection.End (x1Down) . Select
Selection.End (x1Down) . Select

or

Range(Selection, Selection.End(x1Down).Select
ActiveCell.Rows ("1:8").EntireRow.Select

Is there a way to have the macro start and if it sees only one row followed by a space (blank row of subtotal that creates the needed break, that it will copy that one row into memory or if there are two or more rows that it will copy all those rows up to the next break and put that into memory? There can be anywhere from 1 row to several hundred rows.

View 1 Replies View Related

Can't Calculate Subtotals

Jun 13, 2006

have an excel spreadsheet linked to a network printer, it contains a list of what each user printed, how many pages, and the total cost. It is constantly updated. The total cost column contains the wrong price, so Im using this formula:

=If(C2=0,"",SUM(D2/C2)*(H2))

in EACH field on the total user cost column to extract the CORRECT price. So, I started my macro, highlighted the entire TOTAL COST column, then inserted my formula into each field in that column. The correct price displays for rows that contain data, and rows without data are blank. However, when I try to create a SUBTOTAL (Data --> Subtotals) for each user, I get the following error: "To prevent possible loss of data, Microsoft Excel cannot shift nonblank cells off the worksheet."

This is because I applied the formula to the ENTIRE column - even blank cells still contain the formula. How do I get fields without data to be completely blank?

View 6 Replies View Related

Isolate Subtotals

Jul 12, 2006

I need the data "pulled down" into the subtotal row, so to get this after I subtotal, I'm sorting by C, and I've got some VBA deleting all rows where COLs A & B are blank (this is the longest part & the part I want changed the most - this gets rid of the non-subtotaled rows), extended replacing "Total" with "" in COL C and then inserting a lookup in A & B to get the data back next to the subtotals.

This takes really long and I'm sure there's a faster way to do this that I haven't thought of. All in all, I'm looking for something that will ONLY keep the subtotal rows, and will fill down the data to them while removing any non-subtotal rows.

View 9 Replies View Related

Automatically Do SubTotals

May 27, 2007

Client Location Product Cost Sub- total
ABD Here Slurry $125.
ABE There Mud $525. $650.

Where I want to enter the cost and have Excel do the sub-total automatically.
Is there an easy way to do this, remember I am new to all of this? The spread sheet is already 147 entries long and will only grow and I don't want to have to figure out the sub-totals each time.

View 9 Replies View Related

Bolding Subtotals

Aug 27, 2007

After using the subtotal function, I need to highlight and bold the subtotal rows. There are thousand over rows and it is impossible to do it manually, does anyone has a solution to this?

View 9 Replies View Related

Multi-Level Reporting

May 15, 2008

I have a 2-column employee file. Column A has the employee id and column B has the manager's employee ID. The reporting layers can get pretty deep. In this example, there are 6 layers of reporting. For example, Employee id 1200 (which is not in column A) has 2 employees. One of them (1712) has several employees, one of whom (1680) has employees reporting to them and so on for several levels.

Is there a way in Excel (2003 or 2007) to summarize the reporting structure? For instance, I need to know all of the employees that report up through emp id 1712 - not just that report directly to 1712, but all those under 1712's tree.

View 9 Replies View Related

Multi-Level Folders In VBA?

Apr 19, 2012

I have the following function. the problem with this excel is i give it "C:" as directory, it search only one level of sub folders i.e it will search the filename in

C: est
C: est3

It does not go deeper. i.e it wont look in folder that is for example

C: estanotherlevel
C: est3anotherlevelmylevel3

How to modify my function so that it goes to the deepest folder. is there a way to do it?

Function GetFile(directory As String, filename As String)
Dim FSO As Object
Dim fldr As Object
Dim subfldr As Object
Dim file As Object
Dim fullname As String

[code].....

View 2 Replies View Related

Getting Sheet Level Names - VBA

Jul 24, 2012

How can I obtain the sheet-level name through VBA? I'm not referring to the sheet (tab) name but the name as defined though Insert/Define or Insert/Create, etc .

I know the way to get the cell level name goes something like this:

MsgBox Sheet1.Range("A1").Name.Name

But i don't know the correct syntax for sheet level.

View 4 Replies View Related

Check Memory Level

Feb 10, 2009

I have this memory game I want to set out on the computer. In table A, Row 2 (B2:E2) I (Moti) start off being the first player, where I place a series of different items. (In Example is given letters but can be kitchen article food and so…. Separated by coma) these Items are shown to player Jack, for a few seconds, so he can memorize them and Answer in (B3:E3) ....

View 9 Replies View Related

Sum Depending On Indent Level

Sep 22, 2009

I was wondering if it was possible to do a SUMIF function depending on the indent level, e.g. sum all of the values which have an indent of 2 in the row headings.

View 2 Replies View Related

Display Outlines At Different Level

Nov 11, 2006

I created 2 groups of lines (or outlines) with sub-outlines (so I have 2 levels) in the same worksheet.

I would like, via a macro, to display the 1st at level 1 and the 2nd at level 2.

Of course, I tried the "showlevels" command but it puts both groups at the indicated level.

Is there any command to work only on 1 of the 2 groups ?

View 4 Replies View Related

Bucket Level Chart

Aug 30, 2007

With reference to thr great charts that Andy Pope has made available on his website; specifically the thermometer chart and the dollar spit chart. Is there any way that one can use these methods on something shaped like a bucket or do the shapes have to be "straight line shapes"?

What I was trying to do was to adapt the thermometer method to work on a bucket but I realised that the chart is built on a column chart. Thus a bucket would not work cos the top is wider than the bottom.

View 7 Replies View Related

Multi-level Lookup

Oct 24, 2007

I'm looking for a formula to pull the correct piece of data from one worksheet and display it in another. I have tried VLOOKUP, but it is only returning values from the first instance of my lookup value. Below I have included a sample of what the data from the first sheet looks like. My lookup value is entered on Sheet2 in cell A1. The formula I have used is as follows...

=VLOOKUP(A1,Sheet1!A2:D13,4,FALSE)

The result this would return for a value of 111111111 is 15, but I'm looking for one more level of depth in this lookup, by being able to pull not necessarily the cost of the first code, but of a specific code......

View 3 Replies View Related

Change Position Of Where Subtotals Are Placed?

Dec 31, 2013

Formula/code to change the position of where the subtotals are placed. I don't want them appearing at the beginning or end of the data set but in a separate column beside each data set. how to access the code so I can try and alter it myself.

View 14 Replies View Related

Subtotals And Sorting Data

Apr 24, 2014

I have sorted my data by three layers. First by Budget Center, then Invoice, and then Account. I am having trouble writing a formula that will total the amounts by account with respect to its invoice and budget center.

excel forum2.xlsx

View 4 Replies View Related

Sum Of Subtotals, If Negative Equals 0

Dec 31, 2009

I have a column of numbers that after each sum there will be a subtotal. If the sum is a negative number then the new subtotal will be 0. Attached is a sample.

View 3 Replies View Related

Conditional Subtotals (according To The Date)

Dec 11, 2005

Let's say I have this set of data
A..................B..........C
date_______amt_ sub
12/1/05____ 1000
12/1/05____ 2000
12/1/05____ 5000 7000
12/3/05____ 2000
12/3/05____ 9000 11000
12/6/05____ 1000 1000
12/7/05____ 4000
12/7/05____ 2000 6000

So we see a subtotal according to the date, where the total values in chronological order are calculated to be

12/1/05 7000
12/3/05 11000
12/6/05 1000
12/7/05 6000

What sort of formula, then, do I put in column C that subtotals values in B according to the date in A?

View 10 Replies View Related

Insert Subtotals Command

Oct 21, 2007

it state "Use the subtotals command to sum the totals for each sales person) *Hint: convert the list to a normal range before calculating the subtotals.

I highlight Sales person and click Date but subtotals key is not showing up. i have attach the file its in the Subtotals worksheet.

View 14 Replies View Related

Subtotals In Groups Vs Filters

Dec 12, 2008

Subtotal doesn't add cells hidden under a filter column but it does when grouping. How can I get groups to change a subtotal based on whether they are hidden or not. What I'm really trying to do is use conditional formatting to change the format when a group is expanded vs collapsed.

View 3 Replies View Related

Copy Subtotals To New Column

Jun 4, 2009

I am struggling to get the totals from a Pivot table using Getpivot data.

could someone please advise how can i get this.

=GETPIVOTDATA("Sum of USD Value",$A$3,"Security Code","Total")

View 6 Replies View Related

Using Conditional Formatting With Subtotals

Nov 21, 2011

I am trying to work out a spreadsheet that I am using conditional formatting to highlight every two rows. I have 2 lines for each item and i need them to be grouped together. I can get the formula =mod(row(),4)

View 4 Replies View Related

Add Text Frequency As Well As Subtotals

Jan 3, 2012

I need to count the number of times a word appears in a column in a pivot table and add the total value in the next cell minus 1 if the number is more than 1.

MANAGER

(All)
QA
(All)
Count of 1ST LVL ERROR
Error Type
Count

[Code]...

The totals in C come from a pivot table created from the data in a separate sheet. I need to dynamically show who made an error and the frequency of that kind of error. I've gotten the Error Type count to display according to the word in column B, but column C will show how many times that a specific notation was made.

For example:

"Annualization" appears in column B 3 times, but column C shows that one of the descriptions is listed twice in the separate sheet. The current formula shows 3, but it needs to count the 3 in B, then add any number that is more than 1 minus 1 in the relative C cell to yield 4.

This is my count array: =SUM(LEN(B6:B4506)-LEN(SUBSTITUTE(B6:B4506,D6,"")))/LEN(D6)

I've also used: =COUNTIF(_1ST_LVL_ERROR,"*"&D6&"*")

I don't know how to get the second column to factor in, though.

View 1 Replies View Related

Add Column To Table With Subtotals

Apr 26, 2012

I am trying to add a subtotal to the RIGHT of my table for every change in DATE. The desired result looks like this:

Number
Vendor
Run Date
Amount
Subtotals
12580A
4/12/2012 233,220.14

[Code]...

Right now I am manually typing in a sum formula at each change of the value. Is there some way to automatically perform this calculation and have it be in a column of the table?

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved