Excel : Different Criteria for excel to look

Solved/Closed
abcde - Jun 25, 2010 at 01:51 AM
 carman - Jul 8, 2010 at 08:04 PM
Hello,
Please could anyone help? Attached please find the example.
https://authentification.site/files/23122635/nono.xls

The sheet (Say in document A) is seperated into 3 parts:
part 1: The second column has codes starts with MLR (yellow background) -> look up data on document B ( column 4 & 5).

part 2: The second column has codes starts with SP (yellow background)-> look up data on document B ( column 3 & 4 & 5).

part 3: All the data with blue background.--> look up data on document B ( column 2).


The attached document is already the finished version. However, since data would be updated weekly, how can i let the excel know which data belong to which group.( i.e. if i add a new selection no. to second column (say MLR2001 with yellow bg) , how could i tell excel that it belongs to yellow part and to look up data from document B ( column 4 & 5).

Or are there any method that can allow me to do so? (e.g add an column with # which indicate the group the selction no( column 2) belong to)
Any method is OK as long as i can tell excel whereabout it should look up (column 4 & 5)or
( column 3 & 4 & 5) or ( column 2).

Thanks.

2 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
768
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)
0
Please ignore the previous file i uploaded in the last post.
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!!
0
column B in Document 2 and column A in Document 1 is the data that i want to match up. Thanks!
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
768
Jun 28, 2010 at 05:01 AM
Can you have a formula based column in your "document 2" ( the one with 3 blank columns and where you want to pull the data to). The purposed would be to evaluate the color of the cell to know, what columns would be populated
0
Thanks for your reply, rizvisa1!
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...
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
768
Jun 29, 2010 at 03:13 AM
No the question is can you dedicate a column for a formula. That would be a helper formula to do what you want to do. Some time it is not possible to have any additional column in a sheet because hands are tied and most of the time it is possible to add new columns. So the question is, is it possible to add a new column to sheet
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
768
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


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
0
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!!
0
in additional, what can i do to let excel to return both the color and data (after looking up specific column from document 1) together?
Thanks.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
768
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.
0
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....
0
Apart from that, i would like excel to do it just by using one button...
0