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 RepliesI have a pivot table with 10 sub total rows, I would like to know if it is possibe to change the font into bold for rows who are subtotals.
View 6 Replies View RelatedI have a large database with names down the left and numbers across the top. I want to "tilt" the entire table so that the names run across the top and the numbers run down the left side while keeping all the data together.
Example:
from this:
a b c d e f
1
2
3
4
5
to this:
1 2 3 4 5
a
b
c
d
e
f
I have a chart that needs to be positioned at the bottom of a table of data. (The table of data is created by some VBA which extracts data from another sheet based on user input). The chart is a pivot chart built on a pivot table from another worksheet. I've read the suggestion that the chart should be positioned above the table of data which I would do, however as there is already a series of tables and charts above, it would be too confusing for the user to see the chart out of sequence.
As I already have a fair bit of VBA to generate my existing sheet, I’d like to have something very simple that I can insert in various places depending when the user makes a change, which requires the chart to be repositioned. Something like:
Change chart position To Range ("B65536").End(xlUp).Row
I have an autoshape on an sheet, and use this code (it's a circle), to rezise it....
[Code] ....
However, it does rezise it, but the shape doesn't keep it's central position. Is there a bit of code I can add to stop the shape from moving? In other words, have the shape rezise around it's center of location?
I have written a macro to export many bmp image files to different PPT slides. Everything works fine, but for few slides I am not able to change the co-ordinates. I am using the below code to position the image files. But no matter how much I change the values, the images sits in the same place. And I dont get any error.
PPSlide.Shapes.AddPicture Filename:=slide4pic2, LinkToFile:=msoTrue, SaveWithDocument:=msoTrue, _
Left:=475, Top:=240, Width:=245, Height:=100
PPSlide.Shapes.AddPicture Filename:=slide4pic3, LinkToFile:=msoTrue, SaveWithDocument:=msoTrue, _
Left:=475, Top:=340, Width:=245, Height:=100
PPSlide.Shapes.AddPicture Filename:=slide4pic4, LinkToFile:=msoTrue, SaveWithDocument:=msoTrue, _
Left:=475, Top:=440, Width:=245, Height:=100
When a userform textbox value is changed, if the vertical scrollbars were shown previously (due to more text than displayed size) and is still required for new value, how do you reset the scrollbar/textbox value back to the top (if it was previously scrolled down by the user). If teh scrool bar is no longer required for new value (due to sufficient space to display new value without scrollbars), how do you remove/hide the scrollbar again until needed.
View 2 Replies View RelatedI have a very large file of data, over 500,000 rows, opening in one sheet in Excel is not an option with my current version. In each row I need to change the characters in positions 41-44 from whatever they current are, to '9999'. I'm sure there has to be a way I can do this using vba, does anyone have a sample snippet of code, or another post they can point me too?
View 9 Replies View RelatedIt seems that you can change position when you editing comment and its remembered, but when its showing its alway's on default position.
Is there way to change position of displayed (when you mouse over cell and coment pops up) comment position?
I need a macro that will create a new blank worksheet, move it to the last spot and paste the entire contents of the sheet named "master" into the newly created sheet.
I have tried to do this with the recorder but have been unable to get it to work.
Is it possible to position a predetermined cell (e.g. A42) in the top left corner of the screen. (Not every screen users use has the same size)
View 3 Replies View RelatedI have this code below that deletes any single occurrence of a customer number but the "Customer Numbers" must be in column A.
I would like to change the code below to instead look for the "Customer Number" column by name, rather than by column A. My "Customer Number" column will always be in row 1, but yet, it will be in a different column letter every time I run the report.
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.
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())
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.
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?
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.
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.
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 RelatedI 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
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 RelatedLet'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?
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.
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 RelatedI 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")
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 RelatedI 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.
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?
I have a report that needs subtotals between different teams. The report starts at cell A10.
In column A is the team name and then columns D through to G contain monetary values.
So the Admin team might contain data from A10-G15 (although this is variable depending on activity).
There is then three rows of blank cells before Sales team data appears - this might contain data for a dozen rows.
Then there is three rows of blank cells before Training team data appears......and so on.
There is ALWAYS three rows of blank data between teams and the goal is to enter totals for each team in the second blank row.
So in the above, Admin team totals would appear in row 17 in columns D-G.
Sales team data would kick in at row 19, run for a dozen rows (down to say row 30) and at row 32 the toals would appear.
Training team data kicks in at row 34 and so on.
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].....