excel help with formula or something

Copper Contributor

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.

19 Replies

@Linda A. Barnes Heya Linda,

 

It sounds like you're after Conditional Formatting.

@Smitty SmithThanks for the link but it has not help.  I am new to excel so maybe I am not doing it right

@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.

 

 

@Smitty Smith my workbook without donor information   colour .png

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.

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.

@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 

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.

 

  • 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.  

To colour a whole row when a single cell in the row has a specific value, you need to do this:

 

  • Select all rows you want to apply the format to, making sure the current  cell is in your first row of data, i.e. row 2
  • create a new rule using a formula
  • use a formula along these lines

=$A2="something"

 

  • select a fill colour for that rule

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.

 

 

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?

 

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 

@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)

@Linda A. Barnes 

 

Can you post the formula you are using and a screenshot of the Rule Manager dialog. 

Thank you for the help @Ingeborg Hawighorst I think I have nail it but then

I have this problem 

 

 

question.png

 

 

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

 

 

@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".

 

2019-10-03_09-45-50.png

 

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.

 

2019-10-03_09-48-24.png

 

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.

 

2019-10-03_09-53-30.png

 

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.

 

@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  

@Ingeborg Hawighorstso could my helper by my Colour Key Code colour .png

 

I could not work out how to use the colour helper.

 

I have workout how to do this much 

ONE.PNG

 

But still not over ride the No in the don't listed column with the Sold pink colour.

 

Another stupid question
Can I set up Word doc to Print excel information on template with a watermark or do I need to create a Word doc and import excel information on to the template and manually watermark and print each merge doc?

@Linda A. Barnes 

 

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.

@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. 

@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.