May 28, 2014

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)

## Key Formula (E10 On My Attached Spreadsheet)

Jun 25, 2009

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.

## Formula In / With Dropdown: Calculate The Average %age In The Attached Spreadsheet

Oct 13, 2009

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

Feb 5, 2010

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.

## Formula: CHECK And LINKED TO Which Consist Of The ID's Of Each Computer And The Attached Monitor

Aug 1, 2007

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)

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

## Concatenate Formula (drag Down The Attached That Will Place Zeros Infront Of Any Numbers That Don't Have 9 Digits)

Jun 17, 2009

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.

## Formula To Trim And Adjust Data

May 1, 2014

In need of a formula to trim data:

Examples:

* 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

## Adjust Simple Formula To Find Last Row

May 8, 2009

I am using this formula in Cell J12: =AVERAGE(\$G\$12:\$G\$85)

It will always be \$G\$12 but \$G\$85 needs to be done so that it goes until the last empty row on the worksheet.

## Adjust Formula To Show A Selected Day Of Week?

Feb 25, 2014

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.

=DATEVALUE("1"&G7&G5)+IF(WEEKDAY(DATEVALUE("1"&G7&G5),2)=1,0,8-WEEKDAY(DATEVALUE("1"&G7&G5),2))

Is there any way to adjust it to do this the drop down box for the day will be in F9.

## Adjust Formula To Allow For Changing Sheet Reference.

Nov 22, 2009

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.

=IFERROR(INDIRECT("'"&COLUMNS(\$A\$1:A\$1)&"'!P"&ROWS(\$A\$1:\$A8)),"")

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?

=IFERROR(INDEX('1'!\$A\$8:\$AT\$115,MATCH(\$A3,'1'!\$A\$8:\$A\$115,0),MATCH("PAT",'1'!\$A\$7:\$AT\$7,0)),"")

The sheet reference will change from '1' through '90'.

## Adjust Range Within Formula To Get Expected Results?

Dec 13, 2013

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?

## Formula To Adjust Based On Target Cell

Mar 11, 2007

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.

## Concatenate Two Text Fields BUT Left Adjust First Field And Right Adjust Second Field

Jun 22, 2012

I want to concatenate two Cells into a single cell BUT have the first field left justified and the second cell right adjusted.

A1 = "John Williams", A2= "Single"

A3 = "John Williams Single"

Apr 4, 2008

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:

becomes

## Searching For Mistakes In Document While Using Another Document As Reference

Feb 2, 2014

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.

## Script That Will Turn Excel Document Into A Txt Document

Feb 17, 2009

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.

## Excel Document - Formula On Numbers

Aug 5, 2013

Field A which I need to convert into two cells.

So I would have two columns for which would give me the split of A

A) 6210220121120

What this needs to be is 62102 in one field

The other field it needs to read as a date (which is backwards) and this would be: 02/11/2012. As you can see that is how the field is.

Last four digits is the DD/MM backwards and the year is forwards.

## Changing Formula To Reference Spreadsheet From External Excel Document

Nov 22, 2011

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.

So what it's doing is totaling the counts in the 1210laradj worksheet that match columns HMO-ALT SELECT as well as the numbers 3,5,9 etc.

Well now these worksheets will be on a seperate excel spreadsheet located on a network drive, so I am trying to figure out how I can fix this.

I tried adding it in like so, but it fails.

## Formula For -- If Text = 1 And Text =-1 Adjust Running Total

May 13, 2009

formula for -- If Text = 1 and Text =-1 adjust running total
example:

1312 (total)

SS1311 (new total)
LA1310 (new total)
LB1309 (new total)
LS1310 (new total)

LS =+1
SS, LA, LB =-1

## Formula For Multiple Count - Without Subtotalling For Each Document And Then A Count

Oct 19, 2009

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?

Nov 29, 2012

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.

## Attached A Macro To Button

Sep 18, 2006

I attempted to attach the following Macro to a button. But when I tried to run it the following errors: Complie ErrorInvalid inside procedure.

Sub commandButton()
Option Explicit
'Dim MyArray(6, 4)
Dim MyArray(200, 4)
Public MyData As Range, c As Range

'next empty cell in column C
Set c = Range("c65536").End(xlUp).Offset(1, 0)
Application. ScreenUpdating = False ' speed up, hide task
'write userform entries to database
c.Value = Me.Textbox1.Value
c.Offset(0, 1).Value = Me.TextBox2.Value
c.Offset(0, 2).Value = Me.TextBox3.Value
c.Offset(0, 3).Value = Me.TextBox4.Value
c.Offset(0, 4).Value = Me.TextBox5.Value.....................

## Split Up The Address In The Attached Sheet?

May 27, 2014

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;

Joe Bloggs
3/119 newtown street sydney nsw 2001

When i need them to look like this;

Joe Bloggs
3/119 newtown street
sydney nsw 2001

## Match Columns With Attached File

Mar 8, 2007

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.

## Re-create The Attached Chart (.jpg File)

Apr 27, 2009

I would like to re-create the attached chart (.jpg file) but have tried for the past 30 or so minutes with no luck.

I know how to arrange my source data. I just do not know how to combine the stacked columns with a line graph.

## Attached Additional Pdf File To Email?

Aug 18, 2014

I have an existing macro that convert active sheet to pdf then email it as an attachment thru outlook.

now, what i need is to attach another pdf file in it, so it will now be 2 attachment. here is my codes:

var all_spans = document.getElementsByTagName( 'SPAN' );
for( var i =0,skip =0 ; i [code]......

## Sends An Email With A List Attached

Sep 25, 2008

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.

## Attached Message Window Pops Up

Oct 18, 2009

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?

## NAME Doesn't Stay Attached To The Cell

Mar 19, 2008

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

## Ranking With Only Unique Names Attached To Values?

Apr 1, 2014

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.