save table data to query

[Njiua]

hey everyone!

I've got a tiny challenge making something work in Excel.

let me explain it all to you.
For my work I have to make reports. first I gather all data, then a concept version is made, after that a definite version is made. and finally I send the bill.
to keep my workdata clean and understandable I wanted to make a table in which the amount of files in what cathegory are.

per example:

Date	data gathering	concept version	final version
27-6-2024	4	2	1
26-6-2024	4	3

table A

so in table A it is quite easily visible what work I did, in what timespan. I'd like to keep it that way with 5 rows at most. However what I also want is that if I enter the data, it'll all get saved in another sheet so I can still get my best, average or all work done in X time. (probably by a query which updates the moment the data in tabel A is changed.)

so, here's a short version
I'd like to have a table on my "home" sheet with no more then 5 rows. Every day I open the file I'd like the top row of the table being empty, with just the current date standing in the most left column. and all the other data moving one layer down. this means after 6 inputs* the data is not visible on the "home" sheet. however it is still accesable in another sheet.

*inputs being: on six different days, so if I per example start a project on 13-4-2025 and the next time I open the file is 22-4-2025, that is one input.

I want the old data to be accesible because of certain formula's I can throw at it (like average days till a report is finished or total reports done)
is this achievable?

cheers!

 

[GraH]

I would make something easy.

1. make a folder per "report" in a main folder "reports".
2. create a file for gathering, concept version, final version (! creation date of file = start date of your activity)
3. Use a Power Query to fetch all subfolders from the main folder. The report folder name is the "report" name
4. Each file will show creation date and update date. Hence the lead time is the duration between those two fields.

It all comes down with some procedural discipline.
With Power Query you can do a self join of the table as well, so you can stick to the creation date of each file, meaning each step of your process and calculate the lead time that way.

There should not be any activity for you to track your work on the report. And creating the Power Query is doable with some mouse clicking.