Sending out data (stock on hand) to clients

Hi everyone,

I have an issue here. I have a master file with data that I export once a week to send out to clients.

First problem is that everyone that is sending out the data can edit on the sheets, but does not change it with the correct data. How can I prevent editing on these sheets and templates from other users?

Second on is that; I have about 19 clients to send the stock on hand to but not all clients gets the same products. I do have a a template out with all the clients and with each sheet having their products on the sheets. How can I make it easier to edit in the future and also not to have other users making the mistake to send out the wrong stock on hand?

I do hope that this is posted in the right place

Originally Posted by Landi

Hi everyone,

I have an issue here. I have a master file with data that I export once a week to send out to clients.

First problem is that everyone that is sending out the data can edit on the sheets, but does not change it with the correct data. How can I prevent editing on these sheets and templates from other users?

Second on is that; I have about 19 clients to send the stock on hand to but not all clients gets the same products. I do have a a template out with all the clients and with each sheet having their products on the sheets. How can I make it easier to edit in the future and also not to have other users making the mistake to send out the wrong stock on hand?

I do hope that this is posted in the right place

Nobody to help me?

You may have a better chance of a response if you can upload a small but representative subset of your workbook (not a screenshot) with any sensitive/proprietary data removed that illustrates the problem and makes it clear what your expected results are.

To attach a workbook, click on “GO ADVANCED” and then scroll down to “Manage Attachments” to open the upload window. Choose your file then click on “Upload”, scroll down then click on “Close this window”, then “Submit reply”.

Evening all,

I hope that my example is attached.

Stock data changes once a week. As you can see that i have a internal template that has all the ranges. And Client A - D all gets different ranges plus the Original Range.

Sometimes new stock is inserted and also some stock gets taken off. Ir all depends on what the invoicing system show and also what the manager says goes up or taken off

For your first problem from your post #1, deadlyduck offered a solution in post #3. Does that solve this particular problem?

Your second problem in post #1 asks "How can I make it easier to edit in the future?" and in post #5 you say: "Sometimes new stock is inserted and also some stock gets taken off."

I'm still trying to understand this second problem. Is it that when you import to the "Stock Data" worksheet that products may have been added or removed that would require you to adjust your client worksheets but that it is not obvious when such additions or removals have been made?

Hi all,

GeoffW283, Thanx problem one would be sorted with password protection.

Problem 2: Client A is allowed to source The original Range and his exclusive range only available to him. Head Office will decide that for that exclusive range they will add another product, then when that product is manufactured and sent to the warehouse, we as the supplier need to show the client once a week what in stock is so that they can order the next week. But because the client are allowed only a certain range they cant see what our other clients get, because obviously the stock is a different price to other and so forth.

Because I took it on myself to make this project my own, I have to do all the editing on this. And because I dont always have time to do it, I need to do it as easy as possible. I need to do it as quickly as possible and also efficient and thoroughly as possible. No room for mistakes.

Thanx

In looking at your attached file I did a couple of things - I don't know if they are of value.

1) I added a check in 'Stock Data' col-G to make sure that there was nothing new in "Stock Data" that wasn't covered in "Internal SOH". There were no problems.

2) I did the reverse check to see if there were stock codes in "Internal SOH" that were not mentioned in "Stock Data". I did this by modifying the formulas you have in "Internal SOH" col D thru col-I. Your current formula shows zero BOTH when "Stock Data" has that value AND when that stock code is missing from "Stock Data". I modified the formula to show an "X" when that stock code is missing from "Stock Data".
In D5 then copied across and down to all similar cells:

Formula:

Please Login or Register  to view this content.

This shows what may be problems. For example:

• RAM252 (Irish range 5) is not present in "Stock Data"
• RAM366 (Welsh range 5) is not present in "Stock Data"
• None of the stock codes in "Internal SOH" A39:A48 match "Stock Data"

If none of this is helpful to you then perhaps you could describe your workflow step by step and point out which are the time consuming or error prone manual steps that you would like to see automated if possible.

The attached workbook implements the changes noted in (1) and (2) above.

Hi Geoff,

I just used it as an example as I cant use the true product names and client names.

So I will have a look at those change you made and see if it will work for my particular project.

Thank you

OK - so if that didn't help then perhaps you could describe your workflow step by step and point out which are the time consuming or error prone manual steps that you would like to see automated if possible.