I have a football pool worksheet. My goal is to have a button that displays a message box with the players in order of how many points they have. So far I have been able to display this message box with one problem...
The problem occurs when there is a tie between two players. If they have they same score it will only display the first score in the row. In my attachments when you press the macro button "score" it displays a message box saying:
"Sue is in first place with 12 points.
Bob is in second place with 9 points.
Bob is in third place with 9 points.
Larry is in forth place with 3 points."
I want it to say:
"Sue is in first place with 12 points.
Bob and Dave are tied for second with 9 points.
Larry is in (third or forth, doesn't matter) place with 3 points."
Even if it needs to say
"Bob is in second place with 9 points.
Dave is in third place with 9 points."
would be an improvement.
Here is my code so far and i've attached the file.
I am tryig to create a macro that looks up a value between two workbooks. In the workbook the value may be on the 1st, 2nd, or 3rd worksheet. So far all that is returned after the macro is run is a cell with the formula in it with no values. This is the macro formula:
Do While rcnt <= lrow Cells(rcnt, 3) = "=if(iserror(vlookup(cells(rcnt,2),rng1,12,0))=false,vlookup(cells(rcnt,2),rng1,12,0),if(iserror(vlookup(cells(rcnt,2),rng2,12,0))=false,vlookup(cells(rcnt,2),rng2,12,0),if(iserror(vlookup(cells(rcnt,2),rng3,12,0))=false,vlookup(cells(rcnt,2),rng3,12,0),"""")))" rcnt = rcnt + 1 Loop
In a Worksheet On Change event I am trying to obtain a new value that the user has placed into a particular cell.
However, when I get to the line of code that reads the value in that particular cell, it is pulling the value that was in the cell prior to the change. When I view the sheet I can see the new value. When I do a debug.print or ? in the Immediate Window it shows the prior value.
I am trying to do an "if" statment to look in every 23rd cell to see if it is greater than 0. if it is it populates what is in that cell. That is fine...
The problem is i want to create the "if" statement just a couple of time manually, than drag it down 300 rows and have it look every 23 cells. It does not recognize the pattern???
I have a date of hire column in mm/dd/yyyy with 5000 rows. It contains many different formats and I need to sort it by year. Is it possible to sort by year and include all months, i.e. everyone hired in 2008, by month.
It also seems that Excel is not recognizing some of the formats as dates.
I guess because I use a drop down box and another cell reference for the date, my IF/AND statement isn't recognizing the date and therefore gives me a "FALSE" result. I have the date formatted as a date.
I've tried referring to cell I2 which is the drop down box - that doesn't work. I've tried using quotes around the date as if it were text - that doesn't work. If I use just the w10=3 part, that'll work fine but it must be associated with the proper week otherwise every 3rd of the month that falls on a friday will be a holiday.
I am working in two different files. If number "40556" on worksheet B is showing on worksheet A, the function is to state "TRUE", but it's stating "FALSE". Both files' numbers are listed as GENERAL under FORMAT CELLS.
I've got an Excel 2003 sheet where column E has a 6-digit code input into it. I'm able to right a function off that code that outputs if E2 (for example) equals 123456 then H2 outputs "Dog," if E2 equals 123457 then H2 outputs "Cat," and so on until I reach the max of 7 nested functions. Problem is, I have a lot more than 7 codes that I need to analyze. This Excel sheet will be used every two weeks, with lots of codes input that need to have what the code means output. I created a named formula called "part1" where: =if(sheet1!E2=123456,"Dog",if(sheet1!E2=123457,"Cat", etc. It didn't work when I had $ in front of E & 2.
All variables in "part1" work when tested where H2 = part1. I created a "part2" with more variables, checked each and it works too. My problem now is that when I have H2 set to =if(part1,part1,part2) to test all the variables, it'll work on any variable in "part2" but not in "part1."
I have an autofilter setup with 3 values: "Show", "Hide" and "Always". The show/hide values are dependent if formulas based on other cells.
I have two sheets with this filter (Template and Summary); the filter works fine on the Template tab, however on the Summary tab...
When filtered to only show values "Always" and "Show", the filter only recognizes some of the "Show" values. I have copy pasted the if formula from the rows that work to the rows that don't and verified the formatting is the same. There are no named ranges that may be screwing it up and no empty cells in the filter range.
If I manually unhide the rows in questions, then run the filter, the rows stay shown. However, if I run the filter to only show "Always", then run it again to show "Always" or "Show", then the rows in question disappear.
I also have vba assigned objects for show and hide (the same scripts are used for the Template sheet and the Summary sheet), but the issue persists when using the buttons.
I hard code "Show" instead of it being an if formula, the filter works fine. But the formula that doesn't work is also used on rows that do work.
I'm using a macro to send the contents of a sheet to an html file. The problem I am having is that for some reason it is not recognizing the blank cells in the last column (column 9) of the sheet, thus I am not getting the " " that I need in the html code. The code for column 7 works fine and is identical. I did a test and selected column 9, right clicked, and "Cleared Contents". When I ran the macro again it worked fine. The problem is that sometimes there may be data in column 9, so I can't clear it. Here's the code I'm using. Is there another condition I can use to look for a blank cell or something I can do to the spreadsheet for my code to work?
If Cells(i, 9) = "" Then a.writeline " <td valign=""top""> </td>" Else a.writeline " <td valign=""top"">" & Cells(i, 9).Text & "</td>" End If
Perhaps I missed something in the basics of Excel as to how a table's column header is formatted.Also, my date format is dd/mm/yyyy
When using a basic formula, such as below, in A1 and Cells A2 and B2 have the dates 1/01/2013 and 2/01/2013, this code works fine.
VB : =If(A2<=Today(),"Yes";"No")
BUT, when you convert those two dates to a table, the formula with post No.
However, if you make another formula as show below, the correct day will come up.
VB : =Text(A2,"dddd")
When auto filling the dates in the row, without a table, the dates increase per day.
When auto filling the dates in the row, with a table, the dates increase per year. (And yes, even when 3+ dates are inserted, highlighting those 3 and then autofill.)
Why does a column header change the format and to what format is it changed to?
Why do some formulas output accurate date based on the date in the column header and others do not?
Why do the dates increase per day without a table and per year with a table?
Whenever I copy and paste dates from the internet I can't get Excel to recognize the info as dates, thus making it impossible to sort by date. It doesn't matter which site I use, I have the problem with all of them.
[URL]
Using any of the links above, how I can paste those dates and get Excel to sort the date?
I am currently tracking online PPC keyword reports with Excel and need to know if there is a function that will find and match words and phrases and then add the columns that are queried for the matching words/phrases. I think an example is definately in order.
December PPC Keyword Clicks Cost Revenue large dogs 45 .18 $12.00 small dogs 35 .25 $15.00
January PPC Keyword Clicks Cost Revenue large dogs 12 .14 $8.25 small dogs 18 .18 $5.35
Now using the example above I need a function or maybe even a macro that will scan all "keywords" and find a match for each keyword each month, say large dogs, and then add the clicks, costs, and revenue columns and post them on a particular row or rows.
So when running the function it will find and match "large dogs" for each month, it will then add the clicks for all months with "large dogs" in it and then populate a column or row with the total along with the keyword "large dogs" next to it. Is there a function that will do this or maybe even a macro?
I've digitally signed a macro in Excel, where I'm sending mail from using outlook, but I can't seem to get Outlook to recognise the certificate in outlook, even though both programs have my name as a trusted publisher...... so still getting the "A program is automatically trying to send mail on your behalf" Pop up.
How to get outlook to recognise the certificate on the macro that I'm running from excel?
The main issue being it is sending roughly 50 separate emails, and to wait five seconds and manually have to click 'yes' for each one is not really desirable!
What im trying to do is this..I read this number and check for the first number with the 2nd number. If its greater then i will swap it. Im trying to do this so that my number can be rearranged as 456789.
I need a formula that will calucalte the monthly total based on the following conditions:
Col A = Yearly Cost Col B = The number of the month when the costs are starting (1=Jan etc) Col C = The duration or the number of months for which the costs are to spread Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.
Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx
I have an excel database where I register cases. I have in it a button that creates a folder with and ID nr that is in column A (I create new ID nr in the next row, when I press the button it will create a folder with that ID nr and inserts a blank word document in it). We have a template that we copy to the folder (depending what type of case). The idea would be that once the template is filled in and ready to print, It would take the values from the ID nr and a reference number a few cells to the right. Is it possible to tell excel to open the word document in the folder and create a PDF version with the ID nr and reference number. (there are only 2 templates, so the macro would have to look for one of the two in the folder) The names of the templates are: "Standard" and "Other". I guess the best way to start maybe this would be that I select the cell with the ID nr and then press a macro button to have this done. One thing that needs to be done, is to put a copy in the same folder and another in a second folder called "Binder" in my documents folder.
I have a problem that when I try to convert text to number and format the number without 2 decimal places as seen on the link I have given below, Instead of 1607.947, I get 1607947. I have Excel 2010 loaded. The details are in below picture.
I have a large amount of data and I'm trying to count how many unique values I have in one column. I also want to know how many times each duplicate appears. I tried using a pivot table but it's not working for me.
I also tried the following formula: =SUM(IF(FREQUENCY(H:H,H:H)>0,1)) but it's not quite working.
Is there a function that allows you to read column A for an ID (these may or may not include letters/numbers/"?", are non-sequential and are of variable lengths) and, if it is the first time that it has seen an ID column B will read "sample_1_arm_1", if its the second time it has seen an id it will read "sample_2_arm_1", etc? An example of what I am trying to do on a much larger scale:
How to create a spreadsheet with what I think will be a very simple formula?
If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2. If date in B2 - date in A2 is 7 or less days but more than 1, put a 2 in cell C2. If date in B2 - date in A2 is 30 or less days but more than 7, put a 3 in cell C2. If date in B2 - date in A2 is 90 or less days but more than 30, put a 4 in cell C2. If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.
OR
Another, maybe simpler, way of saying it is:
If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2. If date in B2 - date in A2 is 2-7 days, put a 2 in cell C2. If date in B2 - date in A2 is 8-30 days, put a 3 in cell C2. If date in B2 - date in A2 is 31-90 days, put a 4 in cell C2. If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.
I have a spreadsheet listing all my vehicles and their respective oil filter part number. Now, some vehicles share the same oil filter and I am trying to find a way to return the vehicle description when using the filter # as a value. In my example below, I am looking to fill the third column with the Vehicle that also shares the same oil filter number. i.e. in the Dodge row, toyota would appear in the third column and vice versa in the Toyota row.
Vehicle Oil Filter # Also used with Dodge 51515 Honda 54565 Toyota 51515
I am not well versed in excel lingo which is probably why I have a hard time coming up with a method that works.
I'm making a Excel 2013 spreadsheet that has formula in a column that auto enters a number 1-40 when something is entered to the left of that cell. There are 300 rows in the spreadsheet. I would like to make a drop down list in a column cell to the right that would delete that number in that cell from the drop down list. For example cell C1 has 39, that 39 then is deleted from the drop down list. C2 has 22 in it, click on the drop down list cell and it shows 1-40 less 39 and 22.
I have a requirement where I want my list should auto increment with number only.
For example: I want to fill below text in cell and when I drag the cell( Along the column A) and fill the rows downward it should automaticallly incremented.
Data in one cell say (A1) is like 001_TCO_CM, now I want to drag data present in cell A1 and wants data in cell A2, A3, A4 etc... it should get increment like
A2 = 002_TCO_CM
A3 = 003_TCO_CM
A4 = 004_TCO_CM
and so on...any method or way present in Excel 2003 so that my series should get auto filled.
For this example, A1 is the given at 19.875 I am trying to get A2 to net a result of 19.5. A2 is to net that result by searching through cells B1:B14 and finding the nearest equal or lower number. If A1 is less than 13.5 a result of "error" should be generated in A2.
