Sep 29 2019 09:35 PM
my excel doc I have a key code in the document I can't remember all the colours but need to lighten all the colours and I would like colours across the row, so when I open I can see which document are check to be print sold and relist currently list or demage and not for sale. I was told I need to post here because support help could not help.
Sep 30 2019 01:01 PM
Sep 30 2019 01:34 PM
@Smitty SmithThanks for the link but it has not help. I am new to excel so maybe I am not doing it right
Sep 30 2019 01:40 PM
@Linda A. Barnes Can you be a bit more specific about what you're trying to accomplish? Feel free to upload an example workbook (no sensitive information) to OneDrive, and post a link back here, or post some screen shots of what you've got now, and what you'd like to do.
Sep 30 2019 02:12 PM
@Smitty Smith my workbook without donor information
I am trying to create a quick read by colour key but I can
the Pink to a light shade and Red to a lighter shade or tint
I would like the whole row hightened as you can see
I have manual format with fill to try to show you if you scroll the document. You will notice I have format with Dollar signs in the doc this is because I will be merging a word doc and I have also format 00001 this is the way I can identified the item in storage. I am hoping you can sell the headers match with the exception of Checked Y/N spelling and grammar it has the heading checked y/n. I hope you can understand what I am trying to achieve. This is for a fundraiser . so not a business.
Sep 30 2019 02:16 PM
Hello @Linda A. Barnes ,
the document that you shared in you question seems to be your original file with all your company data and links. I urge you to edit your question and remove that link. Then create a small sample file with just a few rows of dummy data and upload that here with the paper clip icon that shows below the reply box.
Explain the logic of what you want to achieve, e.g. "if cell A1 has the word "rhabarber" the whole row should turn orange" -- along these lines.
Sep 30 2019 02:57 PM
@Ingeborg Hawighorst If column D is Y then row is light pink I can't recall my colour key I Column Q is y then row is light orange If Columm s has as a number whole row is light blue If Column T is has R light Blue (might just remove column S) column U with override everything if sold in the column with become light grey Column AF has n or no with be green I Y or yes then will be white no fill. I hope this helps if I can shown in a video I the first couple I can try for my self and change colours myself
Oct 01 2019 02:29 PM
Hi @Linda A. Barnes , I don't know if it's the forum software, but it's really hard to read that description because there is no punctuation and no new lines, so I re-wrote it as a bullet list, but I'm not sure I got it right.
To colour a whole row when a single cell in the row has a specific value, you need to do this:
=$A2="something"
The important bit here is the $ sign before the column letter. This will ensure that only that column is checked for the condition.
Create a rule for each colour. Change the order in which the rules are applied in the Rules Manager dialog and use the "Stop if True" tick box to manage which rules will override other rules.
With so many different columns having conditions, it may be a bit tricky, so play with the order of the rules until things are looking all right.
Oct 01 2019 03:06 PM
thank you @Ingeborg Hawighorst
I will give it a go and let you know in videos I have watch it use ("yes") do I need brackets?
Oct 01 2019 04:05 PM
I think I am doing something wrong the formula is not working for me and the whole row is not highlighting not even the column it highlighted . @Ingeborg Hawighorst
Oct 02 2019 12:32 PM
@Linda A. Barnes Do you need brackets? That depends. You don't need brackets for this type of formula
=$A2="something"
This kind of text comparison needs the text inside double quotes and it is not case sensitive.
If you want to check if A2 has a number, then you need brackets for the IsNumber() function like this
=IsNumber($A2)
Oct 02 2019 12:34 PM
Can you post the formula you are using and a screenshot of the Rule Manager dialog.
Oct 02 2019 12:45 PM
Thank you for the help @Ingeborg Hawighorst I think I have nail it but then
I have this problem
Column D I have yes and in column AF Yes and I only want the condition to identify column D
and I want both Q and R present for another condition
and if Column U has the word Sold I want all the conditions to be ignore
I was thinking it
=$D2="yes" true and $AF="Yes"=Not True
Oct 02 2019 02:05 PM
@Linda A. Barnes I think the problem here is that each "status" of the product is held in a different column with Yes or No in the value. That means that several different (and sometimes contradictory or confusing) statuses can be applied to the same item and that makes the logic for the conditional formatting very complex. Which column should "win" when several columns are "yes"? That requires a bit of planning. In the following screenshot, the order of the rules in the rule manager has been changed. Red will win if both green and red are "yes", blue will win over the other two.
You can add another rule for "Sold" and make the background white and put it at the top of the rules list, or you can add a condition in each rule with the AND function like =AND($E2<>"Sold",$D2="yes"). Then the rule will not be applied if column D has the word "sold".
The order of the rules would not matter if there was just one column for "Status" and the values were what the status of the product is, instead of yes or no, e.g., "listed", "on hold", "not to be listed", "sold", etc. Then you can use rules that only look at the value in the Status column to determine the color of the row. The screenshot below shows this in action. Note how all rules look at column B only.
If you don't want to change the whole setup of your list, you can still use this approach by using a helper column with a formula. All the logic happens in that formula. In the screenshot below you see the helper column and the formula I used for this scenario is
=IFS(E15="sold","",D15="yes","blue",C15="yes","red",B15="yes","green")
Your formula will be different. I'm not quite sure what the business logic is when it comes to your spreadsheet, but I'm happy to help you work that out. In the screenshot below, the conditional formatting rules just look at the helper column F. This column can be hidden once everything has been set up.
A helper column is a lot easier to troubleshoot than the order of the rules in the rule manager (like in the first screenshot), especially if you need to make changes later on. You can see clearly what the result value is for each row and you can tweak the formula until it is right.
I'm attaching the sample file I used for the screenshots.
Let me know if that helps.
Oct 02 2019 02:21 PM
@Ingeborg Hawighorstthank you I will have a go I am thinking if it doesn't work maybe I look at check and not check for grammar checked or not instead of yes and no maybe the relist not relist what are your thoughts It is to not business but a fundraiser and some team will focus on spelling and grammar. Which items are listed for sale and sold items and items that are damaged or maybe on hold awaiting payment or pick. I am trying to make it easy to see where we are at but I have know idea how to make it work.
regards linda
Oct 02 2019 02:28 PM - edited Oct 02 2019 05:29 PM
@Ingeborg Hawighorstso could my helper by my Colour Key Code
I could not work out how to use the colour helper.
I have workout how to do this much
But still not over ride the No in the don't listed column with the Sold pink colour.
Oct 02 2019 06:19 PM
Oct 02 2019 07:46 PM
Please don't stuff other questions into this question.
About the helper column: As you can see in the file I attached, and as I have explained in my post, the helper column contains a formula with the logic that returns a key word. That key word is then evaluated buy the conditional formatting rule.
It may take some time to wrap your head around all this if you are new to Excel. This is a complex conditional formatting issue. You may want to start with smaller bites in a test file if you find this too hard. Work your way from simple to complex and once you've understood how it works, you can use the technique on your real workbook.
Oct 02 2019 09:12 PM
@Ingeborg HawighorstHi I have highlight most I could not work how to do it I was wondering if you had a video link. I have manage to highlight most except for if column c and column U were true and I want column U to be the one to taking the priority over column C condition.
Oct 03 2019 01:15 PM
@Linda A. Barnes If you have it working for some conditions: Great. So now you know how to do that.
There will be no video to show you how to solve your specific issue.
Which of the three approaches I showed in the sample file I posted above did you use?
If column U should "win" when both columns C and U check out true, then
- if you use rule order, move the column U rule above the column C rule
- if you use a helper column with a formula, post the formula.