 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / August 2006Automatically 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? 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 >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, 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 > Tara > [quoted text clipped - 11 lines] > >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? 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 >Gord, >One of the managers brought this to my attention. He is currently inserting [quoted text clipped - 19 lines] >> >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? 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 > What would insert automatically? > [quoted text clipped - 45 lines] > >> >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? 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 >Hi Gord, > [quoted text clipped - 63 lines] >> >> >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 Thank you so much! I will pass on the information and let you know what happens. > OK, so it's not a column he wants inserted, it is a cell inserted in a row. > [quoted text clipped - 85 lines] > > Gord Dibben MS Excel MVP Should read.... This will insert a column and enter today's date in the active cell. Gord >What would insert automatically? > [quoted text clipped - 45 lines] >>> >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 to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.