Hi again.

let say i have 3 columns:

year month day
2006 06 23

i want to concatenate them to a new cell, same row to one date
23-06-2006.
That i know how to do.

BUT

I want that every time i enter in the same 3 cells, same row,
different date, it will add a new cell near the old concatenate cell
meaning:

first date" 23-06-2006". punching new date inthe same cells,

"23-06-2006" "25-07-2006"
and so on.

help :

--
yadaa
-----------------------------------------------------------------------
yadaaa's Profile: http://www.excelforum.com/member.php...fo&userid=3513
View this thread: http://www.excelforum.com/showthread.php?threadid=54940

Try this event in the Sheet module

> year month day
> 2006 06 23

I use col A,B,C

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Column < 4 Then
Set rng = Range("IV" & Target.Row).End(xlToLeft).Offset(0, 1)
If rng.Column < 4 Then Exit Sub
rng.Value = DateSerial(Cells(Target.Row, 1), Cells(Target.Row, 2), Cells(Target.Row, 3))
End If
End Sub


--
Regards Ron De Bruin
http://www.rondebruin.nl



"yadaaa" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Hi again.
>
> let say i have 3 columns:
>
> year month day
> 2006 06 23
>
> i want to concatenate them to a new cell, same row to one date:
> 23-06-2006.
> That i know how to do.
>
> BUT
>
> I want that every time i enter in the same 3 cells, same row, a
> different date, it will add a new cell near the old concatenate cell.
> meaning:
>
> first date" 23-06-2006". punching new date inthe same cells,
>
> "23-06-2006" "25-07-2006"
> and so on.
>
> help
>
>
> --
> yadaaa
> ------------------------------------------------------------------------
> yadaaa's Profile: http://www.excelforum.com/member.php...o&userid=35130
> View this thread: http://www.excelforum.com/showthread...hreadid=549400
>

Thanx for the reply,

when i type in 3 col the first time, it does add a new cell, but fill
with #######

when i re-type the same cells, for EACH cell it adds a cell, meaning
new cells instead of one, again filled with #########

:confused

--
yadaa
-----------------------------------------------------------------------
yadaaa's Profile: http://www.excelforum.com/member.php...fo&userid=3513
View this thread: http://www.excelforum.com/showthread.php?threadid=54940

Your column is to small

--
Regards Ron De Bruin
http://www.rondebruin.nl



"yadaaa" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Thanx for the reply,
>
> when i type in 3 col the first time, it does add a new cell, but filld
> with #######
>
> when i re-type the same cells, for EACH cell it adds a cell, meaning 3
> new cells instead of one, again filled with #########
>
>
>
>
> --
> yadaaa
> ------------------------------------------------------------------------
> yadaaa's Profile: http://www.excelforum.com/member.php...o&userid=35130
> View this thread: http://www.excelforum.com/showthread...hreadid=549400
>

Im sorry, but im a VBA Dummy.
what do you mean?

i tried entering 22222, but it givers a runtime error.




--
yadaa
-----------------------------------------------------------------------
yadaaa's Profile: http://www.excelforum.com/member.php...fo&userid=3513
View this thread: http://www.excelforum.com/showthread.php?threadid=54940

Make your colums width bigger then you see the date

But now every time you change one of the cells in A,B,C it create a new date
Is this what you want ?

Why not add a button that run a macro when you are satisfied with the input of A,B and C

--
Regards Ron De Bruin
http://www.rondebruin.nl



"yadaaa" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Im sorry, but im a VBA Dummy.
> what do you mean?
>
> i tried entering 22222, but it givers a runtime error.
>
>
> ?
>
>
> --
> yadaaa
> ------------------------------------------------------------------------
> yadaaa's Profile: http://www.excelforum.com/member.php...o&userid=35130
> View this thread: http://www.excelforum.com/showthread...hreadid=549400
>

Thank you very much Ron,

yes, i need to enter the whole 3 cells every time, before it creates
new cell.
would it be to much to ask for the method of creating this button? tha
would concatanate to a new cell each time in the same row.

TI

--
yadaa
-----------------------------------------------------------------------
yadaaa's Profile: http://www.excelforum.com/member.php...fo&userid=3513
View this thread: http://www.excelforum.com/showthread.php?threadid=54940

> would it be to much to ask for the method of creating this button

No problem

Insert a button from the Forms toolbar and assign this macro to it
Note: it insert the date in the row of the activecell and I use a sheet with the name "Sheet1"

I format the date like this "dd-mmm-yyyy" but you can change that

There is no error checking in this example, if you need help with that post back

Sub test()
Dim rng As Range
With Sheets("Sheet1")
Set rng = .Range("IV" & ActiveCell.Row).End(xlToLeft).Offset(0, 1)
rng.Value = Format(DateSerial(.Cells(ActiveCell.Row, 1), .Cells(ActiveCell.Row, 2), _
.Cells(ActiveCell.Row, 3)), "dd-mmm-yyyy")
End With
End Sub


--
Regards Ron De Bruin
http://www.rondebruin.nl



"yadaaa" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Thank you very much Ron,
>
> yes, i need to enter the whole 3 cells every time, before it creates a
> new cell.
> would it be to much to ask for the method of creating this button? that
> would concatanate to a new cell each time in the same row.
>
> TIA
>
>
> --
> yadaaa
> ------------------------------------------------------------------------
> yadaaa's Profile: http://www.excelforum.com/member.php...o&userid=35130
> View this thread: http://www.excelforum.com/showthread...hreadid=549400
>

It works just fine

how can i make the button "float" (not move when scrolling)
can the dates be in text format and not date format? i need to us
autofilter on them, so i need it to be text.
:

--
yadaa
-----------------------------------------------------------------------
yadaaa's Profile: http://www.excelforum.com/member.php...fo&userid=3513
View this thread: http://www.excelforum.com/showthread.php?threadid=54940

Oops I not use Reply All but Reply in OE

Move the button To A1 for example
Select A4 and use Window>Freeze Panes

You can also use Autofilter with real dates???
See also EasyFilter
http://www.rondebruin.nl/easyfilter.htm


--
Regards Ron De Bruin
http://www.rondebruin.nl



"yadaaa" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> It works just fine
>
> how can i make the button "float" (not move when scrolling)
> can the dates be in text format and not date format? i need to use
> autofilter on them, so i need it to be text.
>
>
>
> --
> yadaaa
> ------------------------------------------------------------------------
> yadaaa's Profile: http://www.excelforum.com/member.php...o&userid=35130
> View this thread: http://www.excelforum.com/showthread...hreadid=549400
>