Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    New Lounger
    Join Date
    Jun 2007
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Warehouse sheet (Excel)

    Edited by HansV to reduce screenshot in size (it caused horizontal scrolling on smaller monitors), and to edit subject to be more descriptive

    Hello. I'm totally new for Excel and I need to make a warehouse sheet.
    Probably it's a simple thing but I'm stuck in the beginning :/
    Please help!
    I have a sheet with the data on all products: part #, name, comments, quantity etc.
    During the sale - the number of parts being sold entered into the appropriate cell.
    I need this number to be subtracted from the total quantity and automatically transferred to the invoice
    alone with the description and a price. Please see the attachment.
    If anyone knows howto please help. Thanks in advance.
    Attached Images Attached Images
    • File Type: gif x.gif (35.8 KB, 15 views)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Warehouse sheet (Excel)

    Welcome to Woody's Lounge!

    You can use formulas to copy values from the Warehouse sheet to other sheets. For example, in the appropriate cell on the Invoice Quebec sheet, the formula

    =Warehouse!G2

    will display the value of G2 on the Warehouse sheet, and automatically change with it. You can use similar formulas for the description and price. If you clear the check box "Zero Values" in the View tab of Tools | Options, the zeros resulting from empty cells on the Warehouse sheet will not be displayed.

    I would also use a formula to keep track of the remaining quantity, but it's not clear to me where you want this.

  3. #3
    New Lounger
    Join Date
    Jun 2007
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warehouse sheet (Excel)

    Thanks so much for your help. The remaining quantity should be displayed right here at the Warehouse sheet in the quantity column.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warehouse sheet (Excel)

    There are some other things that you might also want to look at. For example, if you enter a part number in the invoice sheet, you can use the VLOOKUP function to get the Description, Price, and any other required information from the Warehouse sheet into the Invoice sheet. You will probably need to use VBA code to subtract the quanity sold from the quanity on hand in the Warehouse sheet, but it would be impossible to say exactly what the best way to do this would be without seeing the entire workbook (or workbooks if the invoice sheet is in a separate workbook). Also, are you using a separate Order sheet? In most cases, it is better to have a separate Order sheet with a Submit button that uses code to create the invoice and packing list and update the inventory when the order is completed.
    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Jun 2007
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warehouse sheet (Excel)

    Thanks again guys for replying. Heres xls file of what Im trying to do with the sample invoices
    Attached Files Attached Files

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warehouse sheet (Excel)

    OK, this raises a couple of comments and questions:

    1- The invoices do not contain the part number, so there is no good way to get the Description and Unit Price from the Warehouse sheet. Is there a reason for not putting the part number on the invoice? As it stands with this form, the person entering the information would have to enter Excel formulas into the invoice sheet to link to the information in the warehouse sheet. There would also not be a real good way to get from the invoice sheet back to the warehouse sheet to update the quantity.

    2- How do you want to update the warehouse sheet? When the invoice is completed, do you want to subtract the quantity on the invoice from the Qnantity column on the Warehouse sheet? What should happen to the invoice when this is done? Should it be printed and then cleared for a new invoice? Are you planning to save an electronic copy of the invoice?

    3- What is the purpose of the Quebec, Canada, Export, and Date columns on the Warehouse sheet? Should the quantity from the invoice also be added to the appropriate column? What should happen to the date column? What should happen if there is already a date in the date column?

    4- when a quantity is entered in an invoice, should a check be made to see if there are that many of the item in the warehouse? If so, and if there are not what should happen?

    5- How many items will there be on the warehouse sheet? If there are a reasonable number, then a dropdown list could be used on the invoice sheet to select the item either by part number or description. Would that be something that you would like?
    Legare Coleman

  7. #7
    New Lounger
    Join Date
    Jun 2007
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warehouse sheet (Excel)

    Forgive my incompetence in these kind of things [img]/forums/images/smilies/smile.gif[/img]
    Ok we thought it would be like this:

    All the parts first added to the warehouse sheet with description, quantities and the prices.
    There are also three columns called Quebec, Canada and Export. Each of them has its own invoice.
    Reason for that is the difference in tax systems.
    Once buyer (lets say) from Quebec going to buy some parts from us - the person responsible for
    the warehouse adds the quantity of parts being sold to the appropriate column (in this case - Quebec column).
    Then The numbers added automatically subtracts from quantity in the same warehouse sheet and transfers
    to the Quebec Invoice. And the invoice automatically calculates all the taxes.
    Then invoice going to be printed and saved somewhere else. But these invoices which linked with the warehouse sheet
    remains empty.
    And I should create a new sheet where all sales will be transfered (like history)
    Never mind "date column" [img]/forums/images/smilies/smile.gif[/img]

    Sorry for the mess. And thank you for replying.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warehouse sheet (Excel)

    OK, I think that you are going to find that there are a lot of problems with this approach. This type of application is usually much better done in something like Access. It sounds like you are going to be sharing this workbook between different people and that is asking for workbook corruption since Excel does not do a very good job of sharing workbooks. However, the attached workbook has a button on the Warehouse worksheet that I think does what you want.
    Attached Files Attached Files
    Legare Coleman

  9. #9
    New Lounger
    Join Date
    Jun 2007
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warehouse sheet (Excel)

    Thanks a lot. This is exactly what I wanted. Sorry for bothering again. Is there any way to do the same thing with formulas?
    And another thing: If in the column Quebec a few numbers added randomly - is it possible to send them automatically to the invoice all together
    with their description and prices listed?
    Maybe some of you guys know the examples of these formulas somewhere on the net. I couldn't find much [img]/forums/images/smilies/sad.gif[/img]

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warehouse sheet (Excel)

    Sorry, I do not understand either of you questions.

    What exactly do you want to do with formulas.

    I also don't understand what you mean in the second question. The code I provided in the workbook moves the info from any row with a number in the Quebec column to consecutive rows in the Quebec invoice sheet. That sound like what you are asking.

    Would you upload another workbook that shows what you want?
    Legare Coleman

  11. #11
    New Lounger
    Join Date
    Jun 2007
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warehouse sheet (Excel)

    Nevermind. Thanks to you I already got it. Thanx again.

  12. #12
    New Lounger
    Join Date
    Jun 2007
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warehouse sheet (Excel)

    Hi. Hate to bother you again. But I really need your help with one more thing.
    I used your code to create buttons for each invoice.
    And it works great.
    What I need now is to transfer sales data to the new sheet
    I've inserted into the workbook called "SALES"

    Once numbers added to columns Quebec, Canada or Export
    and the appropriate button pressed - all that stuff goes not only to the invoices
    but to the SALES sheet too.
    Please look at the file you'll understand what I mean.
    Thank you for your time and helping me.
    Attached Files Attached Files

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warehouse sheet (Excel)

    I have a few questions for you:

    1- Why have you created three buttons and three identical copies of the macro (except for the name)? You only need one button and one copy of the macro like I had in the workbook I sent. The macro will create the invoice for whichever column the data is entered into. If you enter data into the Quebec column, then the Quebec invoice will be created. If you enter data into the Canada column, then the Canada invoice will be created. Same for Export. In fact, if you enter data into more than one column, then more than one invoice will be created.

    2- How do you want data transfered to the Sales sheet? Do you always want the new sale added to the end of the Sales sheet? If there is already a sale for the same part on the Sales sheet, do you want a new row for the current sale, or do you want this sale added to the previous sale?
    Legare Coleman

  14. #14
    New Lounger
    Join Date
    Jun 2007
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warehouse sheet (Excel)

    I have placed 3 buttons for the warehouse person. Thought it would be easier for him - just in case [img]/forums/images/smilies/smile.gif[/img] Anyway its just a sample and I'll edit it later.
    Regarding sales - I need a new row to be created for each sold part. (like it is in the invoice). If there is already a sale for the same part - I need a new row for the current sale. Just to have a list of what and how many were sold and if possible when (date).

    Thank you

  15. #15
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Warehouse sheet (Excel)

    Even if you keep three buttons, you only need one copy of the code. See if the attached does what you want.
    Attached Files Attached Files
    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •