Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on a calender type spreadsheet
Hope I explain this correctly. I need to make (two sided) tabbed spreadsheets
available for individuals to use for each day of the year. I'm okay building the speadsheets themselves but I want to date each one, front and back for the current date without having to type that in daily. Isn't there a way I could do one or two each month, then drag the tabs out to, say 31, for January, repeat for Februuary, etc? Or, is there a way to have one spreadsheet and each time it's opened it has the current (computer) date on it? I'm somewhat formula deficient so please be real specific Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on a calender type spreadsheet
If you have this formula in a cell:
=TODAY() then everytime you open the file it will show the current date in that cell automatically. So, you could derive your dates from this. However, it would mean that the dates would change every (new) day that you opened the file. It might be better to put the start date in a cell (eg put 01/01/2009 in A1), then in A2 you can have this formula: =A1+1 which should show 2nd January, and you can copy the simple formula down to row 31 to get all the dates in January. Then for a new month, all you need is to change the starting date in A1 and the other cells will change automatically. Hope this helps. Pete On Jan 8, 2:24*pm, kayak99 wrote: Hope I explain this correctly. I need to make (two sided) tabbed spreadsheets available for individuals to use for each day of the year. I'm okay building the speadsheets themselves but I want to date each one, front and back for the current date without having to type that in daily. Isn't there a way I could do one or two each month, then drag the tabs out to, say 31, for January, repeat for Februuary, etc? Or, is there a way to have one spreadsheet and each time it's opened it has the current (computer) date on it? I'm somewhat formula deficient so please be real specific Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on a calender type spreadsheet
Regarding your second suggestion, how would I copy the formula and the spreadsheet itself from say 1/01/2009 to make 30 more tabs up to 1/31/2009? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on a calender type spreadsheet
I didn't realise you wanted a sheet for each day of the month. You can
copy sheets quite easily by holding the CTRL key down and then just dragging the sheet tab along the bottom (a small arrow will indicate where the tab will be positioned). You can do this 30 times, and then manually change the name of each tab. If you name them 1st, 2nd, 3rd etc, then once you have done it for January, you can just use File | Save As to save the files with different names for each month, and then for 30-day months (and for February) you can delete the sheet(s) that you don't need. Hope this helps. Pete On Jan 8, 3:11*pm, kayak99 wrote: Regarding your second suggestion, how would I copy the formula and the spreadsheet itself from say 1/01/2009 to make 30 more tabs up to 1/31/2009? Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on a calender type spreadsheet
Pete, That does give a months worth of spreadsheets but it appears I have to go back and manually rename the tabs as well as change the dates on each sheet. It was those two manual changes that I was trying to avoid beacuse I want to chart an entire year. That's a lot of renaming - two each day (tab date and cell date). Just to review if I didn't state my needs correctly, same spreadsheet, different dates, one for each day of the year. Maybe there is no way to accomplish this other than your manual suggestion. Thanks for your help. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on a calender type spreadsheet
That's why I suggested having a file for each month (rather than one
file for the whole year - do you know what 365 sheets in one file will look like?) You can group sheets together and then any changes you make to one can be reflected in all the grouped sheets. For example, in the first (monthly) file you create you could group sheets 2nd to 31st together and in cell A1 you can enter this formula: =IF(1st!A1="","",1st!A1) Then ungroup the sheets by clicking on the tab for 1st, and you can enter January in cell A1. Now all those other sheets will show January in cell A1. When you come to save this file as February, all you need to do is change the entry in A1 of the 1st sheet to February, then all those other sheets will show that month. Hope this helps. Pete On Jan 8, 4:07*pm, kayak99 wrote: Pete, * That does give a months worth of spreadsheets but it appears I have to go back and manually rename the tabs as well as change the dates on each sheet. It was those two manual changes that I was trying to avoid beacuse I want to chart an entire year. *That's a lot of renaming - two each day (tab date and cell date). * Just to review if I didn't state my needs correctly, same spreadsheet, different dates, one for each day of the year. Maybe there is no way to accomplish this other than your manual suggestion. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on a calender type spreadsheet
Kayak,
You could use a macro to copy and rename the sheets: this code is based on the current month, but you could change it to do any month desired. Sub TryNow() Dim i As Integer Dim iDays As Integer iDays = Day(DateSerial(Year(Date), Month(Date) + 1, 0)) - 1 For i = 1 To iDays - 1 Worksheets(1).Copy Worksheets(1) Next i For i = 1 To iDays Worksheets(i).Name = _ Format(DateSerial(Year(Date), Month(Date), i), "mmm dd") Next i End Sub HTH, Bernie MS Excel MVP "kayak99" wrote in message ... Pete, That does give a months worth of spreadsheets but it appears I have to go back and manually rename the tabs as well as change the dates on each sheet. It was those two manual changes that I was trying to avoid beacuse I want to chart an entire year. That's a lot of renaming - two each day (tab date and cell date). Just to review if I didn't state my needs correctly, same spreadsheet, different dates, one for each day of the year. Maybe there is no way to accomplish this other than your manual suggestion. Thanks for your help. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on a calender type spreadsheet
Are you saying that you want 365 worksheets in a workbook?
One worksheet for each day of the year? Or one workbook with worksheets for each day of one month? And please explain what you mean by "two sided tabbed spreadsheets" Gord Dibben MS Excel MVP On Thu, 8 Jan 2009 07:11:38 -0800, kayak99 wrote: Regarding your second suggestion, how would I copy the formula and the spreadsheet itself from say 1/01/2009 to make 30 more tabs up to 1/31/2009? Thanks |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on a calender type spreadsheet
I need a years worth of dated spreadsheets and it will printed out on two
sides, front and back with the date on each side. Its actually a time sheet of sorts and typically only used Mon -Fri. The twelve month file mentioned above seems best. We have an older set of these but someone had taken the time to actually type the date into each of 365 pages front and back. I'm wanting to use the same sheets but without having to type a date into each page. Haven't worked with macros and my experience with Excel was limited to more simple spreadsheets. Still muddling through but haven't landed on the perfect solution yet. Thanks for the help though! "Gord Dibben" wrote: Are you saying that you want 365 worksheets in a workbook? One worksheet for each day of the year? Or one workbook with worksheets for each day of one month? And please explain what you mean by "two sided tabbed spreadsheets" Gord Dibben MS Excel MVP On Thu, 8 Jan 2009 07:11:38 -0800, kayak99 wrote: Regarding your second suggestion, how would I copy the formula and the spreadsheet itself from say 1/01/2009 to make 30 more tabs up to 1/31/2009? Thanks |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on a calender type spreadsheet
Bernie's code to copy Sheet1 and rename to mm dd would give you a workbook
with just one month's sheets. I think that would be best...........12 workbooks......one for each month, would be much less unwieldy than 365 sheets in one workbook. Or you could have 12 sheets, one for each month, in a single workbook. We could work up code for that if you should choose. As far as printing on two sides, that would be another set of code to get a date on each side and would depend upon your printer supporting double-sided printing. Gord On Thu, 8 Jan 2009 12:49:01 -0800, kayak99 wrote: I need a years worth of dated spreadsheets and it will printed out on two sides, front and back with the date on each side. Its actually a time sheet of sorts and typically only used Mon -Fri. The twelve month file mentioned above seems best. We have an older set of these but someone had taken the time to actually type the date into each of 365 pages front and back. I'm wanting to use the same sheets but without having to type a date into each page. Haven't worked with macros and my experience with Excel was limited to more simple spreadsheets. Still muddling through but haven't landed on the perfect solution yet. Thanks for the help though! "Gord Dibben" wrote: Are you saying that you want 365 worksheets in a workbook? One worksheet for each day of the year? Or one workbook with worksheets for each day of one month? And please explain what you mean by "two sided tabbed spreadsheets" Gord Dibben MS Excel MVP On Thu, 8 Jan 2009 07:11:38 -0800, kayak99 wrote: Regarding your second suggestion, how would I copy the formula and the spreadsheet itself from say 1/01/2009 to make 30 more tabs up to 1/31/2009? Thanks |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on a calender type spreadsheet
Gord, sorry I was tied up on another project and had to stop work with this one. Yes, 12 workbooks, one for each month would work best. What code would you use? I'm not very good with macros so please be specific. Thanks "Gord Dibben" wrote: Bernie's code to copy Sheet1 and rename to mm dd would give you a workbook with just one month's sheets. I think that would be best...........12 workbooks......one for each month, would be much less unwieldy than 365 sheets in one workbook. Or you could have 12 sheets, one for each month, in a single workbook. We could work up code for that if you should choose. As far as printing on two sides, that would be another set of code to get a date on each side and would depend upon your printer supporting double-sided printing. Gord |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on a calender type spreadsheet
I'm still not sure what you want for an end result. Easy enough to add a month's worth of date-named sheets to a new workbook. Like 01 Jan 2009 through 31 Jan 2009 You can even add sheets just for weekdays...omitting weekends for that month It is the "double-sided with dates front and back" that confuses me. Let's say you have a workbook with 31 sheets for January, 2009. How would you want those 31 sheets printed out? Would each sheet be one printed page or more? Which pages would be printed "front and back"? Would the Date in a header or footer suffice? Gord On Sat, 14 Feb 2009 16:04:00 -0800, kayak99 wrote: Gord, sorry I was tied up on another project and had to stop work with this one. Yes, 12 workbooks, one for each month would work best. What code would you use? I'm not very good with macros so please be specific. Thanks "Gord Dibben" wrote: Bernie's code to copy Sheet1 and rename to mm dd would give you a workbook with just one month's sheets. I think that would be best...........12 workbooks......one for each month, would be much less unwieldy than 365 sheets in one workbook. Or you could have 12 sheets, one for each month, in a single workbook. We could work up code for that if you should choose. As far as printing on two sides, that would be another set of code to get a date on each side and would depend upon your printer supporting double-sided printing. Gord |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I link data from an Excel spreadsheet to a calender? | Excel Discussion (Misc queries) | |||
calender for spreadsheet | Excel Worksheet Functions | |||
Outlook Calender to Excel Spreadsheet | Excel Worksheet Functions | |||
HELP-Can not type on Spreadsheet | Excel Worksheet Functions | |||
Can I use a spreadsheet to populate an offline calender in excel? | Excel Discussion (Misc queries) |