We manage multiple excel sheets that contain different production information and inventory information across multiple product lines. We currently manage these sheets inside of different Microsoft Teams which uses Sharepoint on the backend.
Our inventory sheets contain information we'd like to share with certain customers. Currently, we do this by managing the sheets in Dropbox (moving to OneDrive soon). It's more of a double data entry process and results in information not always being up to date.
I have this idea of creating a new Sharepoint Site that will be dedicated to providing this information to our customers. I want to then "push" certain information from our internal spreadsheets to the customer-facing spreadsheets.
There are a couple of dynamics to this. First, we don't want to copy every column. Some columns are for internal use only so we would only want to push the customer-facing information.
Second, there's one sheet in particular where we would only want to push the information if a certain cell on the master internal sheet was left blank. The specific information to give better detail is we sell work trucks. We manage a sheet that shows all the "bare" trucks we have available for "build out" of our fabricated products. On our master sheet, there is a column for the "work order" that the truck has been assigned to. If that work order column is blank, we'd like that row of information for that unit to be pushed to a "chassis inventory" sheet for customers to see it's available as a bare truck. Once we assign a work order # in the work order cell on the master sheet we'd like that unit to be removed from the customer-facing sheet.
Summary of goal is to push selective content from internal documents to external documents automatically with the manual addition/deletion of information from the internal documents (basically a copy paste function from one sheet to the other) automatically, as well as performing the same task but with the dependency of whether or not certain columns contain data.
Further, once the information is pushed to the customer-facing excel file. I'd like to create additional automation based on the information on that sheet by creating folders for the inventory units. Short example: New inventory unit is added to our internal sheet, automation pushes selective information to the customer-facing sheet, additional automation creates a folder for that unit on the SharePoint site which will be used to manually load pictures and additional supporting files such as PDFs. Once the inventory unit is marked sold on the internal excel sheet it will be removed/deleted from customer-facing excel sheet and folder will either be deleted or moved to an archive location.
From my little bit of research, I'm thinking we could accomplish this with Microsoft Flow. Looking for someone to confirm a few things. Is setting up a customer Sharepoint site for this information ideal, or should we stick to OneDrive? Is Flow our best option here or is there other methods that may be a better option?