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. Sample rankings.xlsx
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 Replies View Relatedi 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]....
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.
I 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] ........
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 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 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 a spreadsheet which has several fields containing data and
what i want to achieve is that when someone comes to close the
spreadsheet , If they have not filled in all the fields required it will
put up a notice to say that field **** must be completed.
I have a worksheet which will be sent out of our organisation to a supplier for them to complete, save and return to me. I have a number of cells in the worksheet which I want to be mandatory. ie if no information is entered in the cell they will get a warning when they attempt to save the file down but will be unable to save it without filling in the cell. I was also going to colour the cells to identify them as mandatory. The cells are in random positions on the worksheet and different worksheets sent to different suppliers may have a differing number of coloured cells.
Is there a way to have one macro which finds the coloured cells (red), checks that there is an entry in each cell . If it is, allows a save. If it is not populated, displays a warning and stops them saving the worksheet until it is populated and is useable for all worksheets regardless of how many red cells are on the sheet and the position of them? If so how would it look?
I have two combo boxes (created using the control toolbox), and I would like to have the second combo box (pricing analyst approval) be a mandatory field if the combo box value above it (Loss Type) is V-VII only. I would like to have some sort of promt appear that indicates that the Pricing Analyst combo box is mandatory.
View 9 Replies View RelatedIn Column B of my spreadsheet i have a drop down that only allows users to enter "Yes" or "No"
Is it possible to make Column C a required field if "No" is selected in column B.
So basically if cell b4 is "No" then i want to make it manditory that cell C4 is filled out with a reason why.
Same thing for cell b5 then i would like cell c5 filled out before they continue using the sheet. etc.
how can I make a column mandatory?
basically if someone puts data in column C, they have to put data in column D
(people are not putting important information on my spreadsheet, so i would like to make that column mandatory)
i have an excel sheet ,i need every one use this sheet has to fill the spceific cell in one coloum and he couldn't save his editing without fill the requeired cell by pop up message let him know that he has to fill this cell and high light the cell for the user at the same time
View 2 Replies View RelatedI am creating an excel file for my company. This file contains 18 headers (from Columns A to R), all of which are mandatory to be completed.
This file is used by several other colleagues with the intention to update our records almost on a daily basis, and saved by them daily before being checked by me.
I would like to know if there is a macro to be used in order to prevent them from saving the file if all the fields are not completed. (ie. A5 to R5)
This function should only run right up to the next blank row.
Eventually, I would like the users to complete all cells from A5 to R5.
Should a single cell not be completed, an error message will pop-up.
When the next person accesses the file to enter their data underneath, it will again, not allow them to save until all the mandatory fields are filled out.
I have looked at another very similar link but it does not work and I do not know how to tweak it.
The example given is for when the data is in Column A.
The headers on my file are on Row 1 (Column A to R).
Mandatory Cells and knowing to fill in the next row!
I'm coding a userform with many textboxes, one of which needs to be mandatory. How do I make it such?
View 2 Replies View RelatedI have made a spreadsheets wherein all the cells where data are to be entered are coloured yellow.Some cells are mandatory (coloured Green)wherein user have to fill data compulsorily & some are optional (Coloured grey) Since the file is big and sheets are arond 28, I want to attach a buuton which will be assigned a macro to do the validation work.
The macro will search all yellow cells & green cells and if found any of those cell blank will change those colour to red and shall also show a list where the cells are merked red (List will be in a pop up manner, printable, and shall show the name of the sheets & cell address)
I'm looking to find like values in Column A. If there are like values, I'd like to add their row's values from E to F.
I would use the =if(or( function but the amount of like values in A differs from 2-6.
To best explain my problem I will use this example
Dog 2 400
Cat 4 300
Car 1 200
Dog 7 800
Car 8 900
That data is in the top of my worksheet, and I would like to be able to find all the dog values then reference the 2nd and 3rd column and add all the dog values up and then post the totals in a area below like this
dog 9 1200
cat 4 300
car 9 1100
Stipulations
1) there are different amounts of values, there may be 1 dog value or 10 dog values, I would like the program to recognize the dog values and add the corresponding values.
2) dog, cat, car values are not the only values, there may be others like "tree, apple" but are not in use. So only values in the top of the spreadsheet get referenced to the bottom part.........................
I am using Excel 2003. I need to make a mandatory field for TextBox1 on UserForm2. So if the user leaves TextBox1 "blank", then they are forced to enter in data into TextBox1.
View 2 Replies View Related My requirement is something like this. I have a workbook where end-users can fill data and send it to our system. They might enter any rows of data, it can be just 1 or even 100. I want to force users to fill in values in few mandatory fields : columns in B,C,D,E. Now I need a VBA code that should throw a message when it finds that the value in cells B,C,D,E are blank.
Since I am not sure how many rows of data the user can fill, I dont know how to code in VBA the above scenario. I cant give a range as the total rows of input data is not available and static value.
I need to be able to have users complete a range of mandatory cells before they are able to close a workbook.
Foe example cells c5,c10,i11, i18,i22 i24,i26 etc etc need to have data in them before uses is allowed to save or close the workbook. I would like to have a message box prompt to alert them to the issue.