Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
creating dates "database" in-a-row
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
creating dates "database" in-a-row
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
creating dates "database" in-a-row
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
creating dates "database" in-a-row
Your column is to small
-- Regards Ron De Bruin http://www.rondebruin.nl "yadaaa" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
creating dates "database" in-a-row
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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
creating dates "database" in-a-row
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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
creating dates "database" in-a-row
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 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
creating dates "database" in-a-row
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" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
creating dates "database" in-a-row
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 |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
creating dates "database" in-a-row
Oops I not use Reply All but Reply in OE
Move the button To A1 for example Select A4 and use WindowFreeze 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
working out quarters (three-month periods) between two dates | Excel Worksheet Functions | |||
formula to add dates. | Excel Worksheet Functions | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
Automatic shading of cells based on dates??? | Excel Worksheet Functions | |||
2 digit year in dates return 19xx not 20xx | Excel Discussion (Misc queries) |