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 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???
On a spreadsheet, I want my formula to caculate the following: if "certain cell" = "certain name", then do this calculation, if "certain cell" = "certain name", then do this calculation....and so on.
I have a drop down list of employees, which would be "certain name" and it is in a cell, which would be "certain cell". The calculation I want to do is if it is a certain employee, I want to take the manually inputted hours in another cell and multiple it by the employee's rate of pay, which would be it's own calculation including labour burden. I have 13 employees that I want to have as part of this. So, if the first valuation comes up false, it carries on to the next and so on until it finds one that matches. I have tried IF commands, but it is too long, so I tried to split it, didn't work. Tried lookup and it didn't work. Tried named formulas, didn't work. It is entirely possible that I am totally on the wrong track or in over my head, but I know this should work.
A 1 1.1 A 2 1.2 A 3 1.3 B 1 1.1 B 2 1.2 B 3 1.3 C 1 1.1 C 2 1.2 C 3 1.3
This is imported from else were and AutoFilter by machine then number and value every time a new record is added to the list. I want to have three charts in the sheet (one for A, one for B and one for C) and i need to be able to set the data source ranges dynamically.
I have a macro i mind that will search the first column cells one at a time. something like:
Sub marco1() 'find A limit range("a1").select For row=1 To 100 Step 1 If cells(row,1).value<>"A" Then ARowFin = row-1 exit For End If Next row
End Sub
This will find the last "A" cell's reference by index numbers as cell(ARowFin,1) But i can't find how to then use this format to select a range.
The "range" function only uses A1 notation for multiple cell selection. How do i use index notation to set a range?
If i can work this out then i can set: r1 = that range and a second range r2 in a similar fashion, then set the data source range as a union of these ranges.
The range changes each month so I want to use a named cell "LastRow" where I can change the number to replace the 2500. How do I use the range name in my formula without giving me a #value# error?
i have an excel sheet with two columns, and depending on the status of the equipment, i need to compare the two columns and if the data is the same, fine, otherwise i need to display a msg box with an error. the columns have formulas in them, and i assigned a name to the cells i want to check, i keep getting errors with the following code, when the colums are the same, it is still displaying the msg box with the error. what am i doing wrong?
For Each Row In Range("Dev_Found") 'Loop through each row in Column C If Row.Value ActiveWorkbook.Names.Item("Dev_Left").Value Then blah = MsgBox("Your % Dev for after does not match % Dev before, please correct on form!", vbOKOnly, "Error") Exit For End If Next Row
I am wanting to build a macro to calculate the average of a range of cells. I have about 2000 lines of data, and I want to average the first 12 cells (then paste the answer somewhere else), then average the next 12 cells, and so on. Using a loop to do this is simple enough. My problem is that I can't insert variables into the average formula as the cells to be averaged
Sub AutoAverage() For x = 0 To 20 For y = 0 To 171 FirstRowRef = Workbooks("NP FT01-03 010206.xls"). Sheets("NP - FT01") _ .Range("a5").Offset(12 * y, 12 * x) LastRowRef = Workbooks("NP FT01-03 010206.xls").Sheets("NP - FT01") _ .Range("a16").Offset(12 * y, 12 * x) Workbooks("Mega Spectrums.xls").Sheets("NP - FT01").Range("a5").Offset(y, x).Select ActiveCell.FormulaR1C1 = "=AVERAGE(FirstRowRef:LastRowRef)" Next y Next x End Sub
I have been handed a workbook which has been used and amended by a number of people over about 5 years. The workbook has lots of named ranges, many of which are obviously now defunct. Others may be defunct but I'm not sure. I would like to delete all of the defunct ones.
Does anyone know a way in which I can identify the cells in which a named range is currently being used?
I have a workbook with several sheets and named ranges like ES_Date, ES_Range, ES_Volume or MC_Date, MC_Range, MC_Volume. The 1st two letters are the name of the sheet where the named ranges are located (one is dates, another volume, etc.).
What I'm trying to do is calculate averages, extract maximum and minimum values from the the named ranges using a start and end date that are entered in cells "H6" and "I6". The formula I am using right now is:
I am trying to find the cell in the named row "Ann_TaylorRegularBust" that is closest to the input bust size in cell B4. This formula works when I use actual range instead of the named array.
I need to select in a sheet the cells which have name reference to see if the whole sheet is dependent , i have tried to trace the dependents and precedents but that didnt work,
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 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 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!
I have several workshts in a file with a summary worksht. I have named ranges of data on each by salesperson. How can I use a formula to get the named range from each tab to show up on the summary sheet. I tried (=Smith!AugustSales), which is the - sheet name Smith and the named range AugustSales, but I get an error.