If formula conditional formatting

[gazmoz17]

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)

Thanks in advance
Gareth

 

[jasonb75]

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]

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]

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]

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]

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
	A	B	C	D	E
1	Postcode	Zone
2	AB	Zone G		AB1 1ZZ	Zone G
3	AB10	Zone F		AB15 1AB	Zone F
4	AB30	Zone G		B1 1AA	Zone C
5	AL	Zone D
6	B	Zone C
Sheet2

Cell Formulas
Range	Formula
E2:E4	E2	=LOOKUP(D2,$A$2:$A$6,$B$2:$B$6)

 

[gazmoz17]

Hi,

I have managed to solve this prob finally...how time flies! Thanks for your help Jasonb75.

This is a separate part of that prob if anyone could help. Tried using the XL2BB addin, downloaded but dont know how to make the addin active so can use it and see it on toolbar.

*Using this formula to return letters prefix of postcode before space (so no number).

Would love an IF formula so if Split postcode return Postcode plus numbers before space e.g. OL12. Else OL as formula already does*.

Many Thanks
Gareth

 

[gazmoz17]

Hi,

Ive manged to do it with 2 different formula but dont know how to combine into an if statement.


OL2 5JD

Return OL [Form 1]

=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)

Return OL2 [Form 2]

=LEFT(A2, FIND(" ", A2)-1)

"Splitpostcode" in col B then apply if formula. Apply Form 1 if not splitpostocde and apply form2 if is splitpostcode present.

=IF(ISNUMBER(SEARCH("Splitpostcode",B6)),LEFT(A6,FIND(" ",A6)-1))

The above works for From2 to return OL2 when "Splipostocde is present in col B. But dont know how to right rest of if statement....so if col B doesnt contain the text "Splitpostcode" then applu Form 1 and return OL.

Thanks

 

[jasonb75]

Perhaps

=LEFT(A2,MIN(FIND(IF(B2="Splitpostcode"," ",{0,1,2,3,4,5,6,7,8,9}),A2&" 0123456789"))-1)

 

[gazmoz17]

Argh superb many thanks Jason. I'm guessing I did'nt give it the option of if blank.

I need to reformat my sort of working solution to the first question and then see if can be streamlined. You were correct though there was no other option but to list all the split postcodes. Only revisited ityesterday and all of a sudden index match clicked in my head.