trying to come up with a formula that will return the last non-zero value in a row. In the example below, I'd like the formula to return the value "456". If there is a way to do this without VBA or array formulas, that much the better!
View 2 RepliesThe conversion rate is updated daily from telephony reports, and I manually sort it into conversion order each day before sending it round.
What I want to do is find the highest converter for each team.
So:
Column A ('TeamLeader' range) has the Team leaders in, column B has the agent name ('Agent' range), and column F has the agents conversion rate ('conversion' range).
I think I'm on the right track with this formula:
=(MAX(IF(TeamLeader="Bonnie",Conversion))
But this only displays the figure in the Conversion rate, how to I get it to 'step back' 4 column's and show the agents name?
I tried this:
=VLOOKUP(MAX(IF(TeamLeader="Bonnie",Conversion)),A5:F60,-4,FALSE)
but that returns #NA - is this because I'm asking the VLOOKUP to look 'backwards' 4 columns?
I know how to create drop down list but I have a problem to Return different value then is displayed in Dropdown List. For example: On the workbook I have two wokssheets: one with empty cell and secound with list created for dropdown list.
On the secound woksheet I have created: column A namebox "Country" - cointains full name of countries (Great Britain, Australia ...) and column B namebox "CountryCode" - contains country codes only (GBR, AUS ...) When I click on dorp down list on first worksheet, I would like to my drop down list display me list with columnA "Country" but when I choose one of that it would returns me cell from columnB "Country Code".
Is there any function in Excel that returns all values from a list above or below a certain percentage rank/percentile?
For instance, if you want the average of the 2% highest numbers in a list? Or the sum of the 25% most expensive items in a shopping catalogue?
It has to be a "dynamic" function, where you can just drop in a set of figures, sort the list, add a percentile, and perform a calculation on all the figures from the cutoff point signalled by the percentile...
I have run across a problem that I am unable to solve with a combination of index, match and countif funtions. What I am trying to do is have the formula return disctinct symbols. I have a list of currencies on one sheet: audusd, eurusd, usdcad etc, more than one from each. Id like to return one from each onto another sheet...
View 9 Replies View Relatedis there a way to return a textbox full of information based on a selection from a dropdown box?
View 4 Replies View RelatedActually I plan to create an eRequisition Form for our branch sites. My focus would be the consumable items of Lexmark printers. I want to create cascading drop-down lists based on data table (worksheet : All) as per attached. From lots of examples I've seen in this or other similar forums, I notice that in order to allow this cascading to work is by creating column with header name identical with the input in the 1st List. But I'm thinking, if I need to do that, then there gonna be hundreds or maybe thousands columns to create as the number of branches are expanding, so I think tht should not be practical.
I found examples that shows how lookup 1 value can return multiple values but I totally cannot think of a way to allow those multiple values to be returned as dropdown list rather than displaying all in rows of the worksheet.
What I need is that, in 1st list, upon selecting the branch, it'll populate all items related to the branch and when selecting the item, it'll populate brand/model list for the particular item only. The input in the dropdown list should not have duplicates. Been trying using pivot to do this with macro that enables auto refresh once any changes made in the table.
create this cascading drop-down lists just based on the table as per attached? I really want to avoid having to create extra tables to allow this cascading to work.
[URL]
I am doing a list which has the same products returning several times, but with different values. Want to filter/make a new list, with only one of each product and the summed amount of that specific product. Summing the specific amount is not that big of an issue, but the creation of the list is, least in a smart way i have tried this:
[Code]......
This being the last possible entry for the summed list.
My problem is that the formulas is getting too big for my computer :S, since this formula is copied more or less 10 times.
Here is an example of what i want: Product list.xlsx
I have a list of names in say column A, but the names are prefixed with numbers. So, for example you have 2 Peter Jones, 5 Johnny Piper, 10 Andrea, 3 St. Mary's, 13 White etc.
I want to return these list of names in another column say B, but without the prefixed numbers. So, column B will have Peter Jones, Johnny Piper, Andrea, St. Mary's, White etc.
[URL]
If I go any of the cells in that column, the VBA code page opens up and I get the following message:
Compile Error:
Ambigous name detected: Worksheet _SelectionChange
and the following vb code is hightlighted:
Private Sub Worksheet_SelectionChange(ByVal Target As Range
The code I have used is:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 13 Then
Target.Columns.ColumnWidth = 40
Else
Columns(13).ColumnWidth = 20
End If
End Sub
I am trying to create a formula that will allow me to enter a zip code and have excel return the specific tax rate for that zip code. I have zip codes in excel for all of California along with the corresponding tax rates. I am creating a form that I can just input the zip code and have the tax rate automatically pop in.
View 1 Replies View RelatedSo basically I have an Excel sheet which has keywords that need to be entered in Google search. I need the URL of the first page of the search result that appears after that keyword is entered. IS there a macro for the same?
View 3 Replies View RelatedI am trying to use the following code to compare the password that was entered, to the one that is stored in the worksheet to make the user aware that the password they have entered is incorrect
View 2 Replies View RelatedI have the following table:
A
B
C
D
E
F
1
Most Recent
1st
2nd
3rd
4th
5th
[Code] ......
I am wanting to populate the "Most Recent" column by selecting the last value in each row between B to F so that I get the following result:
A
B
C
D
E
F
1
Most Recent
1st
2nd
3rd
4th
5th
[Code] ........
I am also wanting the result under Column A to indicate by cell colour red if the value is less than the previous value and by cell colour green if the value is equal to or greater than the previous value.
For example; Cell A2 should be highlighted green because the last value which is D2 is greater than the previous value in C2. While Cell A4 should be highlighted red because the last value which is C4 is less than the previous value in B4.
I do have another question actually. In the Total Sales worksheet, I have a a cell in S2 that I wish to show the max value from the "total sales" colum and then show the first and last name related to that particular row where the max value was. so, I only have =MAX(O2:O112), but I want to show the Name associated with the max and not just the $ amount.
So in S2 I want ---> John Doe and not $512.50, but I can only think of =IF(MAX(O2:O112), $B2+$B3, 0) becase I want both the first and last name. I used this formula with only $B2, but it just returned the first name in the B2 field
i have this formula in cell e28 =IF(B28<>"",LOOKUP(B28,STOCK!A1:A500,STOCK!B1:B500),"") and when its empty it returns a blank cell
but in cell e29 i have exactly the same formula =IF(B29<>"",LOOKUP(B29,STOCK!A1:A500,STOCK!B1:B500),"") but i returns #n/a
why is this and how do i stop it i want the cell to be empty unless there is a code entered in b29 in which case it will look up the relevant info for me
I have a sheet with this:
cell a1 = numbers from 1 to 10 or nothing
cell b1 = TO or OFF depending on cell a1
cell c1 = numbers from 1 to 10 or nothing
Now I have a formula in cell b1 that detects if theres text in cell a1.
IF A1 = nothing, then b1 = off, else b1 = to.
What I want to achieve is to do 2 things on the IF.
If a1 = nothing, then c1 = nothing and b1 = off. else, b1 = to.
how can I do this?
I looked everywhere and no one speaks of double answers on IF.
i have a drop down list in cell b16 which then corresponds with what to bring up in the next drop down list in cell d29 this works fine.
View 2 Replies View RelatedI am trying to create a formula that will search a row left to right for the first instance of zero and return the corresponding column label. I had this working for a smaller table using a couple of hidden columns with nested ifs however the new data set is approximately 25 columns wide and it will get very messy using the same approach. See attached sample with desired results.
View 2 Replies View RelatedI have just decided to dable in the 'VLOOKUP' function. I have looked at a few examples on the net and I have tried to replicate them with no luck (I have modified titles and values). I have attached my example to this post.
View 2 Replies View RelatedIf I have a column full of data is there a way to copy every nth row to a new column? For example, I may have dates and corresponding days of the week. Can I copy the date of each tuesday (i.e. every 7th row) to a new column?
View 2 Replies View Relatedcode to scan a datasheet for a value and return "MATCH" if it is found and to return "ERROR" written in Red or Yellow if it is not found.
View 4 Replies View Relatedi was trying to see or figure out if there is a way to bring over information on a row from one sheet to another. I have a sheet with dates in column A and information going across to column h.
as an example i have row 1 that has date in A1, text B1, text C1, nothing in D1 but text in E1 all in Sheet 1.
I want to see if there is a formula that will say if it is todays date then grab that row and bring over the row with the cells that have data
I am building a form that uses a combo box to lookup up a row in a table and return row data. To make things more complicated I want to return more than one record. For example: the table has a list of jersey. I search for a style jersey. I want to search the list and return all the colours for the chosen style. Sometimes there is only one colour or there can be up to 10. Have a look a the attached example.
View 2 Replies View RelatedI am trying to use a sub to count files that some one has helpd me out with in the past. It was working but for some reason now it is not. I always have 0 returned as the number of files in the selected location (SourcePath), which is a filthy lie as there are files in there. The idea is that all .xls and all .xml files are deleted, and then if the folder is empty, that is also deleted, but as the Count_Files sub is always returning 0 the code is trying to delete the folder, which causes an error as it has files in it.
View 8 Replies View Related=IF(ISERROR(VLOOKUP(B14,'Metal Worksheet'!$A$8:$L$157,11,FALSE)),"",VLOOKUP(B14,'Metal Worksheet'!$A$8:$L$157,10,FALSE))*D14
As long as the lookup value is true the formula works but if the lookup value is false then I get #value! when it tries to multiply by D14. How do i get the formula to return a blank cell or 0 value if the lookup value is false?
I would like to get the last value in column A & B and place them into another sheets cell. The number of rows will always be changing too.. In the attached spreadsheet I want the totals, which is the very last value in the colums.
View 8 Replies View RelatedI am including the entire code. It does a regression analysis on data for a second degree polynomial. In doing so, it creates a worksheet called Regression. So I test first to see if this sheet already exists within the workbook and then ask the user to delete it if it does. I've stepped through the function and the function is always returning False whether Regression exists or not.
View 5 Replies View RelatedI currently have a range of documents which I know the name of but not the file extension. The Macro I have been working with is fantastic for checking the existance of the file but I need it to also return the file name if it finds a match. I am currently using Excel from the 2007 Small Business Suite on a Windows XP PC. The Code I am currently using works as follows:
View 4 Replies View RelatedI want my worksheet to search a column for a specific date (3-Dec-2008, for example) and count cells containing only that specific date. If it finds the date, I want it to count them.
However, if that particular date doesn't appear in the column, it returns 0. I want the cell to return blank. Basically, instead of giving me 0, I want it to return empty. I've tried IF statements, COUNTIF statements, combinations of IF/COUNTIF statements.