Automation/Updating

02/22/21
Answered - Pending Review

Hi all,


Hoping to get some suggestions on something I'm stuck with.


I have a master sheet that I replace all the contents once a week (via a mass copy and paste). I have a group that needs to add data to one of the columns, however I'm trying to find a way of avoiding them from having any type of write access to the master sheet. In an ideal world, I was hoping to be able to create a report for them with a column they column they can update without feeding back to the master, since each week I replace everything with new data that comes from an external report. I would then tie the two sheets externally. I know it sounds sort of messy, but hope it makes sense somehow.


Thanks,

Carlos

Answers

  • Tamara GordonTamara Gordon ✭✭✭✭✭

    First question are they updating the data in the column after you replace all the contents or before?

    You have a couple of ways to get this done and may be able to automate your mass copy and paste if you have the right apps. I have a similar situation that I have our schedule that changes (hourly sometimes) within all our production sites. I use Data Uploader to update off of a CSV file that updates every 12 hours to bring in the schedule and overwrite anything that is no longer on the sheet and update any items that the date, time or location has changed. Once this is updated I have 3 other groups that update information that determines other information that is needed. This is where I have a Rollup sheet that combines the new information from the other teams. This rollup sheet uses Index(Match) formulas and Data Mesh app. I then have a report for each location that allows them to see the updated columns they need plus has a column they can update the information they need to add in. Lock the columns in the rollup sheet to anything that is automated or formula driven so that they can not change the column in the Report created.

    I hope this has helped but am open to answering any specific questions you may have.

  • Carlos YanesCarlos Yanes ✭✭✭✭✭

    @Tamara Gordon

    They are replacing before and after, it's an ongoing task. Each time I do a mass update to the master (which is done via copy and paste, because we do not have the data uploader license) I have to find a way to bring back their updates and they will continue to update as new items get added.


    Thank you for your suggestion, I'm just not sure it will work in my situation since we don't have the added license options. :(

    Any other ideas?

  • Tamara GordonTamara Gordon ✭✭✭✭✭

    @Carlos Yanes

    You have a lot of manual on your hands. I was having to do a lot of similar manual actions prior to getting our company to approve the update to our licensing. I would at the least use the reports for the department to add their info into it. Lock the columns in your primary sheet that you don't want them to change and leave the column they can change unlocked. Create the report so they only see the columns you want them to see. The other question is what rights does the other department have to the sheet? They will have to have editor rights to the underlying sheet to be able to make changes to the column you would like them to in the report. I have a similar situation with our production facilities where I want them to manage through reports but not the underlying sheet. I have created folders for each facility that shows only their reports and I have only trained them to look and work with their reports.

  • Carlos YanesCarlos Yanes ✭✭✭✭✭

    @Tamara Gordon


    Morning! Yes, very manual with the different tasks at hand. I was initially going to create the reports for them to use, but might as well just have them work off the master sheet I guess and locking the other columns, since I think it would be somewhat redundant. Each week, I will have to replace the contents of the file and vlookup the data they already entered previously.

  • Tamara GordonTamara Gordon ✭✭✭✭✭

    @Carlos Yanes I highly recommend using INDEX(MATCH) over VLOOKUP if possible. VLOOKUP works but if you have large amounts of data it will slow your system every time it has to load. INDEX(MATCH) does the same function but doesn't require the amount of data to reload every time you open and close sheets or refresh.

  • Carlos YanesCarlos Yanes ✭✭✭✭✭

    How does that formula work? No familiar with it.

Sign In or Register to comment.