Need a little help with an Excel file please

[Stephen McBride]

Hi all,

We have a small family run business (online retail)
Every week we do a stock update to make sure we are not running low on certain products.

I created an Excel file that I manually Update - I deduct the stock sold that week from one column and add it to the overall sales of that product in another column.

Sometimes the numbers are not easy to do the math in my head for the overall sales column, so I need to use a calculator.

It would be great if I had a formula that automatically updated the overall sales. ie- I sell 27 of product X and manually decrease the stock on hand by 27 and the overall sales goes UP by 27.

Apologies if this seems confusing or very simple to do, I just can't figure out how to do it myself.

I am unable to attach the original Excel Worksheet as only xml file updates are allowed.

I have attached an xml version and would very much appreciate if someone could take a look at it and give me some advice.

Many thanks,

Stephen.

[Target]

question, and apologies if this seems inappropriate for your situation. 

Why 'assume' that the 2 correlate?  I don't know what your product base is but would assume that you might need some means of identifying lost items, ie just because the stock level has decreased a corresponding increase in sales shouldn't necessarily be assumed.

FWIW I'd be happy to have a look at it for you, but I will need a copy in xl format (I don't have access to excel at the moment, but I can work with spreadsheets...)

[Stephen McBride]

Hi Target,

regarding identifying lost or defective items, I simply subtract these also from the Stock Sold each week. This file is not very important for overall sales figures, but more so to see how many items I have in stock at any time and a good, general idea of overall sales of each product variation.

Unfortunately I am unable to upload an XL file as it is not supported in Attachments, Allowed file types.

I very much appreciate your input, thanks.

Stephen.

[tomos]

Unfortunately I am unable to upload an XL file as it is not supported in Attachments, Allowed file types.

-Stephen McBride (May 09, 2020, 10:30 AM)

Can you zip it? I think it should be okay then

[Stephen McBride]

Thanks Tom, I will give that a go 

[Stephen McBride]

Hi,

I have zipped the Excel file (unable to save it to XL format, could be an Office 365 thing!)

In a nutshell, each time I manually deduct from cell B, I would like cell D to automatically increase by the same amount.

I hope this makes sense.


Thanks all and stay safe.

[tsaint]

1. Over in column G, set up the column to contain Columns B + G. eg in g2: =b2+d2, then copy down
2. Copy column G to column E, but instead of the normal paste, use "paste special" and paste VALUES rather than formulae
3. Setup column F to contain column E - column B eg in f2: =E2-B2
4. Column F heading becomes "Sold" and Column D can be deleted

[Target]

pretty sure I can automate this, but it might take me a few days to organise...

TSaints solution works, but seems (to me) like a convoluted way of doing it

[Stephen McBride]

Thanks guys, really appreciate any help 

[tsaint]

Target.. would really like to see the automated version, hopefully elegant - expect I'd find it useful too or at least get some good ideas.

[Target]

apologies for my tardiness, been a bit preoccupied with the job hunt...

regardless, try the attached.

It's just an on-change macro, ie when a value on the sheet changes it fires off.  It uses a couple of 'helper' values - the stock column (B) is duplicated in column D and used to check for changes in stock levels, and there are subtotals in cells B1 and D1. 

• If the value in the stock column goes up, the corresponding check value in column D is amended accordingly.
• If the value in the stock column goes down, the difference between the new value and the check value is added to the sales column (E), and the check value is amended to match the new stock value.

in practice both row 1 (with the subtotals) and the check column would be hidden as they don't add anything useful to the user (and any user changes to those values would result in corrupted values)

its not terribly pretty, but it works OK for a first cut

appreciate any feedback you might have

[Target]

any feedback on this?  Did it do the job?  should I try to polish it up a bit?

[Stephen McBride]

Hi Target,

in principle it works yes, although I can't seem to get the Undo function to work.

You have definitely grasped the concept I had 

Many thanks.

Stephen.

[Target]

In principle it works yes, although I can't seem to get the Undo function to work.

-Stephen McBride (May 29, 2020, 05:24 AM)

Unfortunately theres no undo, but im not sure why you might need it (unless you're wanting to reverse changes made incorrectly?)

[Stephen McBride]

Yeah, I have a habit of making changes incorrectly 

It works though and I guess I could save regularly as I go.

Also far more user friendly when cell D is hidden.

When I close the document I get a warning "The picture is too large and will be truncated" Whatever that means!

Thanks.