Excel Planner

%3CLINGO-SUB%20id%3D%22lingo-sub-2635454%22%20slang%3D%22en-US%22%3EExcel%20Planner%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2635454%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20excel%20spreadsheet%20which%20is%20used%20as%20a%20tracker%20as%20is%20laid%20out%20as%20below%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3EID%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EPLANNED%20START%20DATE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E01%2F01%2F2021%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E02%2F01%2F2021%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E02%2F01%2F2021%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E04%2F01%2F2021%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E05%2F01%2F2021%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E6%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E06%2F01%2F2021%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20make%20the%20table%20for%20visually%20appealling%20and%20easier%20to%20use%2C%20is%20there%20a%20way%20to%20automate%20this%20in%20a%20different%20view%20to%20read%20like%20the%20below%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E01%2F01%2F2021%20-%20Friday%26nbsp%3B%3C%2FP%3E%3CP%3EID%201%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E02%2F01%2F2021%3C%2FP%3E%3CP%3EID%202%3C%2FP%3E%3CP%3EID%203%3C%2FP%3E%3CP%3E'and%20so%20on'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20will%20make%20the%20users%20be%20able%20to%20easily%20see%20how%20many%20ID%20are%20due%20on%20each%20day%20in%20a%20simple%20clear%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EElliot%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2635454%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2635660%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Planner%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2635660%22%20slang%3D%22en-US%22%3EAre%20you%20looking%20for%20a%20solution%20that%20is%20recorded%20using%20a%20Macro%3F%20Because%20in%20some%20organization%20running%20a%20Macro%20is%20not%20allowed%20for%20security%20reasons.%20If%20Macro%20is%20fine%2C%20I%20can%20suggest%20a%20way.%20If%20not%2C%20we%20will%20have%20to%20concatenate%20and%20do%20some%20counting%20etc.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2635690%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Planner%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2635690%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3E%3CBR%20%2F%3EMacro%20is%20fine%20within%20my%20organisation.%20I%20will%20still%20need%20to%20keep%20the%20original%20format%20but%20have%20an%20altered%20view%20for%20other%20people%20within%20the%20organisation.%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20example%2C%20the%20planner%20will%20require%20the%20orignal%20view.%20The%20people%20following%20the%20plan%20will%20require%20the%20new%20proposed%20view.%3CBR%20%2F%3E%3CBR%20%2F%3ECheers.%3CBR%20%2F%3E%3CBR%20%2F%3EElliot%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2636458%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Planner%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2636458%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1036873%22%20target%3D%22_blank%22%3E%40Serdet%3C%2FA%3E%26nbsp%3BIf%20you%20are%20open%20for%20slightly%20alternative%20formats%20the%20following%20is%20easy%3A%3C%2FP%3E%3CTABLE%20width%3D%22324%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22188%22%3EDates%3C%2FTD%3E%3CTD%20width%3D%2268%22%3ETask%20IDs%3C%2FTD%3E%3CTD%20width%3D%2268%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFriday%2C%20January%201%2C%202021%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMonday%2C%20February%201%2C%202021%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EThursday%2C%20April%201%2C%202021%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESaturday%2C%20May%201%2C%202021%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ETuesday%2C%20June%201%2C%202021%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EIn%20the%20attached%20example%20I%20used%20%3DUNIQUE()%20for%20the%20dates%20but%20you%20could%20force%20the%20Dates%20so%20for%20example%20include%20March%201%20and%20it%20would%20show%20blank%2Fno%20task%20IDs%3C%2FP%3E%3CP%3EFor%20the%20task%20IDs%20I%20used%20%3DTRANSPOSE(FILTER())%20as%20you%20can%20see%20in%20the%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2639024%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Planner%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2639024%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20taking%20the%20time%20to%20help%20me%20with%20my%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20anyway%20to%20transpose%20other%20data%20fields%20in%20with%20the%20ID's%3F%20For%20example%2C%20I%20have%20another%20column%20titled%20'removal%20or%20install'%20which%20I%20would%20like%20the%20data%20to%20be%20tagged%20with%20the%20ID.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EElliot%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi All,

 

I have an excel spreadsheet which is used as a tracker as is laid out as below

 

IDPLANNED START DATE
101/01/2021
202/01/2021
302/01/2021
404/01/2021
505/01/2021
606/01/2021

 

To make the table for visually appealling and easier to use, is there a way to automate this in a different view to read like the below

 

01/01/2021 - Friday 

ID 1

 

02/01/2021

ID 2

ID 3

'and so on'

 

This will make the users be able to easily see how many ID are due on each day in a simple clear format.

 

Many thanks,

 

Elliot 

5 Replies
Are you looking for a solution that is recorded using a Macro? Because in some organization running a Macro is not allowed for security reasons. If Macro is fine, I can suggest a way. If not, we will have to concatenate and do some counting etc.
Hi,

Macro is fine within my organisation. I will still need to keep the original format but have an altered view for other people within the organisation.

For example, the planner will require the orignal view. The people following the plan will require the new proposed view.

Cheers.

Elliot

@Serdet If you are open for slightly alternative formats the following is easy:

DatesTask IDs 
Friday, January 1, 20211 
Monday, February 1, 202123
Thursday, April 1, 20214 
Saturday, May 1, 20215 
Tuesday, June 1, 20216 

In the attached example I used =UNIQUE() for the dates but you could force the Dates so for example include March 1 and it would show blank/no task IDs

For the task IDs I used =TRANSPOSE(FILTER()) as you can see in the attached.

 

 

Hi @mtarler 

 

Thank you for taking the time to help me with my problem.

 

Is there anyway to transpose other data fields in with the ID's? For example, I have another column titled 'removal or install' which I would like the data to be tagged with the ID.

 

Many thanks,

 

Elliot

can you give a sample sheet and how you want it to look? Like concatenate the ID and if it is "removal" or "install"?  Like this:

mtarler_0-1628773166938.png

btw, I updated the file to also fix the ranges