Drop-down list, conditioned static date and time

[Inkarn]

Good morning (or afternoon, ooor evening),

First of all, sorry for my bad English. I am currently expecting 2 problems I need to solve in Excel 2010. The thing is, I am no way skilled in using VBA, that's why I have to rely on my basic Excel skills. To make it short:

1. I need to set, that the content of a drop-down list depends on what is filled in a cell next to it. Here's the example: For a text "Cooking" (cell A1) is set a drop-down list containing "Spaghetti", "Fish'N'Chips", "Rizzoto" (A2), and for a text "House Cleaning" (A1) drop-down list "Vacuuming", "Dirt Cleaning", "Dish Washing" (A2). Shortly, I need to make variable drop-down list in one Excel sheet (I'd like to avoid using more than one). Any way to do it?

2. Is there a way to automatically fill a cell with a static (!) date and a static (!) time (separately in 2 cells) in case one cell contains a text? Example: There are 3 empty cells - B1, B2, B3. I case I fill in cell B3, there will be shown static date in B1 and static time in B2. In case I remove the text in B3, both date and time will be deleted. I tried TODAY and NOW functions, but both are not static.

I hope that you'll understand what I wanted to say, and thank you in advance for a help solving these problems. If something is not understandable, let me know, and I'll try to describe it more accurately.

[Kaper]

First item can be implemented without VBA - if you can manage reading in (a bit similar to Czech, slavic language) Polish - check one of FAQ subjects in excelforum.pl - this one: http://www.excelforum.pl/topics7/29-...sty-vt1493.htm
Basically (in the simplest version - see attachement) you shall have a named range, which changes depending on your first choice. then the drop-down list (easiest to do as validation) shall rely on this named range.

The second one requires already some VBA programming.
Try the following code in the sheet module (right click on the sheet tab and "show code"):

Please Login or Register  to view this content.

Remember to format B1 and B2 properly (as date and time)

[Fotis1991]

Welcome to the forum

Both your request can be done.

For the second one take a look here

For the first one you need an IF in your Data Validation...Something like this..

Please Login or Register  to view this content.

If you need more help, then upload a small sample workbook.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Inkarn]

Thank you very much, though none of the options worked for me. But the problem is clearly on my side.

1. Well, I tried to put the IF function into the table source, BUT... it is way too long, so it doesn't accept it :/

Please Login or Register  to view this content.

In real, this is even longer, because IF means KDYŽ in my language and A, B, C, D, E, ... are names of my determining values.

2. I guess I forgot the most important part, and that's that I want to set this for a range of cells. That means "if cell B3 is filled, cell B1 now contains fixed date, cell B2 now contains fixed time, if cell C3 is filled, cell C1 now contains fixed date, C2 contains fixed time etc. etc." This babel surely doesn't work:
Bez názvu.jpg

Any ideas how to make this mess work? :D I know I'm dumb in this way...

[Fotis1991]

I suspect that you don't handle it well... As i told upload a small sample workbook. Not a picture!

[Kaper]

1) I believe you are doing it wrong way, but as Fotis1991 said - upload attachment
BTW in Polish function name is even longer: JEŻELI (pl) == IF (en) == KDYŽ (cz)
2) similar to one from origimnal post, but you changed the requirements since first question:

Please Login or Register  to view this content.

[Inkarn]

Awesome! Date works now in every desired cell It's like a magic!
I'm sorry I didn't upload an attachment, but here it is. All I need to do is that flexible drop down list, in that yellow area. "Station" cells are the ones that decide the drop down list in "Issue". I tried to set it variable in Data Validation by using IF method (enclosed as well, in czech language, as well as english language), but that method is too long, so I can't insert it.
@Kaper: In your sheet, there was set "list_for_A" only... Honestly, I didn't get it. What else did you do?

By the way, this is not the final excel sheet, I just need to learn the way to do it. F.e. "Time" column will be in another sheet, but I think I'll manage it with that VBA code posted before.

Thanks again, you're awesome!

Ex1.xlsm, Ex1.xls