Excel : Different Criteria for excel to look

[Solved/Closed]
Report
-
 carman -
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

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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)
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
yea its possible to add a column...but i cant think of any specific formula that is possible to relate the 2 documents. What i can think of is adding one more column (say column k) with a # in, for rows that is blue in color..but im not sure how can i write it as to tell excel to look up the column i want?

What i have done so far is to seperate the "yellow MLR" and "yellow SP" part by formulae like :

=IF(LEFT(B48,3)="MLR",VLOOKUP(B48,'[........xls]MLR'!$A$4:$E$119,4,FALSE))
=IF(LEFT(B48,2)="SP",VLOOKUP(B48,'[........xls]MLR'!$A$4:$E$119,3,FALSE))
And so far it works.

But im not sure if the whole thing can be done by one button? I tried to put the above code in the VBA script write but everytime it ends up with red font..
How exactly could i get it then????
Thanks so much!
at the same time, i want to shade the return data with color shaded, but apparently conditinal formatting cannot be linked to other workbook? So is it not possible to done it by conditional formatting?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
May be I should have asked before, what makes a column Blue or yellow ? Is it based on the data on the sheet ?
No, its not based on the data. But, they are already given in the orginal documents, which means when i make any future update, the color of the row will be told. Thanks.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Apart from that, i would like excel to do it just by using one button...
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I am not sure what you were trying to do in the function

See this
https://authentification.site/files/23295067/2.xls
It is not complete as I am still not sure what you want for condition 3
Thanks! your code works perfectly well!

But i want to ask:
1)In this task, i linked document 1 to document 2. If in the future if i got more seperate documents(eg document 3, 4, ,5, 6....etc) and change their name into document 1. Would the same format be maintained?( i.e. the matching critera ( MLR/SP/Color) change)
Thanks again.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Are you saying that, if Doc 2 is your final doc, ( which does the look up)

A. There might be cases where the look up needs to look at doc1, doc2. doc3... etc

Or

B. Instead of doc1, down the road you would be looking at doc2 but every thing will remain

same

or

C. This I think you are saying. That currect doc1 would be replaced with another doc1 and would the code still work.

In case of A, the answer is that code will need to be modifed and perhaps will break sooner than later due to Text length limitation in a cell.

In case of B, all you have to do is replace the reference of Doc1 with reference to Doc2

In case of C, yes as the formula is using information from a book called doc1, so if you replace exisitng Doc1 by another Doc1, the code will still work. How ever the format of the new book needs to be same. By format I mean the column location. Also the results that came as result of lookup using original doc1 will be lost (unless you have saved them by copy and pastespecial as values)