Multiple IF And SEARCH Forumla
Aug 19, 2009
I am trying to do is use a combination of IF and SEARCH forumlas to return a result based on a choice of constants available to the user. Issue 1: IF Function to determine which worksheet to SEARCH What I would like to add to this is if one of the 3rd criteria (Amount) is under a certain number then the results are derived from the 'Staff' worksheet'. (This is stage I have gotten to so far). However, if the criteria is above a certain number then the results are derived from the 'Executive' worksheet. This is the part I'm so far unable to do.
Issue 2: SEARCH function returning the column per the user's selection on 'Matrix' page In addition, criteria 2 (Type) can be selected which corresponds to a column in the Staff and Executive worksheets. So far I have been unable to get the correct search results to post in the Matrix page. What I mean by this is that currently it is set to Type1 only. I have tried using an IF function in cell G8 and include this in the formula, however, the forumla returns the contents of G8 in the cell instead.
Issue 3: SEARCH results are post in an accending order. At this stage I have only been able to work how how to do this by using an AUto Filter on the Staff and Executive pages and sorting accending that way. Going forward as updates are made to the staff and executive pages I would prefer that this manual task was not required. Instead, the formula used returns the search in accending order.
View 4 Replies
ADVERTISEMENT
Oct 11, 2011
Im am trying to create a search marco button that allows me to search in multiple worksheets in one work book. I came across this CODE the first part of it works. It pops open user input box and ask for the word that i would like to search but the this error message pops up Runtime error1004 Method 'range" of object'_Global'failed and i dont know what to do
Private Sub SearchButton_Click()
SearchString = InputBox("Enter Search String", "Search")
If SearchString = "" Then Exit Sub
For Each c In Range(myRange)
If InStr(LCase(CStr(c)), LCase(SearchString)) Then
[Code]....
View 1 Replies
View Related
Nov 2, 2009
I have a workbook with many many sheets in it. The first sheet contains a single column with about 10,000 different values. I'd like to use each of these as search criteria against ALL data in the other sheets (of which there are a good 50 or so). If matches are found (they don't have to be exact case), then I'd like two things to happen:
1. The rows containing the matched search criteria in the first sheet are highlighted.
2. In the cells adjacent to the search criteria in the first sheet, hyperlinks to the matched data are created and named after the sheet upon which this matched data appears.
I've attached a sample file to this post with ideal sample 'answers' to queries made of the first 2 terms.
View 3 Replies
View Related
Aug 21, 2009
I' having trouble using the =IF(ISNUMBER(SEARCH formula to search multiple cells.
I can get it to work to search one cell (as below):
=IF(ISNUMBER(SEARCH("Same",G10)),"No Change",'Aug 09 Matrix'!F10)
(In this case the respone is No Change as Cell G10 contains "Same")
However cannot get it to work for several cells. I have pasted below and highlighted the function I would like it to perform:
=IF(ISNUMBER(SEARCH("Same",G10:R10)),"No Change",'Aug 09 Matrix'!F10)
Essentially the other cells in that row (G10:R10) all contain "Same", however for some reason it is identifying this as FALSE and putting in the data from the 'Aug 09 Matrix' sheet.
View 9 Replies
View Related
Mar 4, 2014
I need one formula to copy and paste the checkcard data changing it from this "CHECKCARD XXXXXX5623" to "CHECKCARD 5623", but I need one formula to look for multiple known checkcard numbers. I will have more then one typically in the bank download so I need to search and copy at least two known numbers as shown below.
I am providing the formula that I am using right now, "=IF(ISNUMBER(SEARCH("5623",B3)),RIGHT(B3,41),"")" but it only works for one checkcard number? Also I don't really need to capture the DEBIT or CREDIT that will always be at the end, it would be redundant. If it's easier to write the formula including that data that is no problem. What I want to copy and move into an adjacent cell is in red.
Here is the source data:
Row 2 - SONIC DRIVE IN#1531 AUSTIN TXDEBIT FOR CHECKCARD XXXXXX5623 DEBIT
Row 3 - JASON'S DELI # 125 Q64 AUSTIN TXDEBIT FOR CHECKCARD XXXXXX9718 DEBIT
View 4 Replies
View Related
Apr 25, 2007
I need a formula showing that if a number is less than say 1.25 then it rounds down to 1.0 and if its between 1.25 and 1.75 then it rounds to 1.5 and then if greater than 1.75 then it rounds up to 2. I need it to work for all numbers not just 1.
View 10 Replies
View Related
Jun 18, 2009
I have a worksheet I use at work that calculates volume proccesed hourly, and also shows a running total as you enter hourly figures into the table, my question is can you hide everything in the result cell until the figures for each hour are entered ? currently all the result cells have irrelevant numbers in them until you enter the hours figure and the formulas do there thing, I could relly do with them being blank until the relevant hours information is input?
View 2 Replies
View Related
May 31, 2007
If you Have a list of account numbers and amounts, is there a way in one forumla to get the average per account?
View 9 Replies
View Related
Jul 20, 2009
Basically I have cells like this.
.....
I want to fill this formula down so that the summing occurs all the way down the collumn. However when I fill it down, it just copys the first two forumlas, rather than make the formula correspond to the row.
View 9 Replies
View Related
Aug 8, 2009
I have a sheet which calculates payment amounts.
Column titles:
Hours | Rate of Pay | Total
In the hours column usually the entries consist of numbers and everything works fine. However when an employee is on holiday they are still paid.
What I want to do is be able to enter the letter "H" for one of the entries in the hours column. The sheet to translate this as 2 hours.
H=2 x rate of pay = total
I cannot for the life of me get the correct formula to in order to achieve this. I don't particularly want to use a macro for this and others have suggested the "COUNTIF" function.
View 10 Replies
View Related
Oct 5, 2009
I have two dates lets call them cells A1 and A2. I then have another cell that caluclates the difference in these dates into a simple number (ie not another date) - A3. I need one of two things.
Ideally I want the whole cell (A3) to appear as a Green (fill) cell if the value is below 5, appear Orange if beween 6 and 9 and appear as red if its 10 or over.
I do not know if you can colour a cell that has a formula in already. So what follows is my current 'work around', which, if you can not colour the A3 cell, then I would need this cell coloured (A4). =IF(D12>7,"Urgent Attention",IF(D12<5,"New",""))
View 5 Replies
View Related
May 27, 2008
I have the margin and I have the sale price. How do I figure out the cost?
View 12 Replies
View Related
Feb 19, 2008
I have a worksheet with pricing & part numbers on sheet1. I put a formula on sheet2 that puts the lowest price in colum C of sheet2. Now I need to also get the part number from the cell before. So if the lowest price is in cell E3 I need to also get the part number in D3, and put it in B3.
View 9 Replies
View Related
May 20, 2009
I basically want for it to be able to calculate all the games a each team has won in the example below.
View 13 Replies
View Related
Jun 16, 2009
I have a workbook that uses several indentification fields one for Job ID, One as a subID which occurs for every part of a job, i am currently using formula
View 5 Replies
View Related
Jan 21, 2010
If I minus H is less that 90 count as 1. But if I minus H is greater than 90 count as one but highlight the row on the speadsheet in yellow.
What I am doing is I have a list of about 3000 entrys and I need to determine if (I minus H is greater or less than 90). I then need to count how many times out of the 3000 entrys each entry is greater or less than 90 and highlight the entrys in yellow that are greater than 90.
It would be ideal to determine the difference between I minus H and then categorize the findings of the 3000 entrys to see how many were say 59 and how many were 47 and how many were 92 etc.
I have never seen a forumla highlight a entry based on count if. I am currently rapidly searching the forums here but to no avail.
View 9 Replies
View Related
Apr 3, 2014
In cell b2 I have a formula =text(today(),"yyyy") which daisplays todays date as a year, in cell b3 I have =text(today(),"mmmm") which displays todays date current month.
I have a column of Months and Years
January 2014
February 2014 etc
I need todays (current) month and year to indicate in a helper column next to the relevent month and year as a number 1 to use as a reference to return date from the row where the 1 is displayed, I have tried =if(and(b2=a10,b3=b10),1,"")) and it dosent like the fact that the year in the colmuns is entered as standard text, is there any whay to do this?
View 8 Replies
View Related
Apr 28, 2012
I am having such a difficult time creating a macro that will reduce the 5+hours I have to spend each week manually copying & pasting all of this data. I making an IMMENSE difference in this worker bee's life!
I have a workbook with two sheets (Sheet1 & Sheet2). Sheet1 has license #'s in column A and the state that the license belongs to in column B like this:
COLUMN ACOLUMN B11111Alaska11112Alabama11113Arkansas11114Arkansas
Sheet2 has three columns. Column A has the license #'s, column B has the state that the license belongs to and Columns C shows a line-of-authority tied to that license #.
COLUMN ACOLUMN BCOLUMN
C11111AlaskaProperty11111AlaskaCasualty11112AlaskaLife11112AlaskaHealth11112
AlabamaProperty11112AlabamaCasualty11113ArkansasLife11113ArkansasHealth11114
ArkansasLife11114ArkansasHealth12345ArizonaProperty
I'm trying to write a macro that will compare the license # and state in Sheet1 to the license # and state in Sheet2. If it matches, append the contents of Column C to the corresponding row in Sheet1.
Here's the thing...Sheet2 contains the entries for all licenses in the company (so this table is HUGE). And there are multiple entries for each state license # (notice how there's two entries above for AK license # 11111 - one for the Property line and one for the Casualty line.
After my macro is run, I want Sheet1 to show all the lines-of-authority on a single line. So if I ran my macro on the above example, after it's run I would have this in Sheet1:
COLUMN ACOLUMN BCOLUMN
C11111AlaskaProperty Casualty11112AlabamaProperty Casualty11113
ArkansasLife Health11114ArkansasLife Health
View 5 Replies
View Related
Mar 11, 2014
I have a spreadsheet with 1,000 rows of data.
Each Cell in Column A has a different long text string.
I need to see which (if any) of 10 specific small text strings exist within each long text string. Depending on which small text string is found I want to return a 3 digit code. If no small text string is found I want to return "Not Found"
E.g.:
- Cell A2 contains "randomtext,randomtext,APPLE,randomntext"
- I want to see if Cell A2 contains any of the words APPLE, ORANGE, CARROT.
- I want to return "APP", "ORG", "CAR" or "Not Found"
Q: What is the most elegant way to accomplish this within a single formula that I could paste into each cell in Column B?
View 2 Replies
View Related
Apr 14, 2009
I have set up a spreadsheet to calculate time as followed:
A1 - Start Time
B1 - Finish Time
A2 - Hours Worked:
=IF(B1<A1,B1+1,B1)-A1
C1 - Start Time
B1 - Finish Time
C2 - Hours Worked:
=IF(D1<C1,D1+1,D1)-C1
and so on.........
View 10 Replies
View Related
May 22, 2009
I am trying to search for multiple values in a cell with Multiple criteria.
E.g: Please find attached the sample excel data.
I tried using VLOOKUP which is not working as I am having multiple values in column A.
View 9 Replies
View Related
Feb 4, 2012
I want to search all the terms Column B within all the text phrases in Column A. I am ok if the words do not match the case sensitivity as I can address that myself.
If the exact phrase match is found, return the value True or False.
Column A
A1:Dog likes to run
A2: Cat Red-tummy-01
[Code]....
View 9 Replies
View Related
Jan 19, 2009
i have 4 areas north east south and west, what is the forumla to enter into a cell that would automatically add the Salesperson when you know the area
Table is
Date, Area, (FORMULA), Amount
13/11/09, North, ( ), Amount
Basically there is a table with the salesperson per area
if i was writing it i would say its as follows
If Area = North then Mr Smith else if Area - South then Mr Jones etc etc
View 9 Replies
View Related
Mar 22, 2009
In 1 cell i need a forumla to give me a starting number and take one away each time a code or codes are dislayed in a cell range. Something like this
A1 = 23 days or any number days i needed
Cell range B1:C52
every time a selected 1 or 2 letter code appears in the cell range i want A1 to subtract 1 day from the starting number, i would need it to subtract half a day if one code appears ie HD, the codes may be P, OT, HD
View 4 Replies
View Related
Jan 11, 2007
see attached workbook. I want VBA to insert an index/match forumla on sheet 1 to lookup a value from sheet 2. I don't want it to specify a range though. I want VBA to look to see if there is data above and to the left of the cell and if it is true insert the index/match formula. Then it won't matter what row or column I put the headings in.
View 2 Replies
View Related
Jul 28, 2012
The first tab of the excel sheet called 'Data' displays information on the years that 2 countries traded with one another. For example, from the years 2005-2010, Belgium traded with multiple countries (Australia, Brazil, Canada, Chile). However, there could have been one year or multiple years where it did not trade with a particular country (e.g. in 2006, it may have skipped trade with Chile).
What I'd like to do is basically create a function that searches through the data and returns a 1 if Belgium traded with a particular country each year from 2005-2010 and a 0 if it did not. So, for instance, because Belgium traded with Australia, Brazil, and Canada in all 6 years, the function would return a 1. But, because it did not trade with Chile in all 6 years, it will get a 0.
The second tab called 'Results' displays what I'd like it to show. I've tried using if statements (but they don't work with text), vlookups, match, index .
View 2 Replies
View Related
Apr 6, 2013
Is it possible to search for multiple values (and present the result in a certain cell) within a range that spreads over two columns? For example: between A10 to A15 and B1 to B10.
View 6 Replies
View Related
Feb 14, 2010
I have a spreadsheet with a database search function incorporated. Whilst this works fine, I was wondering if there is a way to combine the current single query searches into one search macro. I have setup a userform called CriteriaSearch that I would like to use to search my database tab. An example search would be:
User requires someone who speaks French (5), is female, has a rank of 3, is available immediately and has notes/keywords/keyphrases of "excellent linguistic and cultural knowledge". The appropriate fields on the userform are populated accordingly. The user selects search and a macro runs to find matching records. To make things slightly easier, the criteria (Language, Gender, Rank etc) all have fixed column references.
View 2 Replies
View Related
Apr 5, 2014
I need to find the fruit name in the table, and then reference the color from the corresponding row's Column G value. I have experimented Match, Index, Sumproduct, with no luck. Do I need to somehow nest with a VLookup?
Range A1:F4 Fruit Color (Col G)
VeggieVeggieApple VeggieVeggieVeggie Red
ClementineVeggieVeggieVeggieVeggieVeggie Orange
VeggieVeggieVeggieVeggieBananaVeggie Yellow
VeggieVeggiePear VeggieVeggieVeggie Green
Name Color From Col G
Apple Use formula to pull "Red"
Clementine
Banana
Pear
View 4 Replies
View Related
Jan 7, 2010
I need to be able to search for an "Street Address" across mutiple workbooks all stored in same folder
My workbooks have one sheet with several columns one column is "Street Address"
What i need to do is search all the workbooks that are in that folder to see if a particular address is already in a previous workbook
Example:
Search
1313 Mockingbird lane
msgbox no match
or msgbox found in workbook blah blah.xls
I am using excel 2003, however the workbooks i open and the save get converted to 2007 if that makes a difference
View 9 Replies
View Related
