Dear all,
I am trying to generate a calendar which should automatically add events matching a specific date.
Since a picture is worth a thousand words, here is an attachment.
Any words of wisdom?
Thanks in advance for your help!calendar.JPG
Dear all,
I am trying to generate a calendar which should automatically add events matching a specific date.
Since a picture is worth a thousand words, here is an attachment.
Any words of wisdom?
Thanks in advance for your help!calendar.JPG
Not generally the case with assistance here with Excel. Quite the reverse in fact. A workbook is worth a thousand pictures.
You have a workbook, just upload it. Not many of want to spend time recreating something you already have. We always like to test answers for obvious reasons so don't expect us to jump through hoops.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
I admit I completely misunderstood the purpose of this forum then, as I came over here for help.
The picture that I attached is a snapshot of a workbook that I created manually, simulating the desired outcome.
I have spent countless hours trying to figure it out, mixing and matching Vlookup, MATCH and other functions, and I failed miserably.
Welcome to the forum.
There are instructions at the top of the page explaining how to attach your sample workbook (we can't do anything with a picture of one).
NOTE: you will get amazing help here, but if you complain each time you are asked for specific information, you will get nowhere fast. If you expect us to make an effort for you for FREE, then the least you can do is make an effort as requested. It's up to you.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
My apologies: it was never my intention to "complain", just like it definitely was never my intention to have somebody else do my work for free.
I have limited Excel experience (I have a degree in Chemistry), and I was hoping that somebody could recommend a process (e.g. combining different functions). Finally, I certainly don't mind attaching a sample WB.
Any piece of advice would help.
Hi,
A few points of clarification
1. How representative is the data in the C3:E5 range?
i.e. are all the dates in each of the X,Y,Z columns always chronological?
2. Are all dates in any column ALWAYS later than a date in any of the previous columns?
3. Is the C3:E5 fixed. This is clearly a representative example and may not represent the production workbook. Do you have more columns and/or rows and does whatever you have vary from time to time.
It may be possible to create a compound formula to return the results, alternatively and if it were me I'd use a macro. Is that an acceptable solution?
Given that your upper table will probably grow as you add more data to it, I would suggest that you have that in a separate sheet from the calendar.
You can generate the dates quite easily, rather than type them in yourself, by putting this formula in cell C8:
=B8+1
and then copying this across to AF8. Then you need to decide if you want the dates to continue further across that row, or to have separate rows for each month. If the latter, then you can have this formula (in say B11):
=AF8+1
and in C11:
=B11+1
copied across to AC11, as there are always at least 28 days in February. To accommodate leap years, you can use this formula in AD11:
=IF(MONTH(AC11)=MONTH(AC11+1),AC11+1,"")
and then for the start of March, as you don't know exactly where February ends, you can have this formula in B14:
=MAX(11:11)+1
The rest of the calendar can then be built up by adding 1 onto the previous date. Everything is now controlled by the start date in B8 - change this to 1st January of another year and the rest of the calendar will follow automatically.
Hope this helps.
Pete
Mmm,
Pete's post makes me question what is meant by a 'calendar' in this context
I'd misinterpreted the request as a need to generate the row 9 values which appeared to be derived from the matrix.
Maybe zankzank will clarify
Dear Richard,
thank you so much for your follow-up.
I will try to answer your questions point-by-point:
1. All the dates in each of the X,Y,Z columns will always be dd.mm.yy
2. Unfortunately, to make things more complicated, the dates in one column will not always be later than a date in any of the previous columns.
3. The range is fixed, but there will be many more columns (e.g. Tests) and Rows (e.g. Projects).
Sadly, my experience with MACROs is very limited, but I am trying to learn
Thanks again for your help!
ZZ
Dear Pete,
Thank you so much for your message.
I want to apologize for the confusion: I wasn't really trying to figure out how to create a calendar, but rather how to auto-fill it with events matching specific dates (contained in a table/range).
I totally agree with you though: I should definitely place the calendar (however it is generated) in a separate sheet!
Thank you again for your help!
ZZ
Last edited by AliGW; 02-19-2021 at 10:43 AM. Reason: PLEASE don't quote unnecessarily!
Dear Richard,
My apologies for the confusion: English is not my first language and I am sorry if sometimes I can't explain things clearly.
You are absolutely correct: my goal here is to find a combination of functions to generate the row 9 values which are derived from the matrix.
Thanks again for your help,
ZZ
It would help if you explained what the letters A, B, C and X, Y, Z will actually represent in reality. If these are actually words (names of Projects and Tests), then I don't think it would look very good to join these together in one cell separated by a comma. It would be better to list them in their own cell underneath the appropriate date, so that you might have, say, five cells under each date so that you can accommodate up to 5 "events" for each day.
Obviously, the row for February dates (and for subsequent months) would be moved down to accommodate these 5 cells.
I think your upper table would be better laid out if you listed each date vertically, with an appropriate entry for Project and Test in other columns. That would make it much easier to auto-populate the Projects and Tests on the corresponding date.
I have submitted many such calendar files to the forum and my general approach is to have a list of events in one sheet and the calendar in another sheet. The calendar generally displays a single month, where the month and year can be selected via drop-downs. Up to 10 events can be displayed for each day. I can amend one of these files to suit your requirements if you like.
Hope this helps.
Pete
OK I'll put a macro version together for you.
Hi,
See attached.
It contains two range names and the following macro
Please Login or Register to view this content.
Dear Richard,
thank you so much for the Marco: it works perfectly!
I will try to understand the code so that I can apply it to the real dataset.
I can't thank you enough for taking the time to look into it!
I wish you a wonderful weekend!
Warmly,
ZZ
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks