Remove pattern from cells only if another cell has a specific selection from dropdown list

Hi All, I'm not sure if i have the correct forum but this is the only one i could think of that this might apply to.

I have a spreadsheet which acts like a schedule for a team if managers. Across the top I have the times of the day running from 8am until 8pm in 1hour intervals as seen in the attachment 'Screen1'. As you will see the schedule has a pattern running accross it which is to indicate that the slots are not available to book. Towards the bottom i have a table which has a dropdown pulling it's list from a worksheet called 'LData', To give an example there are 8 times altogether in the drop down list and they are formated as follows:

09:00-17:00
10:00-18:00

What i need is that if a specific value from the dropdown is selected then the corresponding cells will have the pattern removed to show that this slot is now available. How this should look is shown in attachment named 'screen2'

So to clarify, if the drop down selection shows 09:00-17:00 then the cells in the schedule that depict the hours 09:00-17:00 ill have the pattern removed automatically. I would normally have done this via a conditional format however working from a business pc i am restricted to excel 2003 which only allows 3 conditional formats whereas i need 8 at a minimum so i am thinking it will probably need to be a VBA code?

Hope this all makes sense but if not then please ask. Hopefully one of you can help.

Thanks

screen1.jpgscreen2.jpg

Originally Posted by mook25

I would normally have done this via a conditional format however working from a business pc i am restricted to excel 2003 which only allows 3 conditional formats whereas i need 8 at a minimum

I'm pretty sure it can be done with one...

Hi,

Thanks fopr your response. I think that look very much like what i want. Unfortunately i can't quite see the formula in the image. Is it possible you could type it here so i could see more easily? Also where would i enter this formula as can't seem to find the menu to bring up the box shown in the image.

Thanks

Originally Posted by mook25

Unfortunately i can't quite see the formula in the image. Is it possible you could type it here so i could see more easily?

Yeah, images are rubbish, aren't they? It would be much easier if I'd posted the Excel workbook instead, wouldn't it?

Can you see the hint there?

My sample workbook is attached - the formula is just defined in conditional formatting.

ha ha ok point taken I've added the sheet as an attachment now sorry, I've had to remove some sheets to get it to upload here. The only thing i would say is that I am restricted to excel 2003 and i know that only allows for 3 conditional formats whereas i would need 8 as a minimum so i'm not sure if this will do what i need?

for forum.xls

also to add a little bit more to the mix i would need this to work for each individual agent in the table found in cells C56:X63 as can be seen in the spreadsheet i attached a minute ago. At present there are 7 agents however this may increase in the future

Is the format fixed? Things like merged cells make something like this much, much harder. Would it be possible to have a hidden sheet running the formula and then use the existing sheet as a display?

Also, while I'm still convinced that only 1 conditional format is required, the cells in question seem to have a number of conditional formats applied already - do you actually have any of your 3 conditional format slots free?

The 3 conditional formats already applied can actually be removed as i have put together a VBA script that will format the cells based on what event dropdown is selected in cells D6:AY52 of the schedule part, they were added at an earlier stage and I just havent removed them as yet.

The sheet as you see it will be how it stays so is fixed in that sense however at some time in the future i may need to add more agents to box at location C56:X63 but i can deal with that when we come to it, maybe i'll just add a few more empty cells into it now so as to overcome that problem from the outset.

I don't mind if there is a hidden sheet running the formula however i'm not 100% how i would do that?

OK, here's what I did:

1. Starting in cell D4 I put the time 08:00 and then put 15 minute increments across from there to the end of the table. This means that a formula can row 4 for the time, rather than having to calculate it from a combination of merged cells.

2. I selected D6:AY12 and conditionally formatted them with the formula:

=AND(D$4>=VALUE(LEFT(INDEX($D$57:$AA$63,MATCH($C6,$C$57:$C$63,0),MATCH($A$4,$D$56:$AA$56,0)),FIND("-",INDEX($D$57:$AA$63,MATCH($C6,$C$57:$C$63,0),MATCH($A$4,$D$56:$AA$56,0)))-1)),D$4<=VALUE(MID(INDEX($D$57:$AA$63,MATCH($C6,$C$57:$C$63,0),MATCH($A$4,$D$56:$AA$56,0)),FIND("-",INDEX($D$57:$AA$63,MATCH($C6,$C$57:$C$63,0),MATCH($A$4,$D$56:$AA$56,0)))+1,255)))

Because of the format of your sheet the lookup for the day will have to be different for each block of data. For example, when conditionally formatting D14:AY20 you'll have to replace $A$4 with $A$13. I could have done a calculation to get the days instead, but this would have limited your ability to add new rows in future.

That should work, and as long as the names appear in the main data and the table at the bottom you can add new rows as and when you wish.

Ok I think i'm getting a little confused now due to some of your selections not selecting certain parts of the sheet I thought it would. I think i have updated the spreadsheet now to reflect what you said would need to be done regards putting the time in cell D4 then 15 minute increments from there on.

I have attached an updated version. Could you have a quick look and see if this is correct and whether the formula supplied still corralates correctly due to the added columns?

Thanks

for forum-.xls

...also it doesn't appear to let me input this formula into the conditional formating by way of copy and paste?

OK, here's your workbook back with things fixed.

Every cell in row 4, from column D to column BP needs to have a time in - the formula won't work otherwise. The cells can be formatted so that the text is the same colour as the background, so the times won't be seen, but they need to be there.

I've put the formula in for the conditional formatting for Monday - it will need to be applied to the other days as well, with the reference to $A$4 replaced as appropriate.

excellent thank you so much. Is there any way to enter the formula in by copy and paste as I can copy the formula but when i hit paste in the box in conditional formatting nothing happens?

I couldn't get it to paste either - one of those Excel things, I guess.

I'd suggest doing what I did:

1. Record a macro of you setting a trivial conditional format

2. Edit the macro so that it puts in the big formula instead

3. Run the macro

It's a bit of a PITA, but easier than re-typing that formula over and over.

Thank you again. you've been a great help. This should sort everything for now but will be in contact again if I need a hand. Once again, thank you for all your help i think i would have gone mad trying to figure this out myself. lol

Hi again,

I've just noticed a small error in the formula you added to my spreadsheet i'm hoping you may be able to fix for me. Basically i noticed when 9-5 is selected it only open up to 4.45 and the same on 12-8 it only opens up to 19:45, all the others are working as they should.

I tried finding the macro you had created to get the formula in so that i could get the full formula so i could sort out the other blocks of data but was unable to find it so is it possible you could post the full formula here so that i can add it to the other cells?

Thanks

I've also tried entering the formula via the macro workaround you mentioned but it isn't letting me run it saying there is an error. I simply removed the trivial formula i entered in the macro which appeared between the speech marks and then replaced with your formula making sure not to remove the speech marks?

The speech marks will probably throw it, because the macro will be expecting a string, so you'll have to double up the speech marks to have them included in the macro.

The other error is puzzling, as it's the same formula for all of the time ranges. Are the times on row 4 correct? They're what drive which cells are formatted.

So i need to double up the speech marks throughout the formula or just at the beginning and end of it?

The times in row 4 all look ok from what i can see, I'm using the sheet that you uploaded yesterday and can't see any discrepancies

managed to sort out the error, just struggling with the macro insert now. sorry to bug you.

Sorry for the delay replying - busy day.

What I did yesterday was:

1. Inserted a new sheet into your workbook (Sheet1)

2. I pasted my formula into cell A1 of that sheet, editing it as required

3. I selected the cells on the main sheet to conditionally format and ran this macro:

Please Login or Register  to view this content.

So give that a go and let me know if you have problems.

Looks like this is a compatability issue between 2007 excel and 2003. I have excel 2010 at home so will attempt your workarounds at home and see if i can sort from there unless you know any workarounds for 2003?

Can anyone else help with this? I'm desperate to get this sorted and unfortunately the processes that Andrew has suggested appear not to work in excel 2003?

Originally Posted by Andrew-R

Sorry for the delay replying - busy day.

What I did yesterday was:

1. Inserted a new sheet into your workbook (Sheet1)

2. I pasted my formula into cell A1 of that sheet, editing it as required

3. I selected the cells on the main sheet to conditionally format and ran this macro:

Please Login or Register  to view this content.

So give that a go and let me know if you have problems.

Is the formula you posted earlier correct as i notice it has cell ranges within the formula included as AA however looking at the conditional format via the excel spreadsheet, what i can see of it, i see those cell ranges as AD. does this mean you have changed the formula slightly which could be why it is not working for me? You mention in your post you edited the formula as required, it's a bit of a long formula and i'm not really understanding what it is doing so any help would be appreciated

Thanks

Managed to get someone else to look at this for me and they have solved it. In the end he copied the main table below the original one, simplified the formula as per below then hid the added table. All working perfect now.

=AND(H$89>=VALUE(LEFT(INDEX($D$57:$AH$63,MATCH($C91,$C$57:$C$63,0),MATCH($A91,$D$56:$AH$56,0)),5)),H$89<=VALUE(RIGHT(INDEX($D$57:$AH$63,MATCH($C91,$C$57:$C$63,0),MATCH($A91,$D$56:$AH$56,0)),5)))