Excel : Different Criteria for excel to look
Solved/Closed
Related:
- Excel vba msgbox when cell meets criteria
- Number to words in excel without vba - Guide
- Enable vba in excel - Guide
- Vba case like - Guide
- Excel date format dd.mm.yyyy - Guide
- Excel free download - Download - Spreadsheets
2 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 25, 2010 at 04:08 PM
Jun 25, 2010 at 04:08 PM
Could you also post your 2nd document too as well as a sheet that shows how data would initially look like (before you had this finished product)
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 30, 2010 at 03:52 AM
Jun 30, 2010 at 03:52 AM
I am not clear on the look up part.
This is what I understood, this is how doc A should be populated
1. If on Doc A, color is yellow, and value in its B cell start with a MLR, then dont populate its column 3 and populated its column 4, 5 by getting values from (DocB - column D and E)
2. If on Doc A, color is yellow, and value in its B cell start with a SP, populated its column 3, 4, 5 by getting values from (DocB - column C, D and E)
3. If on Doc A, color is blue, and then I am lost
Any how this is a helper function. It will return the color name
so you can use it like in a new cell as
=color(a2), it wil show the color of a2
and you can have lookup as
if (AND(left(b2,3)="MLR", K2="yellow"), vlookup(....), if(AND(left(b2,3)="SP", K2="yellow"), vlookup(....), IF(color="blue", vlookup(...),"")))
also you may consider to change the function so that instead of color it returns you column tolook for.
Hope it helps
This is what I understood, this is how doc A should be populated
1. If on Doc A, color is yellow, and value in its B cell start with a MLR, then dont populate its column 3 and populated its column 4, 5 by getting values from (DocB - column D and E)
2. If on Doc A, color is yellow, and value in its B cell start with a SP, populated its column 3, 4, 5 by getting values from (DocB - column C, D and E)
3. If on Doc A, color is blue, and then I am lost
Any how this is a helper function. It will return the color name
Function ColorName(Cell As Range) As Variant
Dim vColor As Variant
vColor = Cell.Interior.Color
Select Case vColor
Case Is = 16777164
ColorName = "blue"
Case Is = 10092543
ColorName = "yellow"
Case Else
ColorName = vColor
End Select
End Function
so you can use it like in a new cell as
=color(a2), it wil show the color of a2
and you can have lookup as
if (AND(left(b2,3)="MLR", K2="yellow"), vlookup(....), if(AND(left(b2,3)="SP", K2="yellow"), vlookup(....), IF(color="blue", vlookup(...),"")))
also you may consider to change the function so that instead of color it returns you column tolook for.
Hope it helps
criteria 3 is to look up data on document 1 column 2 if the color shade is blue in document 2.
i tried the above method but everytime error pops up.
The code that i put in for a new cell(say C2):
=if (AND(left(b2,3)="MLR", K2="yellow"), vlookup(b2,[1.xls]Sheet1!$A$3:$E$150,4,false), if(AND(left(b2,2)="SP", K2="yellow"), vlookup(b2,[1.xls]Sheet1!$A$3:$E$150,3,false), IF(color="blue", vlookup(b2,[1.xls]Sheet1!$A$3:$E$150,3,false),"")))
and i have put the VBA script as sugguested above but the thing just simply doesnt work.
How can i make it work?Thanks!!
i tried the above method but everytime error pops up.
The code that i put in for a new cell(say C2):
=if (AND(left(b2,3)="MLR", K2="yellow"), vlookup(b2,[1.xls]Sheet1!$A$3:$E$150,4,false), if(AND(left(b2,2)="SP", K2="yellow"), vlookup(b2,[1.xls]Sheet1!$A$3:$E$150,3,false), IF(color="blue", vlookup(b2,[1.xls]Sheet1!$A$3:$E$150,3,false),"")))
and i have put the VBA script as sugguested above but the thing just simply doesnt work.
How can i make it work?Thanks!!
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jul 5, 2010 at 09:06 AM
Jul 5, 2010 at 09:06 AM
Could up post a book with the macro that you have used, and two sheets. on one sheet show how the data originally is and on the other show how you want the data to be. From what I recall, my test was successful. May be I made a mistake in the code or may be I am misreading some thing. So again if you could post a book with macro and two sheets at some share site post back the link.
For macro, all i did is to put the code you mentioned above.
I could only get the final version ( https://authentification.site/files/23283024/3.xls simply be using VLOOKUP. But, since the document is not capable of automatic update when i enter new data in document 2, this is not my final goal.
what i have tried so far, is putting the code you mentioned above( set as macro) and place
=if (AND(left(b2,3)="MLR", K2="yellow"), vlookup(b2,[1.xls]Sheet1!$A$3:$E$150,4,false), if(AND(left(b2,2)="SP", K2="yellow"), vlookup(b2,[1.xls]Sheet1!$A$3:$E$150,3,false), IF(color="blue", vlookup(b2,[1.xls]Sheet1!$A$3:$E$150,3,false),"")))
in one of the cell. However, not only the code doesnt work, VBA shows the error message : expected end sub.
My aim: Excel automatically find the matching cells (and column) and fill in the background color,according to the name and cell color of documen 2. And capable of future update when i add in more data.
Two original souces:
https://authentification.site/files/23160746/1.xls
https://authentification.site/files/23160749/2.xls
I know its confusing, but i am a total new to VBA....
I could only get the final version ( https://authentification.site/files/23283024/3.xls simply be using VLOOKUP. But, since the document is not capable of automatic update when i enter new data in document 2, this is not my final goal.
what i have tried so far, is putting the code you mentioned above( set as macro) and place
=if (AND(left(b2,3)="MLR", K2="yellow"), vlookup(b2,[1.xls]Sheet1!$A$3:$E$150,4,false), if(AND(left(b2,2)="SP", K2="yellow"), vlookup(b2,[1.xls]Sheet1!$A$3:$E$150,3,false), IF(color="blue", vlookup(b2,[1.xls]Sheet1!$A$3:$E$150,3,false),"")))
in one of the cell. However, not only the code doesnt work, VBA shows the error message : expected end sub.
My aim: Excel automatically find the matching cells (and column) and fill in the background color,according to the name and cell color of documen 2. And capable of future update when i add in more data.
Two original souces:
https://authentification.site/files/23160746/1.xls
https://authentification.site/files/23160749/2.xls
I know its confusing, but i am a total new to VBA....
Jun 27, 2010 at 09:33 PM
These two are the original copies:
https://authentification.site/files/23160746/1.xls
https://authentification.site/files/23160749/2.xls
There are 3 empty columns in the document "2", and i have to insert the data from document "1" . The document "2" is seperated into 3 parts as i mentioned in the last post. And i want excel to look up automatically the different colums according to whether its " yellow+start with M" or "yellow +start with S" or "blue". Because this document needs to be update weekly ( add more values on column 2 of document 2) , i need excel where to look up each time and have the background of the tab shade accoring to document 1.
I know its quite confusing, but i really appreciate any help!
Thanks!!
Jun 27, 2010 at 09:42 PM
Jun 28, 2010 at 05:01 AM
Jun 28, 2010 at 08:16 PM
I dont think i can create a formula based column in document 2 because they are all random numbers that start with either MLR or SP. However, the color shades and intials (MLR/SP) are already given in document 1 and all i want to acheive is to let excel know where to look up automatically ( in document 1) when given a new number in document 2 column B. To be specific, when i type the new number, i want excel to automaitcally look up the data and fill it up for me, so that i don't have to VLOOKUP every time i receive a new number...
Jun 29, 2010 at 03:13 AM