I've posted a document in which I'm trying to calculate the weekly headcount for employees using their timecard entries.There are many entries per week for each employee, but I want to count them only once per week rather than each time they make a timecard entry. I need to tweak it to deal with one condition that it currently doesn't handle.The person who provided has said he doesn't check in with the forum very often, so I'm putting this out there to a wider audience.
Whenever the value of column W = "UTO" (unpaid time off) I want the formula to ignore that entry (count it as 0 rather than 1) and continue on to the next entry. The way I have the formula now, it counts any "UTO" entries as 0, but then it does not count the subsequent non-UTO entry as 1. I'm not expert enough to fully understand the formula that was provided
--(COUNTIFS(B$2:B945,B945,U$2:U945,U945)=1).
I have added embedded the formula in an IF statement to ignore the "UTO" entries.
=IF([@[Util?]]="UTO",0,--(COUNTIFS(B$2:B945,B945,U$2:U945,U945)=1)) ("Util?" is the header for column W)
I am trying to set up a cell (E10 on my attached spreadsheet) to act as a key that would collect all of the PO#'s from a seperate column (cells A12:A27 on the attached sheet) and wouldn't repeat any of the repeat PO#'s. I am trying to do this because I currently save each order form by the PO #('s) and at times I find myself typing a long string of PO#'s for the book name. It would be much easier if this key cell would reflect all of the PO's for me and I could just copy and paste the contents into the save as box. This brings up another problem- when I try to copy and paste the contents of the key cell I have now, I just get the formula, not what the formula returned.
Need formula to calculate the average %age in the attached spreadsheet. I would like to enter a score between 1 and 4, but with 1 = 10%, 2 = 25%, 3 = 80% and 4 = 100%. The score in the cell must still show as between 1 and 4 but the total must be an average of the relevant %ages. i.e. if scores are recorded as 1, 2, 3, 4, then the total average % will be (10%+25%+80%+100%)/4 = 53.75%. I'm not sure whether this should be in the Validation or in the Total cell.
The results of the formula in cell K36 in the attached spreadsheet returns a value of null. It should be $1,200. Am I blind or have I done something wrong. I just can't see the problem with the formula.
I'm trying to find a way to create an excel sheet for my company. It will be used to import into our CMDB. Its for computers(ID) and their monitors(LINKED TO) We have made 3 rows: ID, CHECK and LINKED TO which consist of the ID's of each computer and the attached monitor. The CHECK row is where we put the word "OK" if that computer is physically present at that location. After this list was made, we decided NOT to link the monitors to the computers, so afterwards we will delete the row LINKED TO.
What i need to do now, in order to not have to seek every monitor manually, is to have a formula which looks up an id (i.e. WBE01111) in the LINKED TO row, and if that one exists in the ID row, it will place the word "OK" in the CHECK row.
Example: (with WBE01111 being a monitor attached to computer WBE03333)
[ID]_____________[CHECK]______[LINKED TO] WBE03333________ OK _________WBE01111 WBE09999________ OK ________ WBE08888 WBE01111________(**)_________
**=formula must put OK here
So the WBE01111(monitor) below [LINKED TO] exists in the [ID] row, i want the formula to see that, and put OK below [CHECK] for WBE01111
I need a formula to drag down the attached that will place zeros infront of any numbers that don't have 9 digits. so if a part number consists of 6 digits then i need 3 zeros in front of it, if a part number consists of 9 digits then i dont need any zeros proceeding it.
* 5 Extravagant Mak (nz) (100) Need the number plus a dot then space then the name, then minus everything after the name, so this would now read 5. Extravagant Mak thats 5dot space Extravagant Mak * 11 Frisbee (100) This one would become 11. Frisbee thats 11dot space Frisbee
I have a formula that displays the 1st monday of a month, I need the formula to take into account which day is selected and then display the first of the selected days date for the month.
I was using the formula below which was working fine for copying across 20 columns and down however many rows, but now the requirements have changed to 90 columns. I have tested this out with the 90 columns but the Indirect function is bogging down the spreadsheet with the constant recalculating.
I rearranged the formula to the one below, but how can I have the reference to the sheet name changed as in the formula above without using the Indirect?
I have a system that generates 4 databases with different types of statistical data. Observed defects, Calculated Defect, Capability and Number of points. I have set up a system for telling me what ranges each section is and I am manually changing the ranges to get my expected results.
Is there a formula that I can use that could adjust my ranges, within my formulas?
I have a spreadsheet at work . I am tracking the quality target for the department and I need to create a formula that adjust accordingly to the target set for month end which is 90%.
I have build in the foreacasted numbers for the whole month but I need the formula to indicate the minimum error points needed to achieve the target of 90% for month end when I replace the forecasted numbers on a daily basis with actual numbers.
I have attached the spreadsheet which will be clearer.
I'm using a template that has formulas using a $ sign to attempt to stop the ranges they refer to changing. The problem is, when I use the template (which involes Access importing some data and adding columns to the sheet the formula refers to in the process) the rows referred to in the formula change in line with the number of rows of data that have been imported. Only the row numbers change not the column headings. So for example:
I am processing a fair few Invoices, which are being sent to me via e-mail as excel documents, very often they contain mistakes, a decent amount of mistakes. Usually the prices are wrong.
I keep track of every single entry on the invoice on my own document - Tracker, which I consider to be the superior/more correct document to the Invoice presented to me by my contractor.
Both of the documents have a reference number, which is a specific docket number, and horizontally, in the invoice, there is going to be a price for this docket. In my document, there is going to be a separate column for the total price.
Is it possible (I guess with VBA) to check for mistakes in the Invoice, but use the Tracker as a reference for this check.
Tracker has columns A - Name B - Department C - Date D - Docket No. C - Total price for the docket (calculation of E to Z) E to Z - all smaller entries
Invoice has columns A - Date B - Docket No. C to E price for that docket, but it is spread, because departments are separated out, so each VAT account can be charged accordingly. I guess it is possible to do a separate column for the price, if it is easier to do a script that way.
Basically, I need to check if in the Invoice document, the price (C to E) for Docket No. (B) is the same as the price (C) for the Docket No. (D) in the Tracker.
I would like the wrong entries to be highlighted on the Invoice Document, so I can see straight away, that this needs attention.
Not always the price is wrong, sometimes the Docket No. is spelled incorrectly (Dyslexic contractor), hence the highlighting.
I need a script that will turn a excel doc into a txt doc. Thats the easy part. The hard part (at least I think it is), is I need it to be in a certain format and I'll do my best to explain that fomat below.
how to change the formula below which is referencing an internal worksheet, to a forumula that will reference an external worksheet that is located in an excel document on the G: Drive.
I have a transactional data set with a line for each transaction and I am looking to count the number of documents (each contains multiple transactions) against criteria.....
It looks something like this.....
Column A Column B Document No Category 11000001 A 11000002 B 11000003 B 11000002 A 11000001 A
Is there anyway to do this without subtotalling for each document and then a count?
Add worksheet as an attachment to an outlook email by having a button on the spreadsheet itself. However I have added another worksheet which i would like to add to the same email ie 2 attached worksheets to the same email this being decontamination certificate. I have tried to do this myself by copying and pasting some of the code and changing the paths and name but all I get is one or the other or the wrong name to the worksheet.
I'm after a formula or some formatting trick to split up the address in the attached sheet. As you can see, the addresses have come through from a database in one cell, instead of a separate cells for the street line and the suburb line, making it difficult to merge for mailing.
eg. the address are coming through to the merge like this;
I attached a sample of what I am working with basically at the end of the day I download all my sales and the following day my distributor sends me a sheet with all the orders that went out.
what I would like to do is match column A and C of the attached fileand have them match up on the same rowso then i can just deduct D from B and see how much I made off that particular item. im sure I have to put C & D in a diff sheet but I still do not know where to go from there
I know from the sample it would look like I could just sort but sometimes there is more in C than A or vice versa because of orders that were filled a day late or so.
I have a list of products in column A and a expiry column B. What I'm trying to do is when I open up excel, a macro gets invoked and creates a list of expiry products based on today, then sends me or someone else an email with a list attached or in body doesn't matter, so that I can attend to the problem. I've searched a lot of places and haven't found the right answer that I'm looking for. This shouldn't be new because I think others would have wanted to know something similar.
When I run my Excel 2003 macro, the attached message pops up. Is there a way please to modify my macro so that when this Excel message pops up, it automatically selects OK?
I have a cell (H27) which has a (sum) total in and have attached a NAME FRED. This is fine as long as the client doesn't add a row above it. If they do when I re-open the sheet the NAME doesn't stay attached to the cell (now H28)
Is there a way to keep the NAME attached to the cell regardless where it is?
Private Sub Workbook_SheetActivate(ByVal Sh As Object) Range("$H$27").Name = "Fred" End Sub
I'm making an excel file where I keep track of the scoring in a little competition me and my friends do with betting on sports.
However, I got a little problem trying to automate my ranking.
Naamloos.jpg
As you can see in the image, when there is a duplicate score, the name stays the same for every other same value (Joris). The formula I use (also as seen in the image) just takes the first name it comes across (from left to right) and keeps using that one. But I need it to ignore the name Joris the second time, and ignore Joris AND Tim the third time, so that every score/rank (even if the scores are a draw) has a unique name attached to it.
