Need help with conditional formatting and some formulas

[mtram]

Hi,

Steve here, so hello everyone. I am quite new to excel and using Excel 2007 so need a little guidance. In order for you to understand what I require I will need to give a bit of information.

I am working on a project that is needed to record sales on a newspaper dispenser that has 11 (eleven) advertising spaces on each dispenser, these available advertising spaces are labelled: AD1,AD2,AD3,AD4,AD5,AD6,AD7,AD8,AD9,AD10,AD11 on each dispenser and there are a total of 40 dispensers.

I have included an attached example of what I have achieved so far (page 1 is titled "My version")and on the second page (titled "How I would like it to look") of the example is how I would like it to look but I am stuck on part of the formulas to complete it. On page 2 I want to have an excel sheet that will allow me to input clients company's names in column A in cells A2 to A41, then in column B in cells B2 to B41 I wish to be able to add the number(s) of all of the dispensers any particular client has placed advertising on. I then wish those advert spaces to be recorded in the corresponding cells in columns O to Y and from rows 2 to 41 to record separately the taken spaces and indicate they are taken by the word "NO" and the background coloured red, whilst the still available spaces will indicate "YES" with the background coloured green. I have filled in a couple as examples to show what I mean.

In the example excel document it will be more obvious to the set out on page 2.

Thanks in advance for your help.

Steve.

[DBY]

Hello Steve
If I've understood you correctly, I think this could be done using the SEARCH function. However, a problem arises as it treats AD1, AD10 and AD11 as the same value, so I've used the SUBSTITUTE function to deal with this and formatted the relevant cells across O1:Y1. See attached.

Hope this is something like you're looking for.
DBY

[mtram]

Hello DBY,

Thank you so much, this is exactly what I was looking for. Your expertise is much appreciated. Thanks again.

Steve

[DBY]

Thanks for the feedback. The formula is a little convoluted but appears to work.

DBY