deLockloire
Board Regular
- Joined
- Apr 4, 2008
- Messages
- 123
- Office Version
- 365
- Platform
- Windows
Hi all,
I'd like some advice on how to achieve something in excel. We have an NGO operation in 12 countries. All of these are required to report their activities in an excel sheet. Which means we have 12 excel sheets with the same structure where they only need to fill in the columns in bold (i.e., activity data). Something like this:
The Unique ID is generated by a formula, and this is an excel table. Now here is the tricky part which I need some help with. We grab all data from the 12 files, and append them into one central table except for the last column (Notes). HQ operates this central file. What I'd like to achieve is that in the central table (which looks like this, since that is, after all, just an appended version of all these local tables), if HQ writes something to the column Notes, for a specific activity, 1) it should appear in the table of the local branch's table AND 2) the notes should stay in the row of the activity it was meant for.
I know it's always wonky when trying to do these two way data exchanges, but we achieve the 1) by doing a power query from the central file to the local file with only the unique id and the notes, and then in the local activity table, we just do an xlookup for the notes. This works alright. However, what does not work is 2). Currently, if HQ writes something in notes, it tends to shift rows as new items appear in the 12 local activity table because when data are appended, activities will shift down. Any way to make the notes column stick to the row it was meant for or any other ideas how we could set this up properly?
Regards,
deL
I'd like some advice on how to achieve something in excel. We have an NGO operation in 12 countries. All of these are required to report their activities in an excel sheet. Which means we have 12 excel sheets with the same structure where they only need to fill in the columns in bold (i.e., activity data). Something like this:
| Unique ID | Activity Data 1 | Activity Data 2 | Activity Data3 | Activity Data4 | Notes |
The Unique ID is generated by a formula, and this is an excel table. Now here is the tricky part which I need some help with. We grab all data from the 12 files, and append them into one central table except for the last column (Notes). HQ operates this central file. What I'd like to achieve is that in the central table (which looks like this, since that is, after all, just an appended version of all these local tables), if HQ writes something to the column Notes, for a specific activity, 1) it should appear in the table of the local branch's table AND 2) the notes should stay in the row of the activity it was meant for.
I know it's always wonky when trying to do these two way data exchanges, but we achieve the 1) by doing a power query from the central file to the local file with only the unique id and the notes, and then in the local activity table, we just do an xlookup for the notes. This works alright. However, what does not work is 2). Currently, if HQ writes something in notes, it tends to shift rows as new items appear in the 12 local activity table because when data are appended, activities will shift down. Any way to make the notes column stick to the row it was meant for or any other ideas how we could set this up properly?
Regards,
deL
