Software

Question

Excel formulas

If I have a stock list and a picking list in different wookbooks, and in the picking list I had to enter a code and quantity, and when I did that I wanted it to look for that code in my stock list, and remove the quantity that I entered in the picking list from my stock list.for example:- Code Quantitytaglarge 18I would want it to look for that code in my stock list then remove the quantity for that item from my stock. so I can save time filling in two forms all the time.THX hope you understand that and hope someone can help.

All Answers

I think you need a VBA program, not a formula in a cell

Use a program to manage a transaction entry window, to both post new entries to the transaction record, and to decrement your stock list. It sounds like you want to maintain two worksheets, one a transaction register and the other a stock list, and have the simple act of entering a new row of transaction data perform an update on the inventory. I don't see how that is going to work.

While you're waiting for Delbert & his VBA developer friend...

Copy & paste the picking list and the stock list into different worksheets in the same workbook. If you're fully familiar with the distinction, I apologize for the previous italics, but many people are not, and the distinction usually needs to be emphasized, in my experience.

Now, you can use a vlookup function in the stock list worksheet to copy the quantity from the picklist worksheet, if and only if the code matches. The function in the stock list worksheet would look like =vlookup(picklist!$a1:$b65536,2,false), assuming that the code is in column a & the quantity is in column b of a worksheet called 'picklist'.

The next step is to use the result of that function, and subtract from the current number in stock. After that, the next step is to start using MS Access & treat these picking lists as link tables, with the functions described above replaced by queries.

Start or search

Create a new discussion

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

Post type

Subject title

Topic Tags

Select up to 3 tags (1 tag required)

Cloud

Piracy

Security

Apple

Microsoft

IT Employment

Google

Open Source

Mobility

Social Enterprise

Community

Smartphones

Operating Systems

Windows

Mac

Malware

Tablets

Networking

Browser

Hardware

Software

Web Developerment

Linux

Off Topic

Message Body

Track this discussion and email me when there are updates

Please note: Do not post advertisements, offensive material, profanity, or personal attacks. Please remember to be considerate of other members. If you're new to the TechRepublic Forums, please read our TechRepublic Forums FAQ. All submitted content is subject to our Terms Of Use.