Hello everyone,
Attached is a file with, on the first sheet, an event table with dates.
The second sheet has a calendar that I found on the web. You can select the month and year and the calendar changes accordingly.
What I would like is to populate the calendar with the events and name of the corresponding person.
Thanks in advance and kind regards,
Marco
You will need to use INDEX and Match for this, i dont think you'll need VBA, think its possible with formula.
I'll take a better look after the meeting i am in
Hope this helps
Sometimes its best to start at the beginning and learn VBA & Excel.
Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
Available for remote consultancy work PM me
That would be great too, thank you!
Something like this, its an array formula so ctrl shift & enter , just playing in calendar E3
=Events!$B$1 & " " & INDEX(Events!A3:A19,SMALL(IF(Events!$B$3:$B$19=Calendar!E2,ROW()-2),ROWS(Calendar!$E$3:E3)),1)
I'll take a better look in about 15mins.
Hi
Yes, it is possible with array formula, although doing it on your example it gets pretty cluttered, it would be something like this, but a lot longer
I think it would be best to give each date area a line per person, then we can just do a similar formula for each person.
Please Login or Register to view this content.
can they do more than 1 event on one day?
This indeed works for just that instance for Person 1.
Thank you.
As for now, I do not think they can do more than 1 event on one day.
Do you mean 1 line for each person in the calendar? The actual data has a lot more people than this example.
Ok, i think it will get a bit messy by formula then. The formula you tried is for 2 people change, person 1's date to 02/01/2014 and person 2's 2st date to 01/01/14 and it will show them.
I'll do some vba
Okay thank you!
HI,
This is my 1st draft, i'll test it a bit more for you
Please Login or Register to view this content.
Last edited by nathansav; 08-07-2014 at 07:04 AM.
Thanks for the code. It seems that right now, whenever 2 people have an event on the same date, the function returns: "Person 1 Event 1Person 2 Event 2".
Is it possible to have each row in the calendar only display one event?
So in this case:
Person 1 Event 1
Person 2 Event 2
That was my original point, you have more peole to add, so it will be massive.
"Do you mean 1 line for each person in the calendar? The actual data has a lot more people than this example"
you only have 4 rows, so if all 8 people are doing a task on that day, you'd need 8...
you could merge the cells and add vbcrlf to this line
strOutput = strOutput + strPerson & " " & wsData.Cells(intRowFound, 1) & vbcrlf
That's true, the calendar only has 4 rows right now, but that could be extended.
I will see how your code works when it's done. I'm also kind of in the dark how many people can do something on the same day.
The output of your code is great too, as long as it's possible to put the results below each other in that one cell.
That seems great, let me just test that out and I'll let you know how it works out! Thanks already for the help!
It works great, and overall it seems like it works like I want too, except for one thing.
The function doesn't seem to update automatically. My workbook is set to automatically update formulas, but whenever I add a date to the list, it doesn't add it to the calendar unless I click on the formula and press Enter again. Might be on my end..?
Originally Posted by nathansav
I fixed the problem I had above by adding this:
Also, it seems it is possible that someone has multiple events on one day. Is it possible to add that?Please Login or Register to view this content.
Sometimes an event can last multiple days. Do you have any idea how to implement that?
Thanks in advance and kind regards,
Marco
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks