If formula conditional formatting

gazmoz17

New Member
Hi,

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

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)

Gareth

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

Replies
3
Views
37
Replies
5
Views
307
Replies
5
Views
476
Replies
2
Views
298
Replies
2
Views
595