Peter Davison
Active Member
- Joined
- Jun 4, 2020
- Messages
- 444
- Office Version
- 365
- Platform
- Windows
I have a formula -
=IFERROR(XLOOKUP(UNIQUE(FILTER(BE6:BE100,BE6:BE100<>"")),BE6:BE100,$BC6#&" - "&ROUND(BE6:BE100,0),""),"")
that returns text (column BC) and joins a number to it (column BE) then removes blank rows to get the list together
e.g. result as a spill
What I want to do is conditionally format each cell based on just the number part
So I tried using the following formula to extract the number in the "Format Values where this formula is true" section using
=RIGHT(BR6, LEN(BR6) - MAX(IF(ISNUMBER(MID(BR6, ROW(INDIRECT("1:"&LEN(BR6))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(BR6))), 0)))>(100+$BG$!)
Cell BG1 is a user number e.g. 20 so it should only format colour if the number is over 120
This doesn't work so I wondered if this can be achieved or not.
Any help would be appreciated.
Thanks
=IFERROR(XLOOKUP(UNIQUE(FILTER(BE6:BE100,BE6:BE100<>"")),BE6:BE100,$BC6#&" - "&ROUND(BE6:BE100,0),""),"")
that returns text (column BC) and joins a number to it (column BE) then removes blank rows to get the list together
e.g. result as a spill
North West - 303 |
South East - 33 |
West Midlands - 156 |
Yorkshire and The Humber - 428 |
So I tried using the following formula to extract the number in the "Format Values where this formula is true" section using
=RIGHT(BR6, LEN(BR6) - MAX(IF(ISNUMBER(MID(BR6, ROW(INDIRECT("1:"&LEN(BR6))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(BR6))), 0)))>(100+$BG$!)
Cell BG1 is a user number e.g. 20 so it should only format colour if the number is over 120
This doesn't work so I wondered if this can be achieved or not.
Any help would be appreciated.
Thanks