topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Thursday November 26, 2020, 6:47 pm
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: Need a little help with an Excel file please  (Read 2154 times)

Stephen McBride

  • Participant
  • Joined in 2020
  • *
  • default avatar
  • Posts: 7
    • View Profile
    • Donate to Member
Need a little help with an Excel file please
« on: May 08, 2020, 03:54 PM »
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

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,760
    • View Profile
    • Donate to Member
Re: Need a little help with an Excel file please
« Reply #1 on: May 09, 2020, 02:22 AM »
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

  • Participant
  • Joined in 2020
  • *
  • default avatar
  • Posts: 7
    • View Profile
    • Donate to Member
Re: Need a little help with an Excel file please
« Reply #2 on: May 09, 2020, 10:30 AM »
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

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 11,730
    • View Profile
    • Donate to Member
Re: Need a little help with an Excel file please
« Reply #3 on: May 09, 2020, 10:47 AM »
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
Tom

Stephen McBride

  • Participant
  • Joined in 2020
  • *
  • default avatar
  • Posts: 7
    • View Profile
    • Donate to Member
Re: Need a little help with an Excel file please
« Reply #4 on: May 09, 2020, 01:08 PM »
Thanks Tom, I will give that a go  :up:

Stephen McBride

  • Participant
  • Joined in 2020
  • *
  • default avatar
  • Posts: 7
    • View Profile
    • Donate to Member
Re: Need a little help with an Excel file please
« Reply #5 on: May 10, 2020, 11:04 AM »
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

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 482
  • Hi from the a*** end of the earth
    • View Profile
    • Read more about this member.
    • Donate to Member
Re: Need a little help with an Excel file please
« Reply #6 on: May 11, 2020, 03:09 AM »
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

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,760
    • View Profile
    • Donate to Member
Re: Need a little help with an Excel file please
« Reply #7 on: May 11, 2020, 03:44 AM »
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

  • Participant
  • Joined in 2020
  • *
  • default avatar
  • Posts: 7
    • View Profile
    • Donate to Member
Re: Need a little help with an Excel file please
« Reply #8 on: May 12, 2020, 08:09 AM »
Thanks guys, really appreciate any help  :)

tsaint

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 482
  • Hi from the a*** end of the earth
    • View Profile
    • Read more about this member.
    • Donate to Member
Re: Need a little help with an Excel file please
« Reply #9 on: May 19, 2020, 03:16 AM »
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

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,760
    • View Profile
    • Donate to Member
Re: Need a little help with an Excel file please
« Reply #10 on: May 20, 2020, 03:08 AM »
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

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,760
    • View Profile
    • Donate to Member
Re: Need a little help with an Excel file please
« Reply #11 on: May 26, 2020, 06:48 PM »
any feedback on this?  Did it do the job?  should I try to polish it up a bit?

Stephen McBride

  • Participant
  • Joined in 2020
  • *
  • default avatar
  • Posts: 7
    • View Profile
    • Donate to Member
Re: Need a little help with an Excel file please
« Reply #12 on: May 29, 2020, 05:24 AM »
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  :up:

Many thanks.

Stephen.

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,760
    • View Profile
    • Donate to Member
Re: Need a little help with an Excel file please
« Reply #13 on: May 29, 2020, 07:02 AM »
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

  • Participant
  • Joined in 2020
  • *
  • default avatar
  • Posts: 7
    • View Profile
    • Donate to Member
Re: Need a little help with an Excel file please
« Reply #14 on: May 29, 2020, 01:02 PM »
Yeah, I have a habit of making changes incorrectly  ;D

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.