Hi all,
If someone could help with this challenge I would be so grateful.
Basically I have a table like this:
Picture1.jpg
What I want to do is have a specific rule for a specific supplier. So for example:
IF supplier is "IMMINGHAM" AND NETCV is lower then a certain limit - apply my conditional formatting. The problem is I have multiple suppliers with different limits. How do I create something to cover all the secnarios off?
Please help than you!!
Matthew
Last edited by mra1984; 11-30-2013 at 05:57 AM. Reason: attach
Instead of picture please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
put them in a lookup table and you can use that as the reference
also post a sample spreadsheet rather than an image and we can help more
in a new sheet add a lookup
in column A put all the suppliers names and in Column B put the value
IMMINGHAM - 22000
NETCV - 26000
now as a conditional format you cna use vlookup or index/match
=vlookup(the cell with the supplier name in main spreadsheet , sheet2!A1:B100 , 2, false)
this looks up the value in the reference table
now you can test to see if its less than
= cell with the value in main main spreadsheet < vlookup(the cell with the supplier name in main spreadsheet , sheet2!A1:B100 , 2, false)
but if you post the spreadsheet and also which cell(s) do you want to change the colour ? /apply the conditional format
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Hi guys, thanks for the quick replies. I have attached the sheet now but still not sure what to do. I need it to use the rule based on the supplier name. Thanks a lot
The conditional formatting is not so much the problem as that is just the result. It is the how do I apply a different rule based on a different name part that I am struggling with, thanks!
=IF(Table1[[#This Row],[Source]]="IMMINGHAM",IF(Table1[[#This Row],[Net CV]]>=$M$8,"OKAY","WARNING"),IF(Table1[[#This Row],[Source]]="REDCAR",IF(Table1[[#This Row],[Net CV]]>=$M$8,"OKAY","WARNING")))
Is something like this the answer... but the more suppliers I add the bigger it gets!
Ah this doesn't work! When I try to do more than two
please ignore
Last edited by etaf; 11-30-2013 at 06:48 AM.
you can do this a few ways
1) is to use
=IF([@[Net CV]]>=VLOOKUP([@Source],$J$3:$K$6,2,FALSE),"OKAY","WARNING")
in column H
and continue with your cond format
or
2)
you could use the vlookup as a cond frmt
=G5<VLOOKUP(E5,$J$4:$K$6,2,FALSE)
etaf, how do I thank you? this is great! I chose the first option and works great.
Never thought of combining and IF statement against a V-look up.. thank you
Matthew
your welcome
Hi etaf,
I was wondering if you could help with a slight complication on the same document?
I have added another column 'below contract'. So if you look at the rules table - if 'Immingham blend' falls beneath 22,500 then the word 'Penalty' appears but I want to add into the same formula that if it falls beneath 23,500 the word 'Below contract' appears.
So basically I would have two levels in the spreadsheet.
Can you help?
Much appreciated!
Matthew
=IF([Net CV]>=VLOOKUP([Blend],$Q$9:$R$12,2,FALSE),"OKAY","PENALTY")
change to
=IF([Net CV]<=VLOOKUP([Blend],$Q$9:$S$12,2,FALSE), "PENALTY",IF([Net CV]<=VLOOKUP([Blend],$Q$9:$S$12,3,FALSE), "BELOW CONTRACT","OKAY"))
so now we are testing to see if < than and also increasing the Range from Q:R now to Q:S
thank you for helping again, and for replying so quickly!
Cheers
Matthew
your welcome
if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
thanks I have added to your reput. I will mark this as solved now. Cheers again, M
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks