Excel Excel: How do I put a formula in a drop down list

[carminedi]

Hi there
How do I put a formula in a drop down list
I got 2 column with 2 drop down list I m trying to get cell in column 2 to reset to blank if I select a specific word from the first drop down list in column 1
Not sure if is possible to insert a if formula considering that when I select something in column 2 from drop down list the formula will be cancelled
Thank I hope it makes sense to someone

 

[Becky]

I'm not sure I follow you here... what has to appear in the second column if it isn't blank?

 

[carminedi]

Hi I will upload you a little sample I knew it wouldn't be easy to understand
Thank you

 

[carminedi]

how can I upload an excel file so you can have a look ?

 

[EvanDavis]

how can I upload an excel file so you can have a look ?

 

[carminedi]

it doesn't let me update the excel file

 

[carminedi]

I'm working in a rota project
when I select (off )from a drop down list (which has all shift am pm off double etc) I would like the drop down list in the next column where I normally pick a time from the drop down list to become blank

 

[Becky]

Ah ok, I getcha now.

I think your best bet is to use Data Validation. It's a bit complicated to explain, but there is a good write up here: http://www.contextures.com/xlDataVal02.html

Basically you could use this same approach. You will have a list for what appears in the first box (being am, pm, double, off etc) and then a corresponding list for the second column. For example, the list for 'am' could run from 9am to 12noon at half-hour intervals. The list for 'off' would be blank.

This would mean that if you select 'am' from the first column, the drop down times would be only in the morning. If you select 'off' from the first column, then there would still be a drop down box for the second column but it would be blank. If you tried to enter any data in it then it would throw up an error.

Would that work for you?

Happy to talk you through it if you need me to

 

[carminedi]

Thank you
My issue is that if I put someone on am shift and then change plan and put him off I would like the cell with the previous selected time to become blank automatically
Hope that makes sense
I used dependent data validation list but you still need to change manually

 

[Becky]

Hmmm there might be a way to do it with a macro, but other than that I'm out of ideas I'm afraid. Even if you used a macro, you would still need to run it, therefore you may as well just delete the data manually.

Is the issue that you don't want to have to delete the data (due to the effort involved), or is it that you don't want to accidentally overlook it? If you're worried about overlooking data (for example, if you have entered a time then change the shift to off, and leaving the time has knock on consequences in the sheet) then you could always use conditional formatting to flag it up for you.

For example, you could set it so that if the shift is 'off' but if a time has been entered, it would appear in bold red.

 

[carminedi]

Hi thank you for taking the time in answer my question
Is basically a practical issue I would like to blank automatically so I don't have to delete manually as if stays it would have an effect on a final calculation (although I could put an if formula) and it would be confusing for the staff to look at it

 

[carminedi]

anyone can tell me why this doesn't work grrr :-(
=IF(F3="selection.font.themecolor=xlthemecolordark1.tintandshade=0",F2,G2)
thank you