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.

Lewy,

Got it, here is a is what you should enter in your ref cells starting
in "I2" * ="Level 1 "&TEXT(NOW(),"DD/MM/YY") * and then copy down to the
other two cells but changing the Level # appropriately. That displays
the level number and the current date in the format like "09/09/09". If
you wanted to see the date in another format let me know or search Excel
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 cell
has text already it needs the function to properly display the date.
Using "NOW()" in the formula returns the current date so when ever you
choose a value from the drop-down it will have the current date
attached. But once the value is selected it is input as static data and
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

Lewy;482182 Wrote:
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


"Dan DeHaven" wrote:


Lewy,

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

I've got a few ideas for you.

First, if you are using the drop-down list it has to be referencing
a
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"
tab
and select "Data Validation" from the "Data Tools" menu. When you
click
the "Data Validation" icon the Data Validation dialog box will open
and
on the "Settings" tab you should see the "Allow:" drop-down with
"List"
preselected. Below that you should see the "Source" box with the
range
(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,
if
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
"Shift
cells down", this ensures the Data Validation range grows with the
inserted cell. Now in the blank cell enter "=NOW()" which is the
formula
which will update every day with the current date. If the cell isn't
formatted as a date you may need to do this as well. If you want the
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
should
see the current date as the top choice in the list.


Second alternative, In the "Data Validation" dialog box you could
change the drop-down "List" to a "Date" in the "Allow:" drop-down
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
fits
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
about
what the purpose of your form and common values or issues that arise
and
I may have other/better suggestions.

Best of Luck,

Dan


--
Dan DeHaven

------------------------------------------------------------------------
Dan DeHaven's Profile: 'The Code Cage Forums - View Profile: Dan
DeHaven' (http://www.thecodecage.com/forumz/member.php?userid=748)
View this thread: 'Auto update date - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=132257)




--
Dan DeHaven
------------------------------------------------------------------------
Dan DeHaven's Profile: http://www.thecodecage.com/forumz/member.php?userid=748
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=132257