If Statements To Lookup Text In Cells / Columns
Mar 14, 2012
I have a workbook with two sheets 'Driver details' and 'Jan Raw Data'.
If column B Employee (Jan raw Data) matches the names in Columns B&C (Cloumn B & C, Sname, Fname) on the 'Driver details'.
If it equals a certain name i.e. Jon smith, then in empty column E of the 'Jan raw data' then copy the entire row of that individual from the 'driver details'.
Driver details
A B C D E F
Emp No Sname Fname Desc Desc Reg
123 smith Jon opticar BVA MA2
456 pratt mike opticar TMA YP7
Jan Raw Data
A B C D
Occup Emp Product Supp no.
Crawley Smith Jon unleaded 3453534
Crawley prat mike unleaded 89797
View 3 Replies
ADVERTISEMENT
Sep 27, 2006
I try to write the function that gets some "ref" and returns appropriate item.
For example: if I give to function "C3", it returns "AAA"
if I give to function "R18", it returns "BBB" (cause it between R15 to R26)
if I give to function "R9", it returns "BBB" also.
View 9 Replies
View Related
Feb 13, 2008
I have the formula below:
=IF(OR(LEN(B2)=0,C2>0),"",IF(AND(VLOOKUP(A2,$A3:$D$99,4,FALSE)=D2,VLOOKUP(A2,A3:$D$99,3,FALSE)>0,B2> A2),"Possible",""))
It should bring back the result "possible" when C2 is negative and D2 (name) matches other D cells while B2 (date) is greater than the A cell (date) for the corresponding matched d cell also the corresponding c cell is positive.
But I think the formula is not incorporating b2> corresponding matched column a.
View 14 Replies
View Related
Apr 25, 2014
When I enter my sales data into a sheet it can be 10000 rows long, I want to be able to enter a set number of transactions on a second sheet which then uses a formula to look up what items was sold on said transaction.
I'm pretty sure it's possible but I'm out of my depth. I've using something like it before which was this statement - =IF($B1566="","",INDEX('RMS Sales'!P:P,MATCH($C1566,'RMS Sales'!$A:$A,0),1))
I've attached example sheet : For-Excel-Forum.xlsx
View 12 Replies
View Related
Jun 17, 2014
I would like to create a formula in a summary sheet ("sheet 2 section" in attached)that looks up and sum cells based on multiple criteria in row and columns in "sheet 1 section". I thought I sumifs would work, but I kept getting #value errors. I'm not a power user in excel. I attached the spreadsheet - it is only an example of what I want to do as the real data is confidential and large. The result I should I get is in section 2. Lookup account 12.251 for tim in the month of February - result is 14.
Test2014.xlsx
View 3 Replies
View Related
Jan 3, 2013
I have a 1000x1000 cell grid on one of the worksheets in my workbook where each cell contains one of the following four possibilities:
1) Blank
2) A string that is not defined in a lookup table (*lookup table = a named range on a different sheet)
3) A string that exactly matches an item in the lookup table
4) A long string that contains an item in the lookup table (which can be found with a FIND function, for example)
I would like to apply conditional formatting to my 1000x1000 grid, to color each cell according to the lookup table, where the last column has the name of a color that I can match with my conditional formatting setup.
I have solved everything except for No. 4).
2) is taken care of by using "Format only cells that contain" with "No Blanks"
My solution for 3) - using "Use a Formula to Determine Which Cells to Format" - uses
=VLOOKUP(A1,lookup_table,2,FALSE)="fill color name" So, for every different color I want to use, I have a different Formatting Rule in the Conditional Formatting dialog.
But 4) is tricky, because VLOOKUP can only return the result for a specific cell value.
If my cell contains France;Germany;Italy, I'd like the color to be according to what I set for "France" in my lookup table. Or, if my cell contains Germany;France;Italy, I'd like it to be set based on what I decided Germany is in the lookup table (i.e. "blue").
You can notice that one thing that is consistent is that I separate my "sub-strings" that are contained in the lookup table with a semi-colon.
So, one way might be to see if FIND finds a semi-colon, and then grab the string that goes before that character position.
View 4 Replies
View Related
Jun 18, 2014
I'm trying to set up a dashboard with a table and some formulas so that I can just copy a data export into one tab and have the table perform various Vlookups, charts, etc. The trouble I'm having is that some of my cells contain a mix of text, and I'm looking for specific text only. Example:
Address |Tags | Result
Email 1 |t1 t2 t3 t4 | Email 1
Email 2 |t1
Email 3 |t2 impact1 | Email 3
Email 4 |impact2
Email 5 |impact2 t2| Email 5
My data source has thousands of rows, and I'm looking at the Tags column. I want to use a Vlookup that gives me the email when [Tags] contains "t2", but my search is getting confused with "impact2". Is there a formula to search for "t2", but not get confused with "impact2"? I considered using =NOT, but I don't want to exclude cells just because they contain "impact2". If there was a formula for =IGNORE, that would be marvelous. My formulas are getting longer & longer, but they always seem to break in the end.
Basically I'm trying something like:
=IF(OR(ISNUMBER(SEARCH("* t2*",Table1[Tags])),SEARCH("t2*",Table1[Tags])),"Yes","No")
(Once I get the IF figured out, I'll use Vlookup to bring out the Address).
View 1 Replies
View Related
Jan 20, 2009
I'm running a macro with multiple statements for hiding columns, and it has been running well for years, now today i'm getting a error message in the macro while debugging that states "Unable to set the Hidden property of the Range class", and when I reset the macro, and try to manually hide the range, I get the error message "Cannot shift objects off sheet".
View 2 Replies
View Related
Apr 7, 2014
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
View 3 Replies
View Related
Dec 29, 2011
I have two sets of data and am applying an IF statement to each one. What I would like is if the answer is positive, to have the value displayed. If the answer is negative to have a string of text displayed.
They way I have it currently set up is IF statement #1 performs the logical test. If the result is a positive number, it performs a calculation. If the answer is negative it returns a string of text.
IF statement number #2 performs a logical test and if the result is positive it performs a calculation. If the result is negative it returns a 0.
Now, this setup allows me to add the two IF statements together if the results of the logical tests are both positive or if the #1 is positive and #2 is negative. However, I get a #VALUE error is the results are both negative as statement #1 is returning a string of text.
How can I set this up so I can get the string of text as the result if the results of both logical tests are negative?
View 2 Replies
View Related
Dec 12, 2008
I want to return Y or N for each row where a certain word (lets say "Apple") appears anywhere in the reference cell. Therefore in the below example the first 3 rows contain apple (somewhere in that cell) so Y is returned. I can get it to work when apple isd the only thing in the cell but not if it appears with text before and/or after it.
ie:
A B
apple Y
apple orange Y
orange apple Y
pear N
grape N
View 4 Replies
View Related
Jul 26, 2006
I am trying to return a text statement using nested IF statements. In order to find the value in the IF statements, I have to use lookups.
Example: ....
View 14 Replies
View Related
Aug 13, 2014
I want to split a cell with a formula and not use text to columns. I will have a cell a1 for example with 1d 4h 36m 34s in and want to have 4 formulas 1 in
a5
a6
a7
a8
.. so the result will be
1
4
36
34
I know you can split with left, mid and right but don't know how to do it with the desired results.
View 7 Replies
View Related
Feb 26, 2009
i have cells with city and state in them and i'm trying to separate the one column into two columns. the problem is, i'm trying to keep the city name in one column and the state in the other. some of my cells have two word cities like new albany, or upper arlington. the text to columns feature is separating those cells into 3 columns not 2. is there a way to do this?
example:
worthington, oh
upper arlington, oh
text to columns splitting upper arlington into 3 different cells because the only delimiter in the cell is a space. i need to keep upper arlington in one cell and oh in another.
View 7 Replies
View Related
Mar 19, 2014
I would like to highlight the rows in my spreadsheet where columns G and H both contain the text "Yes". I've tried conditional formatting and VBA but can't seem to get either to work for what I need.
View 3 Replies
View Related
Jan 3, 2008
I'm using the the Texttocolumns method in a Macro, and when it runs across a cell without any data I get "Run time error '1004' No data was selected to parse". How would I get passed this? I would like it to just remain a blank range without the error. Here's the code I'm using:
Range("F38").Select
Selection.TextToColumns Destination:=Range("F38"), DataType:=xlFixedWidth, _
FieldInfo:= Array(Array(0, 1), Array(5, 1), Array(13, 1), Array(21, 1), Array(25, 1)), _
TrailingMinusNumbers:=True
View 2 Replies
View Related
Nov 22, 2008
I have three IF statements as below. the problem is if the first statement is true I want it to skip the next two statements or the result will be changed again.
View 2 Replies
View Related
May 26, 2008
Sheet one will contain the following:
DATE A B C D E
2/22/2008TRUEFALSEFALSEFALSERon
2/23/2008FALSETRUETRUETRUEPhill
2/24/2008FALSETRUEFALSEFALSETracy
2/25/2008FALSEFALSEFALSEFALSESharon
2/26/2008TRUETRUEFALSETRUEBill
On sheet two I need to list any date that has three or more true statements with the coresponding name.
EXAMPLE:
2/23/2008Phill
2/26/2008 Bill
View 4 Replies
View Related
Jun 11, 2014
I need a VBA code to create a Text file which should be in the form of DML (my scenario is to get Insert Statements for the Data available in the Excel) from Excel Sheet. And this has to be done by assigning a macro to command button where by clicking on it the DML text file should be generated. Even the syntax for the scenario will do.
View 1 Replies
View Related
Jul 15, 2009
i really want to make it into a range formula but cant work it out.
View 2 Replies
View Related
Nov 29, 2013
New task for work today, which involves creating a spreadsheet for all existing members of an organization. I went to the organization's website and copied all of the names & info, then pasted into a blank spreadsheet. All of the entries were separated into their own rows, but all of the information is only in one column. Take a look at my sample:
(NOTE: does not contain actual names or info) Book1.xlsx
This sample only contains 5 entries, but my actual list contains about 200; if it had just been the 5, I would have been fine with manually separating the information, but for 200 entries I need something much faster. You'll notice in the sample that the company, person, address, phone number, email, and website (when there) are not separated by anything. I know that using Text-to-Columns, I would technically be able to achieve what I need using the Delimited option, but I can't imagine this working without separators. I thought perhaps there might be a way to separate them based off font changes or something? Or maybe some way that I can insert a semi-colon or some kind of separator between the necessary data?
While the font is Arial for the majority of the entries, in terms of font changes: company font size is 12 and color is navy blue; the person's name is size 18, bolded, and dark grey; the address & phone number are size 9 and the color is light gray; and finally, the email & website are also size 9, but navy blue in color. However, you'll notice that two of the five entries are formatted differently. This is because formerly, the entire cell & its text were a hyperlink to the email. I went ahead and removed these hyperlinks, simply using a "Remove Hyperlink" macro walk through I found on the web, but now these cells are uniformly set to Calibri and size 11, my default font setting.
View 6 Replies
View Related
Oct 24, 2007
I am manually scrolling through Excel right now to find all blank cells in column L. Some cells have dates, others are blank. IF L is BLANK, I go to row K and copy/paste row K to row L. IF K is BLANK, I got to row I and copy/paste to row L.
Is there a statement I can put into row L to basically say "If blank copy/paste from row K. If row L and K are blank go to row I and copy/paste to L." ???
View 4 Replies
View Related
Aug 21, 2014
I am trying to check if the dates are equal with multiple if statements! Macro works great if first date is wrong, but if the 2nd or third date is not unequal it does not work anymore.
really tried to do it myself but unfortunately
I added an excelexample
Date in E3 is different (if you type in other date in cell b3 macro does work partially).
View 1 Replies
View Related
Sep 7, 2012
I am trying to create a macro that will add and delete individual cells depending on an IF statement. The code that I created deletes/adds cells regardless of the IF statement.
If Sheet1.Range("F3").Value = Sheet1.Range("G2").Value Then
Range("G2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End
ElseIf Sheet1.Range("F3").Value = Sheet1.Range("G4").Value Then
Range("G3").Delete Shift:=xlUp
End
End If
Also, what is the easiest way to have this code apply to all rows within column F, while adjusting the corresponding cells referenced in column G? The last row within the spreadsheet changes.
View 3 Replies
View Related
Apr 2, 2014
How can I create a multiple if statements that returns a specific response if the cell is blank?
For instance, =if(K6=[date],"Carry Out"), if(K6=[blank],"Break Out"))).
View 3 Replies
View Related
Jul 9, 2009
i'd like when i lookup abt specific in range (4 Columns is my lookup range)
get the results in one column with skipping blanks Cells "Note" Blanks cells it contains fromulas with "" so it blanks but have formulas ....
View 9 Replies
View Related
May 21, 2014
Im trying to pull together a formula to achieve the below. What i have is a spreadsheet to be used by staff in different countries so im looking to make it easy to modify the categories used without the user needing to do anything complex with formulas.
The right hand side of my file (C:E) shows a code for each year we have covered a country with the year as a prefix (2014 - Blah). The left (A:B) is a summary showing the latest year we covered that country.
Current formula:
{=IF(COUNTA(C2:D2)<=0,"","✔-"&MAX(LEFT(C2:D2&"0",4)*1))}
The current formula works fine if you set the columns to where the data actually is but for ease of use i need it to look at the data in rows 1:2 and match up the Continent/Country and then MAX the dates.
View 1 Replies
View Related
Dec 10, 2013
What I would like to do is use the IF function to retrieve data from another sheet called Data which has six columns but has various amounts of data that I would also like to pick as a drop-down menu.
The initial cell has a dropdown populated with data using the Data Validation.
Example would be if the following is selected from the initial drop-down menu and then allows me to select data depending on the previous selection.
PX1 - fetch information from Data T2 to T25 and then select that data which then would allow me to select data again from say column U2 to U9.
Is this possible?
View 5 Replies
View Related
Aug 2, 2006
How do you write an If then statement using a range of cells? I want to construct a logic test using the range of cells A7:A19. I want the logic test to see if the any of the values =1. If the test is true then I want it to display the value in B7:19 (which ever cell corrulates to the cell in column A that has the value of 1) and display the number in the B column.
View 6 Replies
View Related
Jul 27, 2009
I have a column "g" with this conditional formatting:- =A2<>A3 Format Bottom Border.
However I will pass this workbook onto someone else who will fill in the text in column "g". They will use copy/paste text from other cells or columns even other workbooks that will not have the conditional formatting.
I have used Cells > Projection > Locked unchecked then used Tools > Protection > Protect Sheet and checked all. There does not seem to be a way to unlock the cell but protect Conditional formatting. Each time I copy and paste from other non formatted cells it wipes out my formatting.
View 4 Replies
View Related
