i need to create a formula to calculate rankings for a race series. there are 10 events in the series, only the best 7 individual results count. and there are 4 events which are mandatory and must be included in the rankings. sample attached.
View 3 RepliesI need to create a formula to calculate rankings for a race series. there are 10 events in the series, only the best 7 individual results count. and there are 4 events which are mandatory and must be included in the rankings.
Sample attached. Sample rankings.xlsx
i have created a form hich dumps into a database.
There are some mandatory fields which need to be filled in by the user on the form - how can i flag these up at the end of the form - with a message box which says - "cant complete - feild "" "" "" are missing "
I have a form (Excel, of course) which needs to be filled following some criteria. I have a filed name "Clause of non-compete" and you have to choose between YES and NO. This field has to be mandatory. I did that with the following code (see below). Now i have another field that says: If YES, should it be maintained? How can i make the second field to be dependant on the first one and mandatory? I want it to be mandatory only when there is Yes in the first field (merged field N35:O35).
[Code]....
I am looking to create a file that would force our sales rep (over 20 people in the company) to fill out certain fields in the excel spreadsheet before submitting the data to other teams. Often times the information is missing which makes many people unhappy. I am new to VBA excel and found the code below online but it does not seem to work. My goal is to have sales people fill out cells, C12, C14, C15, C16, B12, B14, B15, B16 (which are merged cells) and J12, J13, J14, J15, J16, K12, K13, K14, K15, K16 (also merged) and J5 before closing. The code below does not work correctly because as soon as the information is entered just in one cell, excel ignores other 'mandatory' but blank cells and allows the user to close the file. E.g. if info was entered in J5 when other cells were left blank, excel allowed me to close the file.
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Cells(12, 3).Value = "" And Cells(12, 4).Value = "" And Cells(13, 3).Value = "" And Cells(13, 4).Value = "" And Cells(15, 3).Value = "" And Cells(15, 4).Value = "" And Cells(16, 3).Value = "" And Cells(16, 4).Value = "" And Cells(12, 10).Value = "" And Cells(12, 11).Value = "" And Cells(13, 10).Value = "" And Cells(13, 11).Value = "" And Cells(14, 10).Value = "" And Cells(14, 11).Value = "" And Cells(15, 10).Value = "" And Cells(15, 11).Value = "" And Cells(16, 10).Value = "" And Cells(16, 11).Value = "" And Cells(5, 10).Value = "" Then
MsgBox "Please enter required information (cells highlighted in blue) before saving"
Cancel = True
End If
End Sub
I am trying to save an excel file with the following features set up:
1. the user of the file should fill the mandatory fields (possibly highlighted in green) without fail
2. upon saving, the file should only save if all the mandatory fields are keyed / filled in.
in case of empty cells left as per the above, there should be pop-up messages asking to fill the details.
here is what I tried. however, i cant seem to get the pop-up messages. I guess I am missing out on giving some commands that make it mandatory to fill.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Sheets("Sheet1").Range("G11") = "" Then
Cancel = True
MsgBox "Client has not been filled in"
End If
If Sheets("Sheet1").Range("g12") = "" Then
[code]....
I am inputting 5 variables in columns A through E:
Family (Y/N)
Adult (1/0)
Child (1/0)
Date of Birth
Gender
I am attempting to generate:
Age Males in families sorted into age brackets
Age Females in families sorted into age brackets
Age Males single sorted into age brackets
Age Females single sorted into age brackets
I did this rather simplistically generating 4 columns (F through I) and then using COUNTIF (although I know there is a better way than the lame way I used COUNTIF but that is not the point right now).
However, my question is, can I generate the data described above (located in D13:I22 on my spreadsheet) without going through the messy, cumbersome step of creating extra columns?
coding mandatory field in Excel 2010.
This is a code that i have for having a master workbook, and saving it in different folder so my employee cant access it
Sub NextInvoice()
Range("J2").Value = Range("J2").Value + 1
Range("E6:E9,H9,J9,B14:K20,H4,B28:K32,B36:B39,D36:D39,F36:F39,B42:K43,B46:K47,B50:K51,B54:K55,B58:K59,B62:K63,B66:K67,B70:K71,B 74:K75,D78:E78").ClearContents
End Sub
Sub SaveInvWithNewName()
Dim NewFN As Variant
'Copy Invoice to a new workbook
[code].....
I have found a code for mandatory cell, but it creates a second macro and i was not able to link the two.The mandatory field has drop down of employee's and it is located in the cells D78:E78.
Basically I've jumped straight in and have tried to recreate a report I need to produce which requires a pivot table. I have managed to create the code I need to produce a pivot table in a new sheet with all of the headers available for analysis. Where I'm struggling is in getting the pivot to show the datafields correctly.
The list that I am creating the pivot from has about 30 headers. 20 or so are analysis fields with the remainder being various figures; monthly plan, monthly actual, monthly variance etc..
The pivot table I need to create needs all analysis fields available to the end user for manipulation, which I have managed to do. I have created rows for sales areas, also with no problem. It also needs the above plan, actual and variance fields in columns and as datafields. This is where I'm having trouble. When I create the pivot manually it ends up with 1 row and 3 separate columns with summed data in each. I have checked the VBA code, copied it and used it again. The pivot table comes out with 1 row but with the data fields stacked one on top of each other. Any minor alterations that I can think of, (for example adding a position = 1, position = 2 etc.), either creates a chaotic looking sheet or an error.
I've checked various posts on pivot tables but am having trouble understanding the way in which I can correct this. Is there a straightforward command that I'm missing? Do I need to start declaring variables for pivot items?
From an outside source, a date is brought into one cell, and time is brought into another. Is there a way to combine them into one cell with the format of m/d/yyyy h:mm?
I tried concatenate, but that did not work.
In worksheet named, " My Overview", if the total sales values are a zero in C47:C59 the consultant name is duplicated in B47:B59 because of the values being a zero in C47:C59. How can i have the formula not duplicate this?
View 3 Replies View RelatedI have fields "Day", "Posts", "Impressions" in a pivot table and I need to create a new metric for the average Impressions per Post. Ideally, this new metric should function just like the other fields in the pivot table, and not just static, because I'd like to break it out not just by Day, but also Time, etc.
Day
Count of Posts
Sum of Impressions
Impressions Per Post
Monday
52
1,881,468
[Code] ........
How would I go about finding the "Number of Shirts Ordered" values in the top right?
View 1 Replies View RelatedI have a spreadsheet with some calculations and the part i am interested in is data in R4 : V58
In this R4:R58, S4:S58 and T4:T58 have some rows that have False because of IF calculations that put False in some rows of R and hence in S and Hence in T.
For example, R15="FALSE" so will S15 and T15 be. Col U is based on whether the corresponding row in R is False or a number. If a Rown in R is false, the row in U= 0 else the row in U is the same as in the same row of a different column - Col I
Col R, T and U are numbers. Col S is text
For example:"
R S T U
False False False 0
False False False 0
False False False 0
55 DEF 3 15
46 XYZ 2 67
False False False 0
False False False 0
23 GEF 4 43
I want a table in another worksheet that will sort through this using a formula and print
Col1 Col2 Col3 Col4
55 DEF 3 15
46 XYZ 2 67
23 GEF 4 43
If I can define things like where the table will be placed in the new worksheet etc, that will be a bonus..But It appears I cannot understand how to do this at all..
I'm currently using the following formula to return values rounded down to the nearest 2.5: =FLOOR(X8*1.25,2.5)
This works fine but I would like to put in place a system where if the "X8" value is 1 the formula produces a 2.5, and if the "X8" value is 10 or greater the formula produces a 10.
I have an Excel workbook with two sheets "DataSheet" and "Actual Peaks", "DataSheet" contains a column with dates and a column with values. "Actual Peaks" has a bunch of dates listed in a column. For each date in 'Actual Peaks', another column goes back to the "DataSheet" finds the date, then starts adding values until a specified sum is reached, once the sum is reached, it returns the date at which the sum was reached. However, I have found that sometimes it is off by a day or two.
See the attached workbook for a much clearer example : Excel_forum help 7-23-14 DD validation.xlsx
I am trying to add numbers from cells if it IsNumeric and for some reason in column K the macro doesnt recognize numbers after row 14?
The range column is "E4:E"
Search criteria is the letter "R" in column "E" Then using OffSet, I go thru other columns and process data. Most of the macro works except for column K after row 14?
Although I have removed the original numbers in the main area (I12 - AM12), there are still values in the fields I11 - AM11 that just won't go away. These formulas merely sums up the columns below as far as I can see so I can't figure out how come the values 101, 98, 9o etc aren't '0' since there are no values to add anymore..
NB!Not sure if the formulas will be in norwegian or if they change based on language settings but think you will be able to figure them out regardless..
('HVIS'=IF)
I'm creating a Purchase Order Form that will reduce time spent adding in contact details.What I'm looking to achieve is a form that will be printed with a few formulas allowing sections of the form to be filled out automatically once a singular company name (chosen from a drop-down list I created, currently with a 'Combo box ActiveX') then the rest of the form is filled out accordingly.
At the moment it's a bit of a mess, not too sure where I'm meant to put the ranges.There are currently 2 sheets - Sheet 1 with the form, Sheet 2 with all contact information.
I have attached an excel sheet for your reference. I have particular debit values that are to be added between the dates. And Dates are also derived by formula based of payment term.
The ones I need to modify is Highlighted in Yellow. The values to be added is in "Customer Statement" and in H Column
These dates also have formula by which there are derived
-------------------------Current Ageing-------------------------
Date Range
Bucket
Amount
Percent
Start Date
End Date[code].....
I am USing =SUMIFS('Customer Statement'!$A:$A,'Customer Statement'!$H:$H,"=" & E11) but does not work.
I am finishing up a macro that manipulates a pivot table and creates a file for us to run. But we all keep this pivot table up a lot, and usually keep a lot of the data filtererd in it.
Is there a way to set all of fields in a pivot table back to their default values like when you first open up the workbook?
I was wondering if it were at all possible to add multiple columns to the values portion of a pivot table at once, instead of individually dragging. When you click on them, it auto-sends them to Row Labels, which I don't want. I have a bunch of colums I want to add to Values and don't want to waste a bunch of time dragging them individually.
View 2 Replies View RelatedI have an excel file with several sheets. I have a macro with IF-functions to check the values of two fields in sheet1 (one text input field and one drop-down menu with two values A and B), and based on these values I get different data from sheet2 and inserts it into a field in sheet3
View 5 Replies View RelatedAs in the attachment example, I use a database (sheet2, where data start from row 3, and headers are in rows 1,2). I've named it as "Data" with a dynamic range name (formula: FFSET(Sheet2!$A$3;0;0;COUNTA(Sheet2!$A:$A)-2;COUNTA(Sheet2!$2:$2)). When I enter a month and a year, in sheet1, I want to get for that year's month:
1) The working days
2) The sums of the fields Totals and each of the other (AA, BB, CC etc.).
I have used the advanced filter option in excel 2010 to display all the unique occurences of a list of names (column B) and then used the count function =COUNTIF(A$2:A$21,B2) to count the occurences of each name (column C).
However, excel did not manage to take into account the last 3 entries in column A (mir-23). I assume this is because it is a mixture of text and numeric values. How can I include this information in the grouping and counting?
excel1.png
I have a column of dates (2007, 2006, 2005 etc...) and a column of data ranging from "unsatisfied" to "very satisfied". I want a formula to provide, for example, the total number of occurences of "very satisfied" in "2006".
View 9 Replies View RelatedI need a formula to count and give me totals. Here's a sample of my data tab:
WG 3806 8
WG 3806 10
WL 3806 10
WG 3806 8
WG 3806 10
WG 3806 8
My summary tab would be:
WG 3806 8 3
WG 3806 10 2
WL 3806 10 1
I need a formula that will do the math for me and provide the end results (3, 2, 1).
I am trying to compare two fields using the INDEX MATCH formula. One field needs to match exactly and the second field should return the closest match to a date. I have attached an example where I am trying to find the closest production date (column F) for the matching ID that is before the complaint date.
View 8 Replies View RelatedI am trying to count the number of rows that have values greater than 10/01/2008 in either of two fields. I tried following formula but instead of giving total number of rows, it returns a random date.
View 2 Replies View Relatedi have two date fields....one date requested eg 02/06/09 (cell C10)...another date completed eg 03/06/09 (cell R10)
a third field (cell S10) contains the formula: =IF(R10-C10=0,"less than a day",R10-C10)
so if a request was actioned on the day then it shows as less than a day, otherwise will show how many days it took
but when this formula is draggeddown all other cells show - less than a day
how can i make these cells blank whilst still holding the formula?
also - is there a quick formula to add to show only the amount of wrking days a request took to complete?