Calendar help

Hi all,
Just joined for help on a calendar.
2003 excel
I have a order sheet made and want to popilate and print a month view calendar from it.

Ideas?
Templates?

Thanks

Just found a template here but it wont let me download it...http://www.excelforum.com/excel-gene...buttons-2.html

Try this.............

Here's a link to one (or two) of mine:

http://www.excelforum.com/excel-gene...-on-excel.html

Hope this helps.

Pete

thanks Pete.

Basically I have a sheet made with the delivery date in col A. Order number in B, Quantity of item 1 in C, item 2 in D item 3 in E etc.
Ideally I want to auto populate a calendar with the data (order number, item and quantity in the coresponding day.
Is this possible?

It depends how many items you might have for any particular order number. Post a sample workbook showing the kind of thing you have, and I'll take a look at it tomorrow.

Pete

i would if I could work out how to add an attachment!

In your sample you indicate that you would like 6 fields of data to be shown on the calendar (well, actually 5, as the date is part of the calendar). The main drawback with this is that they might look a bit small on the completed calendar, as you have 7 days across, so that equates to about 1 1/8 inch of space per day if shown in portrait mode or about 1 3/4 inch in landscape mode. Before I go ahead and set up the 5 fields for you, are you sure this is what you want? We could gain a bit more space if you don't have activities over the weekends, so Saturday and Sunday could be narrowed.

Do you want to show up to 10 Order numbers per day (in batches of 10), as per the later calendar on that thread?

Pete

Hi James,

here is a link to another calendar of mine (post #10):

http://www.excelforum.com/excel-prog...html?p=2768117

This one shows 4 fields per day, although 2 of them are for the user to tick. This might give you some further ideas about displaying the data.

Hope this helps.

Pete

Thanks Pete,

Ok, you are right the date (col A) is already there.
Sat and Sun can be narrowed.
I could abreviate the name so that could be narrowed too.
Item number will be max 3 letters long so that also could be narrowed.
I actually have a total of 7 items. columns D-K
A max of 5 orders per day would be plenty.

It is a calendar to print and put up in the workshop so I know how many of which item to have ready for when, and which items to pack together. I then cross off each item as it is made.

Hi Pete_UK,

You've made hundreds of versions of calendars at the request of the people. Excellent job. Congratulations.

Hi James,

I've been out much of today, so I haven't made much progress so far.

It would help if you could attach a few more examples of your data, say 10 or 20 records, as it's a bit difficult to work from just one record. It could be made-up data, but the layout should follow how you have it at the moment, so it will be easier for you to incorporate within the file that I produce.

@Indi_Ra

thanks for the kind comments.

Pete

Like this?

That's fine, although column H does not seem to have any data. Do you want the data for Items 1 to 4 (cols D, E, F, G) and 5 to 7 (cols I, J, K) t be carried across to the calendar?

I was thinking about having two rows for each item, one for the Name and Wood, and the other for the Items.

Pete

Hi Pete, wood could actually be left out as there is only 1 type I use now.
Data is as you say.
will I be able to rename or number the Items?

I'm not sure what you mean by rename or renumber. The file will be fully formula-driven, so it will respond automatically to the data (and to changes in it). So, if you change a date then that entry will appear at the appropriate place on the calendar. If you change a number in Item 2 for a particular order then that will be reflected on the calendar. You make changes on the Activities sheet (or add new orders etc.) and the Calendar sheet displays the data on the appropriate day. You can change the month/year using two drop-downs.

And, it will be fully XL2003 compliant ...

I've got the basic structure now with 7 fields for each day (not weekends), and I have arranged things so that "orphan" days appear on the top row of the calendar. In a 30-day month which starts on a Saturday, or a 31-day month which starts on either a Friday or Saturday, the final day(s) fall into the sixth week, thus requiring an extra block at the bottom of the calendar. To avoid this, these orphan days will appear at the top, so I'll only need to span 5 weeks.

Hope this is what you want.

Pete

I'm not fully up on all the lingo but it sounds great!
I meant if I changed "item 1" to "table" or "chair" would it show on the calendar?

It's only going to show the number under Item 1 for a particular day - the label "Item 1" is what you want it to be. If you want those headings to be more meaningful, let me know (or you can change them later).

Pete

No thats great.

Hi James,

I've attached the completed calendar file.

I've made a few slight changes to your Orders sheet, by inserting a new column A and putting a formula in cell A8 and copying this down to row 200. I've also put your totals calculations at the top of your data, so the data can grow downwards as you add more orders. If you are still using this file next year, then you can generate new totals for another year by just copying the cells B2:M2 down and changing the year in cell B3. If your data goes beyond row 200, you will need to copy the formula in column A further down (it doesn't really matter how far).

As advised earlier, I've used a block of 2 rows by 7 columns on the Calendar sheet for each of your orders - the top row has the customer name or reference along with the wood, and the second row shows the number for each of the seven items (these are shown at the bottom of the calendar, and if you want to change the labels you should do so in cells E7 to L7 of the Orders sheet). I've allowed up to 5 records per day, which is more than enough for the sample data. I've assumed there are no orders for Saturday and Sunday, so these columns do not contain any formulae and are much narrower than for the days of the week.

You can select the month and year using the drop-downs in cells AO4 and AO5 on the Calendar sheet, and the display will automatically adjust. I have used conditional formatting to hide the borders for days at the beginning and end of a month, and as mentioned above I have shown "orphan" days in the top left corner as required (see January, July, or October 2016 to see this effect).

You can copy your existing data and paste it into the Orders sheet at cell B8 onwards. Thereafter, you can just keep adding data to the Orders sheet (or amending it) as required. The Calendar sheet will automatically adjust to shown any changes to the data - all you need to do in this sheet is to choose the appropriate month and year of interest and then you can print it out.

Hope this helps.

Pete

Wow! Impressive, thanks so much.
Could I copy the row 58 the item row, and paste it at the bottom of each week row? eg rows 24, 35, 46? I would lose no. 5 record row but this is unlikely to be used.

It would probably be better to use copy then Paste Special | Formulas | OK so that you do not mess up the formatting and conditional formatting on the record 5 rows.

In your first post you said you came here to look for help on a calendar, so I hope you have got what you wanted - I'm happy to be able to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

I have my price data in row 49. In the price col I have this formula; =SUM(E8*E49)+(F8*F49)+(G8*G49)+(H8*H49)+(J8*J49)+(K8*K49)+(L8*L49)+M8
when I try to copy the formula all the way down the price col it wont work properly. It changes E49 to E50, E51 etc etc.
On my old sheet it works fine. What am I doing wrong?

Great thanks,
How do I copy a list from another sheet? In my new one it has the dropdown arrow but no options in it.

That works thanks.
I have it set so that yes has green fill and no yellow fill. It works on the old sheet but I can't see how to do it on the new one.

Worked out the list prob.
Next one is the boss (wife!) asked me to add a date col to the left of the current one. One will be shipping date and the other delivery date.
Is there an easy way to subtract 3 workdays from a date?

Hi James,

you can use the WORKDAY function like this:

=WORKDAY(B11,-3)

so if B11 contains 16/03/2016 (a Wednesday) the formula will return 11/03/2016 (the previous Friday). You can also include a third parameter which is a range that contains holiday dates, and the function will take account of any holidays that occur during the week. If your list of holiday dates is in X8:X24, for example, then the formula would become:

=WORKDAY(B11,-3, $X$8:$X$24)

Incidentally, referring back to your Post #23, I would advise against having your price information further down the sheet, because when you start adding new data there will come a time when you will butt up against the price information. It would be better to have it in that top block (say on row 6), so you can just CUT from row 49 and Paste it into row 6, and the formulae will automatically adjust. If there comes a time when you want to change the price information, then you can just insert a new row 7 and put the new prices there, and then the formulae for total cost from that point in time onwards can just be changed to refer to row 7 rather than row 6, so that the earlier price information remains the same.

Hope this helps.

Pete

Thanks Pete,
For some reason it doesn't like that formula. When I enter it, it shows #NAME?
It also then shows the same across F to N on row 2, all the item totals.

I'm not sure if the WORKDAY function is available in Excel 2003 - you might have to install the Analysis Tool Pack add-in to make it available (if it was included within there at that time). As that is producing errors at the moment, so the totals on row 2 will also produce errors, as it is looking at the dates in column B.

I assume you have inserted a new column to get the dates, so you could use column B for the earliest date (which you enter) and derive the later date by adding 3 days onto it, rather than the other way round.

I'm going away shortly for a couple of days, so I can't offer any more help at the moment.

Pete

Thanks again.
I went to tools-add ins and selected Analysis and Analysis VBA.
Still no different..

Got it done!! Thanks for all your =(help)*ref-brilliant

Glad to hear it, James.

Don't forget to mark the thread as SOLVED if you think it is.

Pete