Automatically Insert Row in Another Sheet If a row is added (MS 2021)

Hello Lorenzo 

Hope you are doing well.

I am afraid that I need you help again, I would like to make some changes to the file, could you please help me? 

Dear Lorenzo Lorenzo ,

It seems that I will need your help again. I need to disable the columns for the weekends, add sum for the weeks (I want to add a validation rule not to exceed the 40 hours) and sum for each day (with a validation rule not to exceed the 8 hours).

I know how to add the validation rules, the problem is how to add the sums so when new projects are added, no data to be overlapped.

Could you please help me Lorenzo ? 

Many thanks in advance!!

elianaelia 

See my Private Message with an update version of the ProjectsSourceWorkbook...

elianaelia 

Re. Disabling Hardware graphics acceleration does not solve the issue

On the Employee PC
- Open the TimeTracking workbook
- Go to Home (tab) > Cell Style > Right-click on 'Normal' > Modify...

- Format... > Font (tab) > Select 'Calibry (Body)' in the list

- OK > OK
- Save the file > Close it > Re-open it and see how it goes

Hope this will fix the issue as I'm out of ideas...

Hello elianaelia 

Thanks for sharing files

Re "Archive file cannot be 0" when refreshing the PivotTable in ProjectsSourceWorkbook:

- I copied the 2 TimeTracking files in a local folder

- Updated the Full path to Workbook Source Projects + re-hidded the PARAM sheet

- Added some hours on the DummyEmpl file

- Updated the Full path to Directory with Employee sub-folders in the ProjectsSourceWorkbook

and Refreshed the PivotTable no problem

So, made a quick search on the Net ==> https://community.fabric.microsoft.com/t5/Service/Refresh-error-Archive-file-cannot-be-size-0/m-p/1996691

So check the content of your  \\192.xxx.yy.z\homes Folder hierachy

 @L z. 

Disabling Hardware graphics acceleration does not solve the issue neither.

Hello Lorenzo 

No, I do not experience the same with Excel 2021 when I use the code you sent.

However, now I am experiencing another problem with the pivot table in the source file. Even though I have not made any change that table does not retrieve any new data inserted in the employee files with the error "Archive file cannot be 0."

I have not checked yet if Disabling Hardware graphics acceleration solves the issue but I will do so soon and I will update you.

I have sent you privately the files. 

Thank you so much for all the assistance and help you have provided me!!!

elianaelia 

Wow (???) No idea at all why a query would be responsible of something like that

I just did a couple of tests here and with no suprise I can't repro. the issue

Could you:

- Check if you experience the same with your Excel 2021?

- On the user PC see if Disabling Hardware graphics acceleration solves the issue

- Send me in a Private Message the ProjectsSourceWorkbook and the EmployeeTrackingWorkbook you updated

Lorenzo 

It worked, I saw the new projects that I have added in the source file but after a few seconds the cells turn to black.

elianaelia 

You must implement the change to the Tracking files of all users running Excel 2016

You don't need to do it for those running Excel 2021 but you can

Hope this clarifies things otherwise let me know

Lorenzo 

So I would need to copy the below code in every tracking file or just to the 2016 excel files?

Thank you a lot for your help!!!!

Hi elianaelia 

- Select (all) the following query code and copy it to the Clipboard (Ctrl+C):

(table_project_hours as table, short_month as text) as table =>
let
    SourceProjects = Table.AddIndexColumn(
        Table.SelectColumns(
            Table.SelectRows(SourceWbookProjectsTable, each ([Month] = short_month)),
            {"ΕΡΓΟ"}
        ),
        "IDX_PROJECT", 0, 1
    ),
    SourceProjectHours = table_project_hours,
    ProjectHoursColumnNames = List.Buffer(
        List.RemoveItems(
            Table.ColumnNames(SourceProjectHours),
            {"ΕΡΓΟ", "Total"}
        )
    ),
    MergedTables = Table.NestedJoin(
        SourceProjects, {"ΕΡΓΟ"},
        SourceProjectHours, {"ΕΡΓΟ"},
        "PROJECT_HOURS", JoinKind.LeftOuter
    ),
    ExpandedProjectHours = Table.ExpandTableColumn(MergedTables,
        "PROJECT_HOURS", ProjectHoursColumnNames
    ),
    SortedIdxProject = Table.Sort(ExpandedProjectHours,{{"IDX_PROJECT", Order.Ascending}}),
    RemovedIdxProject = Table.RemoveColumns(SortedIdxProject,{"IDX_PROJECT"}),
    AddedTotal = Table.AddColumn(RemovedIdxProject, "Total", each
        List.Sum(
            Record.FieldValues(
                Record.SelectFields(_, ProjectHoursColumnNames)
            )
        )
    )
in
    AddedTotal

- Open the EmployeeTrackingHours file
- Go to Data(tab) > Queries & Connections. The Queries & Connections pane should appear on the right side of the screen:

- Right-click on query 'UpdateProjectHoursTable' > Edit (Power Query Editor opens)
- In the Home tab > Advanced Editor:

- Select all (Ctrl+A) > Paste (Ctrl+V) from the Clipboard
- Click Done (bottom right)
- Close & Load (1st button on the left of the toolbar
- Save the file & Close it
- Ask the user to ckeck this now works before implementing the same change for the others

NB: Users with Excel version > 2016 can also use same query code - Implemented in attached file '2024-EmployeeTimeTracking_DummyEmpl_NEW_BLANK.xlsx'

If you want to explaination let me know

elianaelia 

OK. I don't have a 2016 to check it but I'm 99% sure to know where the problem is and it shouldn't require any Excel upgrade for those users

I need to test something and now that you're live with the solution you will have to implement the change yourself. Not difficult but I have to provide you the steps

Unfortunately I can't do that right now. I'm leaving for a customer project. If by chance I find an hour before next Monday PM I'll test and post...

Cheers

elianaelia 

Something came in my mind... Are the users experiencing the issue running an old/outdated version of Excel? Something like Excel 2016

Hello elianaelia 

I understand the 1st error message. However, after reviewing the query codes I'm not in a position to determine the source of that error + it's quite strange this only occurs with some users, not with others...

Only way I'll have a chance to trace and hopefully fix the issue is to get your ProjectSourceWorkbook + an EmployeeTimeTracking workbook where the issue occurs

Lorenzo 

Hello and Happy New Year,

It seems that I will need your help again as some users are getting the below messages when I add new projects, and as a result, the new projects do not appear in their files. All users have the same file, so I cannot understand why some of them have this specific issue. When I open their files, the new projects are appearing as per normal, and if I save their files after the projects are being loaded then the next time the user will open the file, the below warning messages appear again but this time the new projects are included in the file. So it seems that for these users I would need to open, load the new projects and save their files, every time I add a new project. Do you think we can find a solution?

At your disposal for any further information,

Many thanks in advance for your help!

elianaelia 

Forget my point - my bad 😞 If we're in Jan and an employee log hours in March, those hours are reported in the PivotTable at any time

Sincere apologies for disturbing you with this

(I had different versions of what I delivered you and in the initial version hours logged in March were not reported in the PivotTable before today's date is in March)

elianaelia 

I am not sure if I understood well, but yes if we are in Jan, employees should log hours only in Jan's sheet.

What I tried to explain (but failed 🙂 is: If we're i.e. in Jan and an employee enter hours in say her/his March table this will be saved no problem. However, those hours won't be reported in the PivotTable before today's date is in March

Clearer? Makes more sense?