Please help me create a calender on Microsoft Excel using dates in the spreadsheet from another tab.

June 25, 2012 2:15 PM

Please help me create a calender on Microsoft Excel as I can't find any help online.

So.... I'm good enough with Microsoft Excel (2010 version I think) to be able to create good enough spreadsheets for work, tracking jobs and providing data and what not, but that's as far as I can go.

I currently have a master sheet, with hyperlinks of a customer's name, which takes me to each customers individual sheet. This sheet has a few dates on it that I need to have on the master sheet so I know when to book in customer visits.

I would love to be able to have it so that I can have these dates automatically show up on the master sheet or a calender that automatically updates itself/has a refresh function. Is there such a thing within Excel or something similar to this? If there is, could someone point me in the right direction or tell me how please.

Thanks in advance,

Sockpim

posted by sockpim to Computers & Internet (3 answers total)

Can you be a little bit more specific about the formatting of the customer sheet?
Would something like a table with dates down a column and customers across the top work?
In other words, each row would indicate a day, and you'd read it across to see what customers you'd book for that day.
posted by hot soup at 2:25 PM on June 25, 2012

Response by poster: Hmm... well there is more than one date per tab. So... Hypothetically speaking... if customer a. had his car cleaned every two months and serviced every six months. there would be a column of dates for his clean (25/6/42, 25/8/12, 25/10/12 etc) and a column of dates for his service (25/6/12, 25/12/12 etc) I'd like to be able to seed the next date coming up for each of the two categories or have a calender showing when the next jobs will be.
posted by sockpim at 2:30 PM on June 25, 2012

Assuming:
1) The sheet that the following formulae are on is called "Summary"
2) Each customer sheet is called the name of the customer
3) On customer sheets, where there is a column of dates, there is a heading of what the activity those dates refer to is (and that this heading is in row 1)
4) There are two columns of dates on the customer sheets, in columns A and B

Then, for the summary:
a) Have a heading row of dates, so A1 is =today(), B1 is =A1+1, C1 is =B1+1 etc
b) Have a heading column of customer names, so A2 is CustomerA, A3 is CustomerB etc
c) Each data item can then be a series of countifs, using an indirect lookup based on the customer name in b) as the range and using the date in a) as the criterion. If the countif is greater than 0 then we have a match so use the heading of the relevant column on the customer sheet as the reminder text. So
B2 is =IF(COUNTIF(INDIRECT($A2 & "!A:A"),Summary!B$1)>0,INDIRECT($A2 & "!A1"),"") & IF(COUNTIF(INDIRECT($A2 & "!B:B"),Summary!B$1)>0,", " & INDIRECT($A2 & "!B1"),"")
C2 is =IF(COUNTIF(INDIRECT($A2 & "!A:A"),Summary!C$1)>0,INDIRECT($A2 & "!A1"),"") & IF(COUNTIF(INDIRECT($A2 & "!B:B"),Summary!C$1)>0,", " & INDIRECT($A2 & "!B1"),"")
etc

Notes:
i) You could always have the starting date for the headings (i.e. A1) be based on a date that you type explicitly, rather than being dependant on whenever you've got the spreadsheet open
ii) If the location for these forumulae is not going to start at row 1 and column A, then do watch out for the dollar signs locking various rows and columns in c). That's the sort of thing that I usually miss when moving things around
iii) The reminder text may not always look totally elegant as it is but should be a decent starting point
posted by MUD at 9:53 AM on June 26, 2012