Thanks: 0
Needs Pictures: 0
Picture(s) thanks: 0
Results 1 to 14 of 14
-
13th September 2019, 05:47 PM #1
Question on Conditional Formatting Excel 2016
Ok, I give in. In the days of XP/Office 2003 I was a bit of a whiz with Excel, but it seems to be a little more complex than it needs to be in the 2016 version.
In particular, Conditional Formatting...which used to make so much sense....doesn't make any sense or work intuitively any more, so I need a little help please....
This is what I have:
Capture.JPG
Now there are several of those black cells on the left that should obey those rules, but none of them do. Have tried various other illogical combos.....nup. They are all within the correct rows range of 11 to 39.
And what does "Stop if True" mean?
(I will have a follow-up question on how to get another cell in the row to follow the same formatting - from the value in "S", but let's wait on that.....)
-
13th September 2019 05:47 PM # ADSGoogle Adsense Advertisement
- Join Date
- Always
- Location
- Advertising world
- Posts
- Many
-
13th September 2019, 06:15 PM #2SENIOR MEMBER
- Join Date
- Feb 2016
- Location
- Perth WA Australia
- Posts
- 829
I think your issue is your conditional formatting is text rather than number, note the quotation marks.
Stop if true is exactly that, it'll stop if other conditional formatting holds true. I've never had a need to use it, but its commonly used when you want to hide some conditional formatting when another criteria holds true.
In terms of getting another cell to follow the same use the format painter, which can be found under the home tab, directly below the "home" text, it looks like a paint brush
-
13th September 2019, 06:25 PM #3
If I leave only one rule for the first cell it still doesn't work. It used to be that it would follow those conditions down the list, and I thought it still did that.
-
13th September 2019, 06:25 PM #4Senior Member
- Join Date
- Jun 2015
- Location
- Mexico. Actual Mexico not Victoria.
- Posts
- 418
Is it because you have a formula as opposed to a cell value?
-
13th September 2019, 06:32 PM #5SENIOR MEMBER
- Join Date
- Feb 2016
- Location
- Perth WA Australia
- Posts
- 829
-
13th September 2019, 06:36 PM #6SENIOR MEMBER
- Join Date
- Feb 2016
- Location
- Perth WA Australia
- Posts
- 829
Change all your conditional formatting using this format and it'll work fine
Conditionalformatting.png
-
13th September 2019, 06:41 PM #7
Harrrr....while you were posting I got to the same conclusion:
Capture.JPG
and she works!
I got to that by adding three "Greater than" rules, and then edited rules and that is what they look like now. I suspect that I'll be able to bumble through that other question myself (to format other cells on condition of another).
Thanks for your help!
-
13th September 2019, 06:45 PM #8SENIOR MEMBER
- Join Date
- Feb 2016
- Location
- Perth WA Australia
- Posts
- 829
no worries... sadly i spend more time in Excel than i do in the shed...
-
13th September 2019, 07:28 PM #9
I used to too....
Perhaps another question then please?
If I have a cell with a value of "1,2,5,6" can you think of a formula that will count how many commas there are in that? Nothing I could find in the list.....
I could write a Function for it but Excel is very much "use it or lose it"...and I've lost it. (My old Personal.xls seems to have disappeared, and I used to be able to find previous examples of code I had written, and then it all came back to me)
-
13th September 2019, 08:11 PM #10SENIOR MEMBER
- Join Date
- Mar 2018
- Location
- Sydney
- Posts
- 469
-
13th September 2019, 08:23 PM #11
-
13th September 2019, 08:33 PM #12SENIOR MEMBER
- Join Date
- Mar 2018
- Location
- Sydney
- Posts
- 469
Hi FF,
Use "substitute".
The full formula would be something like =LEN(cell) - LEN(SUBSTITUTE(cell, "," , "")
Regards
Adam
Sent from my SM-G950F using Tapatalk
-
13th September 2019, 08:38 PM #13
Ahhhh, lovely Adam, thank you. As usual the question box in insert function is not very helpful - "Find and Replace" gets a response of "Please rephrase your question".....when Excel knows very very well what Ctrl F is for.....
-
13th September 2019, 08:47 PM #14SENIOR MEMBER
- Join Date
- Mar 2018
- Location
- Sydney
- Posts
- 469
Glad to help.
I gave up using excel help a while ago, Microsoft doesn't write it's help guides in clear English. Google is awesome for problems like this.
That particular one i needed for clearing some payroll data (changing the order of surname firstname) for a project i was helping on.
Regards.
Adam
Sent from my SM-G950F using Tapatalk
Similar Threads
-
Excel Question
By Chris Parks in forum COMPUTERSReplies: 5Last Post: 30th July 2011, 12:03 AM -
Tankstand needs Excel help
By Tankstand in forum COMPUTERSReplies: 5Last Post: 3rd October 2008, 12:21 PM -
MS excel
By Harry72 in forum COMPUTERSReplies: 4Last Post: 30th July 2008, 12:36 AM -
Excel question
By Daddles in forum COMPUTERSReplies: 26Last Post: 6th June 2007, 12:29 AM