Forum Discussion
Automatically Insert Row in Another Sheet If a row is added (MS 2021)
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!
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?
64 Replies
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?
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!
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
