Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically inserting a column
I have a spreadsheet that has several columns with dates - Today's date,
yesterday's date, etc. If a new date is typed in, how can I set the spreadsheet to automatically insert a column for the new date? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically inserting a column
Tara
To do what you want would require VBA code of some sort. Where/how would the new date be typed in? Where would the new column be inserted? Why not just insert a new column and type the date in that column? Gord Dibben MS Excel MVP On Thu, 17 Aug 2006 08:41:02 -0700, Tara wrote: I have a spreadsheet that has several columns with dates - Today's date, yesterday's date, etc. If a new date is typed in, how can I set the spreadsheet to automatically insert a column for the new date? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically inserting a column
Gord,
One of the managers brought this to my attention. He is currently inserting the column & date manually but I believe there is a lot of data input each day and he would like to be able to just type the date in any cell and it would insert automatically. It is my understanding it would go just to the right of the main date cell. I hope this helps. Tara "Gord Dibben" wrote: Tara To do what you want would require VBA code of some sort. Where/how would the new date be typed in? Where would the new column be inserted? Why not just insert a new column and type the date in that column? Gord Dibben MS Excel MVP On Thu, 17 Aug 2006 08:41:02 -0700, Tara wrote: I have a spreadsheet that has several columns with dates - Today's date, yesterday's date, etc. If a new date is typed in, how can I set the spreadsheet to automatically insert a column for the new date? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically inserting a column
What would insert automatically?
The column? Type the date in any cell and that date would stay there? Or go into the new column? Into which cell in the new column? What is the "main date cell"? I'm struggling here as you can see. Can we give him a macro which he can trigger with a button or shortcut key to insert a new column and enter the date? Sub Insert_Enter() Selection.EntireColumn.Insert ActiveCell.Value = Format(Now, "dd mmm yyyy") End Sub This will insert a column and enter in the active cell. Gord Dibben MS Excel MVP On Thu, 17 Aug 2006 12:54:01 -0700, Tara wrote: Gord, One of the managers brought this to my attention. He is currently inserting the column & date manually but I believe there is a lot of data input each day and he would like to be able to just type the date in any cell and it would insert automatically. It is my understanding it would go just to the right of the main date cell. I hope this helps. Tara "Gord Dibben" wrote: Tara To do what you want would require VBA code of some sort. Where/how would the new date be typed in? Where would the new column be inserted? Why not just insert a new column and type the date in that column? Gord Dibben MS Excel MVP On Thu, 17 Aug 2006 08:41:02 -0700, Tara wrote: I have a spreadsheet that has several columns with dates - Today's date, yesterday's date, etc. If a new date is typed in, how can I set the spreadsheet to automatically insert a column for the new date? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically inserting a column
Hi Gord,
I am going to need to verify with him, and have him write you back. As far as I can tell, he has a spreadsheet that has 40 rows (1 for each employee) and each row has several (20+) columns with dates of training classes completed (ex. 8/17/06, 8/17/05,8/17/04,8/17/03 etc.) As an employee takes a class they currently insert a "cell" into the spreadsheet manually and type in the new date. He wants the spreadsheet to insert a new cell and new date and only effect 1 row at a time. Sometimes they have several at a time to update. (will a macro have to be set up for each row?) He also has a conditional formatting in the "Current Date" column that turns red when the training is outdated. He wants to have the existing formatting in that column to move to the new column automatically. I know this is confusing, I am sorry. Maybe I am not explaining it right. Thank you for all your help. Tara "Gord Dibben" wrote: What would insert automatically? The column? Type the date in any cell and that date would stay there? Or go into the new column? Into which cell in the new column? What is the "main date cell"? I'm struggling here as you can see. Can we give him a macro which he can trigger with a button or shortcut key to insert a new column and enter the date? Sub Insert_Enter() Selection.EntireColumn.Insert ActiveCell.Value = Format(Now, "dd mmm yyyy") End Sub This will insert a column and enter in the active cell. Gord Dibben MS Excel MVP On Thu, 17 Aug 2006 12:54:01 -0700, Tara wrote: Gord, One of the managers brought this to my attention. He is currently inserting the column & date manually but I believe there is a lot of data input each day and he would like to be able to just type the date in any cell and it would insert automatically. It is my understanding it would go just to the right of the main date cell. I hope this helps. Tara "Gord Dibben" wrote: Tara To do what you want would require VBA code of some sort. Where/how would the new date be typed in? Where would the new column be inserted? Why not just insert a new column and type the date in that column? Gord Dibben MS Excel MVP On Thu, 17 Aug 2006 08:41:02 -0700, Tara wrote: I have a spreadsheet that has several columns with dates - Today's date, yesterday's date, etc. If a new date is typed in, how can I set the spreadsheet to automatically insert a column for the new date? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically inserting a column
Should read....
This will insert a column and enter today's date in the active cell. Gord On Thu, 17 Aug 2006 16:38:17 -0700, Gord Dibben <gorddibbATshawDOTca wrote: What would insert automatically? The column? Type the date in any cell and that date would stay there? Or go into the new column? Into which cell in the new column? What is the "main date cell"? I'm struggling here as you can see. Can we give him a macro which he can trigger with a button or shortcut key to insert a new column and enter the date? Sub Insert_Enter() Selection.EntireColumn.Insert ActiveCell.Value = Format(Now, "dd mmm yyyy") End Sub This will insert a column and enter in the active cell. Gord Dibben MS Excel MVP On Thu, 17 Aug 2006 12:54:01 -0700, Tara wrote: Gord, One of the managers brought this to my attention. He is currently inserting the column & date manually but I believe there is a lot of data input each day and he would like to be able to just type the date in any cell and it would insert automatically. It is my understanding it would go just to the right of the main date cell. I hope this helps. Tara "Gord Dibben" wrote: Tara To do what you want would require VBA code of some sort. Where/how would the new date be typed in? Where would the new column be inserted? Why not just insert a new column and type the date in that column? Gord Dibben MS Excel MVP On Thu, 17 Aug 2006 08:41:02 -0700, Tara wrote: I have a spreadsheet that has several columns with dates - Today's date, yesterday's date, etc. If a new date is typed in, how can I set the spreadsheet to automatically insert a column for the new date? Gord Dibben MS Excel MVP |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically inserting a column
OK, so it's not a column he wants inserted, it is a cell inserted in a row.
Revised macro......... Sub Insert_Enter() Selection.Insert Shift:=xlToRight ActiveCell.Value = Format(Now, "dd mmm yyyy") Selection.Columns.AutoFit End Sub Select any cell in any row and run the macro. Cells will be shifted to the right and date will be entered in the activecell. No, you don't need a macro for each row. Selection and ActiveCell cover all rows and any cell. Gord On Thu, 17 Aug 2006 17:00:01 -0700, Tara wrote: Hi Gord, I am going to need to verify with him, and have him write you back. As far as I can tell, he has a spreadsheet that has 40 rows (1 for each employee) and each row has several (20+) columns with dates of training classes completed (ex. 8/17/06, 8/17/05,8/17/04,8/17/03 etc.) As an employee takes a class they currently insert a "cell" into the spreadsheet manually and type in the new date. He wants the spreadsheet to insert a new cell and new date and only effect 1 row at a time. Sometimes they have several at a time to update. (will a macro have to be set up for each row?) He also has a conditional formatting in the "Current Date" column that turns red when the training is outdated. He wants to have the existing formatting in that column to move to the new column automatically. I know this is confusing, I am sorry. Maybe I am not explaining it right. Thank you for all your help. Tara "Gord Dibben" wrote: What would insert automatically? The column? Type the date in any cell and that date would stay there? Or go into the new column? Into which cell in the new column? What is the "main date cell"? I'm struggling here as you can see. Can we give him a macro which he can trigger with a button or shortcut key to insert a new column and enter the date? Sub Insert_Enter() Selection.EntireColumn.Insert ActiveCell.Value = Format(Now, "dd mmm yyyy") End Sub This will insert a column and enter in the active cell. Gord Dibben MS Excel MVP On Thu, 17 Aug 2006 12:54:01 -0700, Tara wrote: Gord, One of the managers brought this to my attention. He is currently inserting the column & date manually but I believe there is a lot of data input each day and he would like to be able to just type the date in any cell and it would insert automatically. It is my understanding it would go just to the right of the main date cell. I hope this helps. Tara "Gord Dibben" wrote: Tara To do what you want would require VBA code of some sort. Where/how would the new date be typed in? Where would the new column be inserted? Why not just insert a new column and type the date in that column? Gord Dibben MS Excel MVP On Thu, 17 Aug 2006 08:41:02 -0700, Tara wrote: I have a spreadsheet that has several columns with dates - Today's date, yesterday's date, etc. If a new date is typed in, how can I set the spreadsheet to automatically insert a column for the new date? Gord Dibben MS Excel MVP |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Automatically inserting a column
Thank you so much! I will pass on the information and let you know what
happens. "Gord Dibben" wrote: OK, so it's not a column he wants inserted, it is a cell inserted in a row. Revised macro......... Sub Insert_Enter() Selection.Insert Shift:=xlToRight ActiveCell.Value = Format(Now, "dd mmm yyyy") Selection.Columns.AutoFit End Sub Select any cell in any row and run the macro. Cells will be shifted to the right and date will be entered in the activecell. No, you don't need a macro for each row. Selection and ActiveCell cover all rows and any cell. Gord On Thu, 17 Aug 2006 17:00:01 -0700, Tara wrote: Hi Gord, I am going to need to verify with him, and have him write you back. As far as I can tell, he has a spreadsheet that has 40 rows (1 for each employee) and each row has several (20+) columns with dates of training classes completed (ex. 8/17/06, 8/17/05,8/17/04,8/17/03 etc.) As an employee takes a class they currently insert a "cell" into the spreadsheet manually and type in the new date. He wants the spreadsheet to insert a new cell and new date and only effect 1 row at a time. Sometimes they have several at a time to update. (will a macro have to be set up for each row?) He also has a conditional formatting in the "Current Date" column that turns red when the training is outdated. He wants to have the existing formatting in that column to move to the new column automatically. I know this is confusing, I am sorry. Maybe I am not explaining it right. Thank you for all your help. Tara "Gord Dibben" wrote: What would insert automatically? The column? Type the date in any cell and that date would stay there? Or go into the new column? Into which cell in the new column? What is the "main date cell"? I'm struggling here as you can see. Can we give him a macro which he can trigger with a button or shortcut key to insert a new column and enter the date? Sub Insert_Enter() Selection.EntireColumn.Insert ActiveCell.Value = Format(Now, "dd mmm yyyy") End Sub This will insert a column and enter in the active cell. Gord Dibben MS Excel MVP On Thu, 17 Aug 2006 12:54:01 -0700, Tara wrote: Gord, One of the managers brought this to my attention. He is currently inserting the column & date manually but I believe there is a lot of data input each day and he would like to be able to just type the date in any cell and it would insert automatically. It is my understanding it would go just to the right of the main date cell. I hope this helps. Tara "Gord Dibben" wrote: Tara To do what you want would require VBA code of some sort. Where/how would the new date be typed in? Where would the new column be inserted? Why not just insert a new column and type the date in that column? Gord Dibben MS Excel MVP On Thu, 17 Aug 2006 08:41:02 -0700, Tara wrote: I have a spreadsheet that has several columns with dates - Today's date, yesterday's date, etc. If a new date is typed in, how can I set the spreadsheet to automatically insert a column for the new date? Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format as a MACRO | Excel Worksheet Functions | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
how to highlight current row & column automatically by a color | Excel Discussion (Misc queries) | |||
getting data from 2 excel sheets automatically | Excel Worksheet Functions |