How could I automate a tracking list of "how many projects users were on at given dates"

[PierrickE]

Hi,

I managed to get where I needed to, certainly not in the most efficient way, but it's working.
Now, I would like to automate the process if the data sheet is updated.

So, from a data sheet like this example : Data Sheet , I would like to get a list like this : List.

Got it this time, with manual editing and some formula :

=(NB.SI.ENS(Sheet1!$C$2:$C$13;Sheet2!B$1;Sheet1!$B$2:$B$13;Sheet2!$A2))+(NB.SI.ENS(Sheet1!$D$2:$D$13;Sheet2!B$1;Sheet1!$B$2:$B$13;Sheet2!$A2))+(NB.SI.ENS(Sheet1!$E$2:$E$13;Sheet2!B$1;Sheet1!$B$2:$B$13;Sheet2!$A2))+(NB.SI.ENS(Sheet1!$F$2:$F$13;Sheet2!B$1;Sheet1!$B$2:$B$13;Sheet2!$A2))+(NB.SI.ENS(Sheet1!$G$2:$G$13;Sheet2!B$1;Sheet1!$B$2:$B$13;Sheet2!$A2))

(in french, "NB.SI.ENS" = "COUNTIFS")

Now, I would like this list to update if the data sheet is updated.

That means adding/removing columns for each individual date found (in the data sheet's specific columns). And then counting everytime a date fits an user.

And, if possible, don't auto-update but make a button to press for that.

I guess I'll have to use macros/scripts, but I'm really new to this.

I anyone could help or direct me toward some guide, I would be gratefull.

[#2]

@PierrickE 

An alternative could be Power Query.

[#3]

@OliverScheurich 

Thanks for your reply.
I could use Power Query to automate my actions and repeat them to update.

But we'll have to use it on a few PC from the office and can't install anything on most.
That's why I would like to find a way to create a macro/script for it.

The List I made with a manual output could be use as it is. Maybe a simple macro to add an update button.

But my major issue is that dates are going to move. I'm looking for a way to make that button "grab" every single date, from a range (7 columns and 500 rows), and take those individual dates as colum headers (creating new ones if needed and deleting old ones, or plainly overwriting all the old ones) for the Listing.

[#4]

Hi @PierrickE,

Thanks for reaching out and apologies for the delayed response! I think an Office Script might be able to help you out; to double-check, do you have access to the Office Scripts feature? You can confirm this by seeing if you have the Automate tab in Excel on the web.

If you can use Office Scripts - before I give specific suggestions, could you clarify what you mean when you mentioned that dates are going to move?

Best,

Michelle