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

[elianaelia]

Please for your help!

I have two worksheets, one is the source and the second one is the destination. In the source workbook I have a list of project names. The project names are listed in column A . Adjacent to column A, are in columns the dates of a month, where the employees will manually add the hours they worked for each project for that month.

I managed with tables to link the data in the destination file so when I add a new project in the source workbook automatically is added in the correct row of the column A in the destination workbook. However, when a new project is added in the destination workbook, the formula I used (=FileName.xlsx.Table[Projects]) creates only a cell and not the whole row. I do not want to link the whole table (this would have added a new row), I want the new row to be empty in the date columns and ready to be filled by a user.

How can I automatically create a new row with filled the column A and empty the rest columns of the row when a new project is added in the source workbook?

Many thanks in advance!

[#2 · Accepted solution]

Hi @elianaelia 

My understanding is you have such a Table in say WorkbookProjects:

You want the same table in say WorkbookProjectHours (for employee hour entries) and when you add/delete Projects and/or Dates in WorkbookProjects this should be reflected in WorkbookProjectHours

#1 Is this a good summary or did I misunderstand something?

#2 In term of process what happen/should happen between the end of a month and the beginning of the next month (cf. where the employees will manually add the hours they worked for each project for that month)?

#3 Could you post a picture of your table projects in WorkbookProjects?

#4 Do you happen to Sort the projects in WorkbookProjects?

[#3]

@Lorenzo 

Please look for my answers below:

1 Is this a good summary or did I misunderstand something?

Yes

#2 In term of process what happen/should happen between the end of a month and the beginning of the next month (cf. where the employees will manually add the hours they worked for each project for that month)?

Employees will only data entry the hours they worked per day per project. No different calculations will be made for the end or the beginning of the next month. One sheet for each month.

#3 Could you post a picture of your table projects in WorkbookProjects?

In the attachments you will find a sample of my source file which includes the projects. I will have on sheet per month, I do not mind If have to manually enter the project name of a new project to several sheets in the source file as long as this is reflected to all employees files. For instance, let’s say that a new project comes in Feb, then I will need to add this project name in sheets of Feb and onwards, but what I am seeking is to automatically appear in the sheets of the employees’ files. Even though I have successfully managed to automatically update the destination file when new project is entered in the source file, the problem is that the column with the project names moves down on cell, but the data in the adjacent rows does not (I need a new row to be created when a new project is entered)/

For instance in the below screenshot we see the destination file when the employee has entered some values for ProjectName1 and ProjectName2:

Then I entered a new project name in the source file with the name ProjectName1a by creating a new row.

In the destination file the new project name appears in correct order but the data in the adjacent columns is getting mixed up since the hours entered for the ProjectName2 appear now as hours for the ProjectName1a.

The desired outcome should have been like below:

#4 Do you happen to Sort the projects in WorkbookProjects?

Yes

Many thanks in advance for your help!

[#4]

Hi @elianaelia 

(Thanks for answering so precisely - it's quite uncommon on forums...)

#1 Setup understood. Though, there's something I don't understand...On the Source workbook you shared I see hours at zero, I guess this is for demo. only. However, what I need to understand is
Q: How to do you envision updating those hours according to hours enterred in the Destination workbook?

#2 Re. I do not mind If have to manually enter the project name of a new project to several sheets in the source file
I don't think you have other options

#3 What I have in mind is based on queries (Power Query) between the Source and the Destination workbooks. This has a few implications:
3a - Changes (project addition, sorting...) to the Source workbook can't be reflected in the Destination workbook until the Source file is Saved
3b - When users will open the Destination workbook there will be a few seconds (depends on PC velocity) of latency before they see changes made to the Source workbook and consequently before they can add/update their project hours
3c - In the Source worbook (and in the Destination) you'll have 12 Tables (1 per month), including the columns with Dates (not the case in what you shared where only the PROJECT column is formatted as Table)
3d - The Tables in the Source & Destination workbooks must remain "stable". No renaming or things like that
3e - It's a MUST that users don't make any change to the PROJECT column in the Destination workbook
Q: Before I spend hour(s) on this, is this acceptable to you? (I know it's doable as I already did that n time, it's just tricky to put in place and 12 sheets/tables just make it more challenging)

#4 Could you give me a sense re. the max number of PROJECTs you would have per month?

#5 You come back a bit late given what I now have on my plate until the 28th (at the earliest). Just a comment to set expectations as I suspect you'd like this to be in place for Jan 1st 2024. Until the 28th I'll have a bit of time to read your reply though, so the sooner, the better 🙂

EDIT

#6 Any good reason to have a [Total] column in the Destination workbook?

IMHO the Destination workbook should contain facts only

[#5]

@Lorenzo 

Thank you so much for your quick response!!

My answers below:

#1 The source file will contain only 12 sheets (one per month) with the project names. The zeros in the source file can be deleted, they where initially entered in order to create a conditional formatting for the destination file; when the user enters the hours worked in whole numbers the colour of the cell changes to green so that is easier to find the projects that the particular user worked.

Each employee will have their own file and if the Accounting dpt wants the total for each project then with another file we will add the hours of each project, but this should not worry us yet.

#2 Re. I do not mind If have to manually enter the project name of a new project to several sheets in the source file
I don't think you have other options 

OK

#3a - Changes (project addition, sorting...) to the Source workbook can't be reflected in the Destination workbook until the Source file is Saved

OK

3b - When users will open the Destination workbook there will be a few seconds (depends on PC velocity) of latency before they see changes made to the Source workbook and consequently before they can add/update their project hours

OK

3c - In the Source workbook (and in the Destination) you'll have 12 Tables (1 per month), including the columns with Dates (not the case in what you shared where only the PROJECT column is formatted as Table)

Only the Project column (column A) with the project names is formatted as table, and I have created 12 tables, one per month (I am attaching again the files, maybe I have uploaded previous versions). The adjacent rows are not included because when linked with the destination file the user will not be able to enter his/her working hours, as the link to the table will be deleted. Otherwise, if that worked, I wouldn't have any problem.

3d - The Tables in the Source & Destination workbooks must remain "stable". No renaming or things like that

OK

3e - It's a MUST that users don't make any change to the PROJECT column in the Destination workbook

OK

Q: Before I spend hour(s) on this, is this acceptable to you? (I know it's doable as I already did that n time, it's just tricky to put in place and 12 sheets/tables just make it more challenging)

If we managed one sheet to work then we will just copy the steps to the following sheets. I forgot to mention that the files will be stored in our server, and while the formula to retrieve the project names worked fine on my desktop when I change the links to the server even though the destination file was automatically updated, when I close and reopen the file I have the following error and the links do now work:

Many thanks in advance for your time and help!!

[#6]

@elianaelia 

(you forgot a few of my points - repeated below)

Before everything else, if you don't mind forget about your current setup with links, as you've experienced this won't work. With what I have in mind you will have - in the Source workbook - 12 Tables of > 30 columns ([PROJECT] + all the Date columns + [Total]). Depending on next responses the [Total] column could be removed...

OK, one Destination workbook per employee. Files stored on a file server
Q1: Will you store all employee Destination workbooks in the same folder?

For consolidation/reporting purpose that would be ideal
Q2: If you store them all in the same folder do you have a standard naming convention, i.e.?:
- ProjectHours_DavidB.xlsx
- ProjectHours_ElianaE.xlxs
...
again that would be ideal for or consolidation/reporting

Previous points:
Q3: Could you give me a sense re. the max number of PROJECTs you would have per month?
This is to check scalability

Q4: Any good reason to have a [Total] column in the Destination workbooks?
(IMHO the Destination workbooks should contain facts only) Know that I know there's 1 Destination workbook/employee this makes a bit of sense for them to see their Totals

[#7]

@Lorenzo 

Q1: Will you store all employee Destination workbooks in the same folder? 

I was thinking in different folders but if is better to have them all in one, then we will go for that one and I will lock each file.

For consolidation/reporting purpose that would be ideal
Q2: If you store them all in the same folder do you have a standard naming convention, i.e.?:
- ProjectHours_DavidB.xlsx
- ProjectHours_ElianaE.xlxs
...
again that would be ideal for or consolidation/reporting

yes something like that, ProjectHours_SurnameN.xlsx 

Previous points:
Q3: Could you give me a sense re. the max number of PROJECTs you would have per month?
This is to check scalability  around 150 projects

Q4: Any good reason to have a [Total] column in the Destination workbooks?
(IMHO the Destination workbooks should contain facts only) Know that I know there's 1 Destination workbook/employee this makes a bit of sense for them to see their Totals

yes we need the total per employee per project

Thank you.

[#8]

Hi @elianaelia 

A couple of points/questions (should be the last ones...)

#1 TimeTracking-Source
In the Source workbook, beside adding/removing/sorting projects, nothing (hours) will actually be enterred in the 12 sheets/tables, correct?

#2 TimeTracking-Destination
According to your previous reply I will assume the following:
- Employees time tracking workbooks will be stored in the same folder
- Naming convention will be like:

ProjectHours_SurnameN.xlsx or

2024-ProjectHours_SurnameN.xlsx or

ProjectHours24_SurnameN.xlsx

What's important here - to extract the Employee name/surname - is to have a known/constant delimiter. In above examples this would be the underscore. In other words we would extract the Employee name/surname according to what's between the underscore and the file extension (.xlsx)
If you want to opt for a different delimiter please let me know

#3 Pivot Table
Yesterday I started to talk about consolidation/reporting. What I have in mind (already tested) is to add a new sheet on the TimeTracking-Source workbook to consolidate the hours, from all Employee TimeTracking-Destination workbooks, in a Pivot Table. This would look like:

- Helps/Makes sense?
- Are you a bit familiar with Pivot Tables?

[#9]

@Lorenzo 

#1 TimeTracking-Source

Yes correct! No hours will be entered in the source file, only in the destination file.

#2TimeTracking-Destination

- Employees time tracking workbooks will be stored in the same folder

Yes since you suggested that it will be better, I will lock the files.

- Naming convention will be like:

ProjectHours24_SurnameN.xlsx

#3 Pivot Table

Yes I am, that would be great!!!

Thank you so much for your help!!!

[#10]

@elianaelia 

Thanks for the prompt reply. Everything OK here but one thing: What do you exactly mean with "I will lock the files" ???

[#11]

I will password protect files so that employees would be able to see only data from their own files.

[#12]

@elianaelia 

Ah, we hit a roadblock 😞 as Power Query can't open a password protected workbook

Is this a showstopper for you?

If it is we might have a solution (I can't test it) with a Directory structure (on the server) like:

where you give individuals the appropriate rights to their respective folder only + the person who manage the TimeTrackingSOURCE workbook has at least read rights on ALL Employee sub-folders

Again I can't test this here but in principal this should work. Let me know your thoughts and if such a structure is a must I'll see what I can do...

[#13]

@Lorenzo 

Yes, I can create subfolders and give permissions accordingly. Users should not see data from each other that is why I need to find a way to protect the input data, and giving permissions to the employee's respective folder only is a way around.

[#14]

@elianaelia 

OK, so we have another little challenge: nothing will prevent an Employee from storing other Excel file(s) in her/his personal folder

The query that consolidates (for the Pivot Table) the Employee hours cannot randomly exclude any other extra file(s). Only solution I see again is to agree on a file naming convention

In the Source workbook you will have a couple of parameters. One of them is used by the consolidation query (I'll explain later) to add (concenate) the Year to your "text kind of dates" (i.e. 1-Φεβ is not a date from an Excel standpoint, it's a text value) and to convert the resulting text value as an actual date value

To give you a sense, the parameter I talk about looks like this:

So, proposal to deal with this last (hopefully) challenge is to agree on a file naming convention where the first 4 chars of the file name = the tracking/reporting year. Destination file names would then be:

YYYYTrackingOrAnyOtherNameYouWant_Surname.xlsx or

YYYY-TrackingOrAnyOtherNameYouWant_Surname.xlsx or

YYYY_TrackingOrAnyOtherNameYouWant_Surname.xlsx or

YYYY TrackingOrAnyOtherNameYouWant_Surname.xlsx (I usually avoid spaces but it's your call)

Adopting such a convention also allows storing previous/next years tracking hours in the same folder

Listening... 🙂

[#15]

I am not sure whether I understood well the issue, but if we need a file naming convention, we can proceed with YYYYTrackingOrAnyOtherNameYouWant_Surname.xlsx.

[#16]

@elianaelia 

OK done deal with YYYYTrackingOrAnyOtherNameYouWant_Surname.xlsx (Thank You). I'll explain everything later....

I'll be with a customer for the remainder of the day and then off until Wednesday mid-day. If no more surprise you should have something in hands by end of day Wednesday/Thursday mid-day

Wish you (& yours) a Merry Christmas

Cheers

[#17]

Thank you so much for the time and your help!!!

Marry Christmas to you too!!!

[#18]

Hi @elianaelia 

Attached files:

• QuickDoc.docx. If you don't have MSFT Word let me know, I'll convert it as PDF
• ProjectsSourceWorkbook. Rename it as you want. It doesn't need begin with YYYY
• 2024-EmployeeTimeTracking_BLANK.xlsx. To be renamed as i.e. 2024-EmployeeTimeTracking_ElianaE.xlsx, 2024-EmployeeTimeTracking_DavidB.xlsx...
• 2024-EmployeeTimeTracking_DummyEmpl.xlsx. See paragraph Recommendations in QuickDoc

Any issue/question/need for clarifications please let me know

[#19]

@Lorenzo 

I will study your answer and I will try to apply it. In case I face any problems I will reach you again.

Thank you so much for your help and I would be grateful if you could show me how you created the queries and how you work around in order to be able to do it on my own next time.

[#20]

@elianaelia 

I would be grateful if you could show me how you created the queries and how you work around in order to be able to do it on my own next time

It's not that I don't want but this sounds unrealistic I'm afraid (learning Power Query is a journey...)