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 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.
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.
I 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
As 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 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.
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?
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.
I have a list of company names and I need to find the duplicates. Of course pivot table finds the exact matches, but how do I find duplicates when only 1 character is off. Like punctuation or "s" or a mistype of a single character?
I have a workbook that is used to calculate sales & commissions for our sales reps. The majority of the worksheets are "RepLast, RepFirst". All of these Rep worksheets include an identical format. I need to add another sheet which grabs the total quantity of products A for each rep and sums them on the new sheet. And repeats the process for products B to Z.
Is there a way to do this other than longhand naming each rep and then the field in the worksheet? We are regularly adding new reps (and losing some periodically), so I would prefer to not have to regularly update the longhand formulas (i.e. ='RepName1'!A25+'RepName2'!A25+'RepName3'!A25...etc) as that would seem prone to introduce calculation errors.
By now I think this is pretty straight forward and works wonderfully well for the other 50+ sheets. However I have two employee names where the second of the two (Diddly, Bill) takes and autofills the fields with Schmoe's information. The only thing I can randomly pick up is perhaps the employee numbers are too close: 00868
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 spreadsheet that I receive with a column containing some numbers. It arrives in genearl format but as part of a macro I format Column "C" to 2 decimal place numbers.
This works fine but some of the cells are completely empty. I need to have those empty cells populated with "0" which will of course display as 0.00 because of the formatting.
I am trying to figure out a solution and wondering what would work the best. Here is my situation. As an example, I have one big database with fields such as:
This continues for up to 1000 lines from a database. I have this is a tab called "Database". From the data in the tab "Database", I want to be able to create 4 seperate reports.
The first report might only have the columns "Item #" and "Date". The second report might only have the columns "Item #" and "Qty". The 3rd with only "Item #" and "Price" The 4th with only "Item #" and "Cost"
If I create a new spreadsheet called "Sales" and create the following:
I'm in the process of writing some SQL in excel and normally when I want to specify a field name as another name say wx22 as Part Number i just use square brackets eg "select wx22 as [Part Number]"
I have a field that needs to be split into a set amount of columns depending on a delimited character. This works fine with all of your standard delimeted characters such as / or ,. The only issue is I have a field that has a delimiter of the square symbol . How would I be able to insert this symbol into a input box. When I try and copy the symbol from excel it does not show in the inputbox. I also tried to copy it from the sybol that can be generated in word and that just pastes as?. I was wanting to know if there is anyway this can be entered into a variable so that I can delimit the data using this symbol. If I have to use a ascii value I don't mind I just cant find a ascii code for it.
I'm creating a spreadsheet that will have two types of data fields: hex and binary, with pairs of corresponding values for each record in the worksheet. I need to be able to edit the hex values and have the binary values update correspondingly and vice versa, on the same worksheet. I can't figure out how to create such a two-way feedback mechanism.
I have two worksheets. Each have a common value in two cells. An ID and a currency amount. One worksheet also has a payment date. I need a worksheet that combines the two, along with the payment date.
So, I've created a pivot table and need to use one of my values in both the row and value fields. I haven't seen a way to easily do that so far. Is there something I'm missing or is this not possible with Excel (yet)?
I have a workbook with two sheets, one used for referance (Column A - Product SKUS, B - Product Description and C - Carton Quantities) - Sheet 1
The other sheet I would like to import incoming shipments. (Column A - Product SKUS, B - Product Description, C - Quantity Ordered and D - No of Cartons) - Sheet 2
What I need to do is find out how to get Excel to find and match up the SKUs in Col A and then dividing Sheet 2's column C by Sheet 1's column C to give the number of incoming cartons of each product in Sheet 2's Column D
I have a excel sheet with 3 columns. In column B and C there is either a number, or empty. If both for instance B1 and C1 is empty, i want A1 to automatically be filled in with the number 1. If there is value in one of them, then i dont want anything in column A.
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".
I'm sure it is possible to solve this with an array, despite all my efforts having produced nothing so far. Attached is an example workbook. On the 'Data' sheet, every month the data will change and I need to fill in column A, with the approriate Name from the 'Map' sheet. Is this possible using an array?
I am trying to create an asset management database using excel, and would like to be able to add custom notes to each entry (index) so as time goes on, I can refresh my memory what we have done with a particular item. I have attached the current file I have created.
Essentially, when I use the GREEN drop down menu in the top right, I want it to display the notes/comments that I enter in the rightmost column of the data, as the attached file illustrates.
However, I want to be able to hide that rightmost column and be able to edit the data from the new area (white space) it is being displayed in, however as I have it set up right now, if I edit the cell, it edits the forumla and poof goes all the comments and the formula becomes messed up.
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).
is there a way to protect the userform fields or the userform as whole from other people entering date accept the person who has been given the privilege to enter data?
I have a workbook with sheet 1 called Support which is basically an input form. The second sheet is called Database.
I want to enter data into the form and via a macro copy the data to the database. I have followed a text book to the letter on how this is to be completed to the letter.
My problem is that when the data is written to the database some of the fields are blank. Using F8 I can step through the VB script which appears to select fields at random.
