Auto update date

L

Lewy

i have a training spreadsheet with conditional formatting and a drop down
list, would it be possible to put the date in the reference cell for the drop
down and for this date to auto update itself without affecting the actual
cell on the training sheet. Many thanks for any help with this question.
 
D

Dan DeHaven

Lewy;479555 said:
i have a training spreadsheet with conditional formatting and a dro
down
list, would it be possible to put the date in the reference cell fo
the drop
down and for this date to auto update itself without affecting th
actual
cell on the training sheet. Many thanks for any help with thi
question.
Click to expand...

Let me understand, you want the drop down to default to the curren
date but once a date has been selected you don't want that to b
updated, is that correct?

Sorry if I'm misunderstanding....

Da
 
L

Lewy

Yes that is correct Dan

Dan DeHaven said:
Let me understand, you want the drop down to default to the current
date but once a date has been selected you don't want that to be
updated, is that correct?

Sorry if I'm misunderstanding....

Dan
Click to expand...
 
D

Dan DeHaven

Lewy,

I wish I would have also asked before; what does the drop-down no
display?

I've got a few ideas for you.

First, if you are using the drop-down list it has to be referencing
range on in the workbook somewhere. To locate this range in (Excel 2007
select the cell that has the validation and then go-to the "Data" ta
and select "Data Validation" from the "Data Tools" menu. When you clic
the "Data Validation" icon the Data Validation dialog box will open an
on the "Settings" tab you should see the "Allow:" drop-down with "List
preselected. Below that you should see the "Source" box with the rang
(or Named Range). Click on the Icon in the right side of the "Source
box and this should select the range.

Now that you know the range select anywhere in the range. Example, i
the range is A100:A110 select cell A101 (one cell below the top cell
and right click and choose insert from the shortcut menu. Choose "Shif
cells down", this ensures the Data Validation range grows with th
inserted cell. Now in the blank cell enter "=NOW()" which is the formul
which will update every day with the current date. If the cell isn'
formatted as a date you may need to do this as well. If you want th
current date to be the top choice you may need to sort the data range
From here on if someone selects the Data Validation field they shoul
see the current date as the top choice in the list.


Second alternative, In the "Data Validation" dialog box you coul
change the drop-down "List" to a "Date" in the "Allow:" drop-dow
selector. Once you've changed it to "Date" you have to set the "Data:
drop-down to something like "Greater than or equal to" or whichever fit
your needs. Then in the "Start Date:" field input the formula "=NOW()"
(without the quotes).

If these don't seem to fit your needs please give a bit more info abou
what the purpose of your form and common values or issues that arise an
I may have other/better suggestions.

Best of Luck,

Da
 
L

Lewy

Thanks for getting back to me Dan, this is my layout,
A B C D I
Tasks Mark Billy John Drop list ref
Open bag Level 1 Level 2 Level 3 Drop list ref

Drop list ref
The list of tasks go down col A 2 to A 20, Names across the top B1 to B7 and
B2 to G20 has a drop list in each cell, the reference cells for my drop list
is in I 2, I 3, I 4,
in my ref cells are Level 1, Level 2, Level 3, I would also like the date to
go in the ref cells, so that when i update the persons Level the date goes in
aswell as the Level, i need the ref cell date to auto update itself and when
inserted to a person to stay at the same date as when it was entered. I hope
this is a more clear explanation Dan thanks for your help thus far and i hope
you can help some more, much appreciated. Lewy
 
D

Dan DeHaven

Lewy,

Got it, here is a is what you should enter in your ref cells startin
in "I2" * ="Level 1 "&TEXT(NOW(),"DD/MM/YY") * and then copy down to th
other two cells but changing the Level # appropriately. That display
the level number and the current date in the format like "09/09/09". I
you wanted to see the date in another format let me know or search Exce
help on the "TEXT" function, it can do some really cool stuff.

Also the reason you need to use the "TEXT" function is because the cel
has text already it needs the function to properly display the date
Using "NOW()" in the formula returns the current date so when ever yo
choose a value from the drop-down it will have the current dat
attached. But once the value is selected it is input as static data an
won't change based on the change of the actual date each day.

That should take care of it, if not let me know.

Glad to be of service,

Dan
 
L

Lewy

This works as far as putting the date in the ref cell with the text already
in there, i had to put some dashes in after my text to seperate the text and
date as my text had a number at the end and it looked like Level 112/09/09 it
now looks like Level 1---12/09/09
I will let you know if the date in either the ref cell or in the destination
cell causes any problems but once again thank you Dan, this makes life quite
a bit simpler for me, much appreciated mate.
 
L

Lewy

As far as the date entry goes it works spot on Dan but when the date in the
reference cell changes i loose the conditional formatting colour in my main
cells, any thoughts, thanks Lewy.
 
D

Dan DeHaven

Lewy,

I'm not sure what your conditional formatting looks like, can you repl
with the formula or which formatting pre-set that your using.

I'm sure with a tailor made conditional format formula we can fix th
issue.

Also, I may be quicker if you can attach a sample of your file then
can probably resolve this much quicker.

Conditional formatting it a bit tricky and not the easiest thing t
explain.

Da
 
L

Lewy

Dan these are the formatting rules in use, if it makes a difference i am
using excel 2007 at home but 2003 at work, the one at work is the main one i
want to work.
Format only cells with specific text containg Level 1
Format only cells with specific text containg Level 2
Format only cells with specific text containg Level 3
The format above colours each cell according to its level, and the one below
colours the persons name when all their tasks are at level 1.
=AND(B2="Level 1",B3="Level 1",B4="Level 1",B5="Level 1",B6="Level
1",B7="Level 1")
Thanks again Dan
 
Top