Forum Discussion
Automatically Insert Row in Another Sheet If a row is added (MS 2021)
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
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 optionsOK
#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 hoursOK
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 thatOK
3e - It's a MUST that users don't make any change to the PROJECT column in the Destination workbookOK
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!!
(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/reportingPrevious points:
Q3: Could you give me a sense re. the max number of PROJECTs you would have per month?
This is to check scalabilityQ4: 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 TotalsQ1: 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/reportingyes 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 projectsQ4: 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 Totalsyes we need the total per employee per project
Thank you.