How do you automatically add lines based on exported information on another sheet AND their dates ?

[James bolton]

Hello,

I will try as best as I can to explain what I need help creating.

I am creating a departmental dashboard that outlines all the deadlines on a weekly basis, you chose a "week ending" date from a drop list, and certain information is changed to outline everything up until (or in that) week, and we print it out weekly for our management meetings.

We have a shared calendar in our department that all seniors input departmental deadlines into, I know that I can export the calendar into a separate sheet in excel that outlines what the deadline is & the date. How can I add a macro, ideally through a button, so that every time someone exports the calendar, (please note, for examples, the export takes up columns A-V) lines are added (or removed) on the main dashboard (Row 7 & Below) to show all outlook lines only within the selected weeks date range, and everytime I change the selected week, the rows automatically change again to show only lines from the export within that week range.

Does that make sense ? Any help or insight would be much appreciated.

Thankyou.

JOBO*

 

[eibi]

James,

Double check my understanding of your request -- and tell me if I've got it:

You are already able to import calendar data from Excel in a separate worksheet.

You want to select a given date on your dashboard, and you need help to develop a macro that will

1. Delete the old deadlines/events from the week of the previously selected date, and
2. Find and insert the deadlines/events that correspond to the week of the newly selected date.

Tell a little more about your "week ending date": Will you always be choosing a Friday, for example? And you want to include information back through the Monday of the same week?

Also, a little more about what data (columns A-V) of the Outlook data you want to display on your dashboard.

Isaac

 

[James bolton]

Thankyou for your reply Isaac.

In answer to your questions, 1. is correct, the week ending date will always be a Sunday actually and I would like information to be displayed from the previous week Mon - Sunday (sometimes we have weekend deadlines)

and 2. just need columns A and B displayed (comlumn A is the subject, column B is the start (deadline) date

I look forward to your response.

 

[eibi]

James,

Look at the attached file. (I'm just using a set of dummy dates I exported from Outlook...on the second tab of the file.) When you change the end date, it inserts a new set of rows with the deadlines you need. I assume this is what you want?

Here's the issue I'm running into:

Right now, the macro only inserts new information; it doesn't clear the old information. The more times you change dates, the longer the list of deadlines will grow because I haven't yet built in the deleting (cleanup) function.

I don't want to assume how much space you've allowed in the version of the dashboard you're working with right now -- and I don't want my dummy macro to delete any of your work.

Give me a little more information about the layout of the dashboard you're building...A screenshot or sample file would be helpful.

This way, as I build the cleanup function, I can tell the macro "where to stop deleting" the old content of the dashboard when you update it.

If this is a good enough start, feel free to take it from here without my help.

All best...

 

[James bolton]

Hi Eibi,

Thankyou very much again for your help, its really appreciated. Really almost there.

I've attached a dummy spreadsheet that essentially replicated my exact dashboard layout (without the added info) the code you wrote is almost perfect, just needs to, as you said, delete the old info once a new date is selected, is that possible ?

If not, I'm thinking maybe creating a 'clear deadline' button to clear all deadline rows before selecting a new week, just wondering though how that would work without having to be precise on the number of rows to clear, I guess creating a unique identifier (such as adding a column to the calendar export with the same number/text string in each row) and then writing a code that looks for that precise identifier within the sheet and deleting all those rows ?

Please let me know your thoughts.

 

[eibi]

James,

If you'll always have a "blank" row following the list of deadlines, we can use the blank row as a "stop" and delete all the rows above it.

Not a complicated solution...

See attached.

With exception of some formatting details, I think it's where you want it...Post a reply if you need any further input.

Isaac

 

[James bolton]

Is there like a member voting option or a way to rate your responses, because this is brilliant - exactly what I needed, I don't know how to thank you enough !

 

[eibi]

Appreciate the kind words. Just click "like" in the bottom right corner of my posts if they are helpful.

Feel free to post any time; I've been a member of this forum for several years -- and always found it extremely helpful.

Chandoo WILL make you awesome in Excel.

All best.