i have a pivot table where i define a custom calculation on.
My issues is after the calcs are complete (pivot refreshed) i want to sort by my values ratehr than the ascending alpha order for my Rows fields.
Is there a work around so that i may keep my calcd fields?
I have written a custom function to be called from worksheet cells. The function is basically a wrapper function for VLOOKUP on a single table. It makes cell formulas shorter, easier to read and self-documenting. Here's a much simplified version of the function:
View 3 Replies View RelatedI am trying to find expected proportion of code per country by looking at current values. I have a list of countries and associated classifications (0-5) with counts, similar to as follows:
Country
code
count
USA
1
65465
USA
2
54651
USA
3
65411
[code]...
I am interested in creating a pivot table with the average of each code as a proportion of each country. The final table would be expected proportion of codes. The pivot table for this set would look like this:
Row Labels
Average
1
5.4%
2
3.9%
3
4.7%
[code]...
Mean per code of the proportion of code per country
I want to autosort by date (column I - dd mmmm yyyy hh:mm AM/PM format) in a worksheet which contains data in columns A:I of 15000 rows.
I want to know how to do it.
When i update/enters a data in a new row or present row, It has to be go according to date automatically(ascending)
I had already googled it and tried all avialable codes with all modifications.
If someone help me would be greatful to me.
I was new to EXCEL with some basics.
I have an excel report that is generated in another program every week. The report is not sorted by date and it may have 100 rows of data one week and 200 rows of data another week. My excel file has formulas that refer to this original report. I need those formulas to update first, then I need the information to automatically sort by date (column D in my excel file). I have tried to setup a script to run, but it's not working. I will attach the file and the script.
Attached File: Sample w VBA.xls‎
A sheet containing all the daily tasks, and according to the tasks that I need my employees to work on a particular day I write YES in the column. The cells that have YES marked next to them are then reflected in a new sheet with the following formula:
=IF('PLANNING CELLS (P)'!C12="Yes",'JOBS LIST CELLS'!C12,"")
Obviously the formula is different for every cell.
The issue with this is that if i have a list of 50 items and i select only 10 (Marked yes) I have a lot of blank spaces in the new list. I need to remove these blank spaces from all columns automatically whenever updating. Is it possible?
I need to find a way to autosort a series of numbers, inputted manually for example, in cells A1 through A15. The autosort function needs to put the numbers in descending or ascending (either way, doesn't matter) in cells B1 through B15.
In effect, if the numbers inputted into the first cells change daily, then the correct order of these be automatically sorted so that they are in a specific order.
The closest I can come WORKS, but I'm using about 300 filter formulas to do so.
I have a column of data that constantly changes in length and want this column to autosort the data alphabetically when the data changes. the list contains the names of worksheets
I then want to display in the next couple of columns data from the worksheets listed in the column. i know i can do this manualy by saying that that the cell equals sheet? cell etc but was wondering instead on putting in a formula of ='A sheet'!E20 i can put something where A Sheet is that uses the name in the column of data ?
I'm trying to make a converter between about 8 various types of values. These are not units like Km or miles or something like that, but rather numbers that represent a specific "hardness value" on a variety of scales (to name a few: HRC, HRA, K)
What I've been doing so far is plotting the two types against eachother and then getting the best trendline I can so that I can use that formula to convert between the two with relative certainty. (for example, when plotting HV vs HRC my fourth order polynomial trendline with an Rsquared of 1 is y=0.0001x4 - 0.0188x3 + 1.0768x2 - 20.709x + 350.69)
My questions comes up where I was hoping to make a window or box of some sort allowing the user to input a numeric value, then selecting the Input units and the hopeful output calculated units, and have the box spit back to the user the conversion.
I want to be able to create a range of VBA userforms to quickly perform long tedious tasks. I want these userforms to be accessed from a nice tidy toolbar.
I have done this and it looks nice and works well. What I would like to be able to do is have my custom toolbar of userform controlled functions be transferable so that if someone else wants my toolbar and attached functions they can install it easily much the same way you can do with an add in.
Is this sort of thing possible or does it require them to manually install all my userforms, modules and toolbar? If it is possible what sort of things should I be looking at?
make a calculation(addition) and use the answer to multiply against another addition calculation....
The sum of (Monday!A1:A4) multiplied by the sum of (Monday!B1:B4) plus (Tuesday!A1:A4) multiplied by the sum of (Tuesday!B1:B4) and so on.
I added a custom add in ages and ages ago and have forgotten how I did it! I'm now trying to remove it but not having much luck.
Its not listed in the Tools>References menu and its not added on to a workbook saved in the C:Documents and SettingsUserApplication DataMicrosoftExcelXLSTART folder.
Are there other ways I could have added it? If so can someone let me know as I might be able to remove it then!
I have a series of monthly revenues and want to calculate each month a commission % - but only want this commission calculated up to a defined limit from the previous months and current month and then to stop when the limit is reached.
View 13 Replies View RelatedA
B
C
D
F
1
Received
Overdue
Completed
Untouched
[code]....
Overdue is received in previous slot and still under dependency.
Formula used for untouched - B2+C2-D2
SLA Target Min - 98% and Max 99%
Algorithm - (Total Number of Allocations completed within 5 business days Plus Type A Exclusions) / Total Number of Allocations received minus Type B Exclusions) * 100
I need a individual SLA achieved % which includes untouched too.
Hoping someone might be able to kindly help me out with this one. It's for a spreadsheet of call charges (credit crunch thing).
On the sheet, I have the call charge up to an hour ($H$5). Over an hour, it's charged per minute at the rate in $H$6.
In cell E27 I can enter the number of minutes for the call.
So basically, if E27 is up to a value of 60 then the cost is just H5, if the value in E27 is 61 or more then it's H5 + (E27-60)*H6.
I'm thinking it's an 'IF' but keep making a mess of it...
How do I calculate YTD from 1 MTD cell? The YTD cell needs to keep a running total of the MTD cell. If the current YTD cell has the number 11 in it and I typed 2 in the MTD cell the YTD cell needs to increase by 2.
View 9 Replies View RelatedIf I send a workbook out with the zoom set at 90...will the zoom be at 90 when others open it.? Also, is there a worksheet event that I could insert to force a specific worksheet to always open with the zoom at 90??
View 2 Replies View RelatedI have a 9 digit number that I want to format as 999.9999.99 Can anyone tall me how? Does anyone know a good link with pointers on custom formats?
View 9 Replies View RelatedWhen using format cells > number >custom I want to custom oC with the degree symbol in superscript. how to get superscript into the custom box? Also where is the superscript icon for the quick access toolbar in Excel?
View 3 Replies View RelatedI am trying to sort a sheet based on the values in a column.
S, SI, M, MI. and it need to be sorted only in this order.
I am working on a excel file to help me interpret survey data. I am having some problems transposing data from tab1 to tab2. So basically, I need all the data from Z on tab1 to be transposed on individual lines on tab2, attached.
View 5 Replies View Relatedi had a play with a document that contains a custon menu bar and now the menu bar is always there.
the code that puts up the menu bar is ...
I am trying to add aright click menu item a cell.
I am using the following code in a module.
Sub addmenu()
Set nbar = Application.CommandBars("Cell")
Set ntrnew = nbar.Controls.Add(Type:=msoControlButton)
ntrnew.Caption = "Paste In"
ntrnew.Visible = True
ntrnew.BeginGroup = True
ntrnew.FaceId = 110
ntrnew.OnAction = "Test"
To trigger the first code when needed.
I know this all works when I put it in brand new workbook it works my custom menu appears and I can run the the "Test sub"
However as soon as I put this in the workbook where I want this to work it does not show the menu.
It is there though as when I step through the remove Sub I can see it matched and then deleted.
I thought at first it was protection but that is now removed but still no joy.
I can not see any setting that controls these sub menus.
I am writing some custom functions to be saved in an Add-In file, but am having trouble saving named ranges. I would like named ranges in the Add-In file to be available in other spreadsheets. Is there a way to do this? Or is there a way to take a range from Excel and save it into a globally available named range using VBA?
View 9 Replies View RelatedI would like to custom sort a table by ID, the first ID to be shown on top would be the ID the user enters in an inputbox. Below is the code I used but I don’t know why its not working:
Sub CustSort()
Dim MyCount As Integer
MyCount = Application.CustomListCount + 1
MyValue = InputBox("Enter ID")
Application.AddCustomList Array(MyValue)
ActiveSheet.UsedRange.Sort _
Key1:= Range("A1"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=MyCount, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.DeleteCustomList MyCount
End Sub
find attached an example, try entering 300000 in the inputbox the custom sort doesn’t work
to complete the attached worksheet. My aim is to “bring” into the RANGE column the contents of corresponding cells in that column whose name is chosen in the Select dropdown. I suspect that one of these lookup and reference functions can do this task but am lost as to which one of them:
CHOOSE
COLUMN
COLUMNS
HLOOKUP
INDEX
LOOKUP
MATCH
ROW
ROWS
VLOOKUP
With page breaks in Excel, is it possible to have different pages span a different number of columns?
Right now, I have my page breaks laid out so I've got 3 separate pages. Currently, they are each 30 rows in height, and 30 columns in width.
However, I want the first one to be 20 columns wide, the second to be 30 columns wide and the third to be 40 columns wide.
If I try and drag the vertical blue bar, I adjust the column span for all 3 pages. I need a way to drag the vertical blue bar for each page separately.
What I need is to add a field that gives a gross margin percentage for a project.
To begin, I simplified the following data that the Pivot Table is using. Project (10 different projects), Account Number, Account Category (Revenue, and Cost), Debit Amount, Credit Amount, and a manually inserted column named Total (CR Amount - DR Amount). See 1st image. Pivot1.jpg
I have designed the Pivot table so it lays out like this. Pivot2.png Pivot3 (2).png
I have been trying to enter a calculated field that would automatically provide the Gross Margin Percentage. In the 2nd image, the Grand Total is the gross margin, but I am unable to use that as a selection when creating a calculated field.
I am trying to figure out the calculation steps that Goal Seek is doing behind the scenes so I can create the formula rather than clicking on goal seek whenever I need to make a change. I need to maintain certain weeks of supply every week depending on the sales. Let's say i want to maintain 5 weeks of supply every week, I would like to insert a column for "Suggested To Order' column that would change if weeks of supply goes below 5. In this scenario I would do a Goal Seek, but I want to automate the calculation so I don't have to do this step every week.
View 2 Replies View Relatedi need to total a range of cells, however, these contain time values; hh:mm:ss. it shows me the total when all cells are highlighted. but =sum() doesn't work.
View 2 Replies View Related