If formula conditional formatting

gazmoz17

New Member
Joined
Sep 18, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi,

We have a colured PDF zone map...diff postcodes are different colours which are different prices with our transport company.
Transport map.JPG


Ive read that you cant do a conditional format and IF combined.

I want to include a formula in end col of current spreadsheet which changes colour per the colour code on the map. e.g. KW postcode goes light green (very top right of map).

I can then report on most popular sales postcode & relevant price incurred.

Im a beginner level excel really. Ive had a play with =IF(ISNUMBER(SEARCH("IV",A1)),"ZONE 7","")

Then was going to call the colours a zone, then from returned zone number set up 7 conditional formatting rules for the colour. But I dont know if I can have multiple postcodes within that formula. Im thinking surely theres a bteer way than this?

Only just joined & forum looks really good, Im sure I'll be posting regularly (simlarily to when discovered autohotkey)

Thanks in advance
Gareth
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,970
Office Version
  1. 2019
Platform
  1. Windows
Im thinking surely theres a bteer way than this?
I would have to agree with that, but without the nuances, finding that way would be difficult.

=IF(OR(ISNUMBER(SEARCH({"IV","KW"},A1))),"ZONE 7","") would work with multiple searches at once, but something to be aware of is that using "W" as the string to find would also be matched to "KW" in A1, the same error would be applicable to any single letter postcode district in A1.

What would be better is to have all of the postcode regions listed in a single column (in a separate sheet if desired) then use vlookup with exact match to identify the region and return the zone.

For conditional formatting, the formula must return either a number other than zero or a logical result of TRUE. Anything else (zero, text, errors) is evaluated as FALSE and the format is not applied.

You would need a conditional format rule for each zone with a rule (for example) of =A1="ZONE A" to apply the correct colours for Zone A, then an additional rule using the same method for each of the other zones.
 

gazmoz17

New Member
Joined
Sep 18, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi,

Many thanks for your time replying jasonb75. Apologies in advance as typing on phone now.

ISNUMBER(SEARCH thanks for the correct formula for multiple postcodes. I understand the error you have highlighted...so there is no method of exact match with isnumbersearch so KW only returns a value and not any postcodes containing W.

The colour codings relate to an additional pdf which has rate tables to do with postcode & shipment weight. Dream solution is I create an input box ( excel amateur here) second box weight and it returns the transport cost.

I’ll have a look into vlookup exact match- thanks.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,970
Office Version
  1. 2019
Platform
  1. Windows
so there is no method of exact match with isnumbersearch so KW only returns a value and not any postcodes containing W.
It would depend on the exact content of the cell, you could possibly do a bit of trickery with wildcards and substitution, at a second look I'm not sure that vlookup would do it either. When I suggested that I was thinking that A1 only contains the postcode region prefix, if it contains anything more that the letters of the first part then it is going to be a little more complex.

There is a link in my signature to the XL2BB add in which you can use to post an example of your sheet layout to the forum with some before and after data so that we can get a better idea of what you're trying to achieve.
 

gazmoz17

New Member
Joined
Sep 18, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Ok no problem. I might be nipping into work tomorrow so I’ll post an example. Yeah I might be asking for too much, certainly with the split postcodes for the same region prefix which are all the listings top left of the map.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,970
Office Version
  1. 2019
Platform
  1. Windows
Here's a quick example of the way that I would do the postcode lookup, the list on the left would need to be in alpha-numeric order. In order to keep it tidy, I would have that list in a separate sheet. You could easily add extra columns to it for additional information, rates, etc if they vary by postcode rather than Zone.

If rates are set by Zone, then I would use a second, smaller table for that in order to make it easier to update as needed.
Book2 (version 1).xlsb
ABCDE
1PostcodeZone
2ABZone GAB1 1ZZZone G
3AB10Zone FAB15 1ABZone F
4AB30Zone GB1 1AAZone C
5ALZone D
6BZone C
Sheet2
Cell Formulas
RangeFormula
E2:E4E2=LOOKUP(D2,$A$2:$A$6,$B$2:$B$6)
 

Watch MrExcel Video

Forum statistics

Threads
1,112,772
Messages
5,542,436
Members
410,552
Latest member
Yogesh977
Top