Transferring data sets into an excel calendar

[thoughtiknewwhattodo]

Hi, really hoping someone can help!! I have spent hours trawling the net to try and find an answer to this! I have a calendar set up in excel and I am wanting to transfer a lot of data into this. The data will be in list form and could be amended. For this reason I don't want to manually type each element in to the corresponding date as it may change so would instead want to update the data table. I would also like eventually to be able to apply filters (or slices) to the document to make it more user friendly. In essence i guess i am wanting to create a dashbaord but in calendar format - hope that makes sense!! if you got this far thank you for reading!!!

[AliGW]

Welcome to the forum.

There are hundreds of threads on this sort of thing here already, but every project is slightly different, so there are instructions at the top of the page explaining how to attach your sample workbook.

[thoughtiknewwhattodo]

Thank you for your reply.

I have attached a sample doc with the calendar template on one sheet then a list of bogus dates on the second sheet. I am wanting to populate the specific dates listed with this information initially.

Thank you

[AliGW]

In B5:

=IFNA(INDEX(Sheet1!$A$2:$A$7,MATCH(Calendar!B4,Sheet1!$B$2:$B$7,0)),"")

Drag copy across to H5, then select B5:H5, copy and paste to the other cell ranges.

[thoughtiknewwhattodo]

Fab that is brilliant thank you. If i was to add more data would it add those to the relevant date boxes as well?

[JeteMc]

If I understand the request then:
1. Convert the range on Sheet1 into a table
2. Convert references to the column ranges in Sheet1 in AliGW's formula into structured references: =IFNA(INDEX(Table1[[Cohort Name]:[Cohort Name]],MATCH(Calendar!B4,Table1[[Start Date]:[Start Date]],0)),"")
Let us know if you have any questions.

[Pete_UK]

The sample file is a bit simplistic, in that there is only one row under each date row on the Calendar sheet to record the task. If there are 2 or more tasks possible on a single day then you will need something a bit more complex.

Another drawback with the sample is that you show only a single date for each task, although the column heading is Start Date, which implies that the task might cover a range of dates and so you would have an End Date column as well, and presumably you would want to show that particular task on the calendar for every date of the date range.

You might also want to categorise the data in some way, e.g. to have a Department code and the ability to show only the data for one Department (or all departments) on the calendar.

Let me know if you want to pursue this further.

Hope this helps.

Pete

[menem]

you may use this formula.

B13

Please Login or Register  to view this content.

Regards.

[MrEdujor]

I placed that formula in B13 in the calendar and it just says #name? can someone help?

[JeteMc]

Not sure why you should get the #NAME! error as TEXTJOIN is supported in the 2019 version of Excel.
On the other hand you might be getting a #VALUE error unless you activate the formula by pressing the Ctrl, Shift and Enter keys while the formula is in the edit mode.
As to "can someone help" I attempted to help in post #6 but got no feedback.
Let us know if you have any questions.

[torachan]

The attached may be of interest - there are no on-sheet formula to maintain or corrupt.
Simply double-click on any of the eight rows of any day - pop-up form appears to enter data - if day is blank form allows 'ADD' - if data present form allows 'UPDATE' or 'DELETE'.
On the 'defaults' page add 'date' + 'event' - then on 'Diary' page press 'Update' this stores your listing and clears it for further enteries (note; this is a table and should expand/contract when data is entered/removed.
For dates further in the future use the 'Calendar' (quicker than pressing 'Next' for months ahead).
Do not type in cell as they will not be stored or saved - Always double-click for pop-up form.

[menem]

Please verify the name of TEXTJOIN function in your excel language version, it maybe use different words.

Regards.