# Creating a basic stock inventory sheet

1. ## Creating a basic stock inventory sheet

Hi All,

I am very much a novice on excel so please bear with me!
I am trying to create a very basic spreadsheet that I can use to keep track of stock items. What I want to do is to be able to update a particular stocks total when either stock is added or stock is taken. I am not very familiar with formulas, but what I want is when I add or remove stock the total updates accordingly, then if I need to update the same stock again, I can do so with the newly updated total now being the source that is updated - I hope that this makes sense?

Hope someone can help.

Best regards

2. ## Re: Creating a basic stock inventory sheet

Hi and welcome to the forum

There are a number of different ways to do this, but seeing as you have not provided a sample to work from, see if this will get you heades in the right direction...

 B C D E 1 Code In Out Balance 2 aa 20 10 3 bb 200 100 4 cc 2000 1000 5 aa 11 9 6 bb 111 89 7 cc 1111 889 8 aa 22 31 9 bb 2222 2311 10 cc 3333 4222 11 aa 44 -13 12 bb 444 1867 13 cc 4444 -222

E2=SUMIF(\$B\$2:B2,B2,\$C\$2:C2)-SUMIF(\$B\$2:B2,B2,\$D\$2:D2) copied down

3. ## Re: Creating a basic stock inventory sheet

Ford,

Many thanks for your quick response - very much appreciated.
If I do not want to have additional lines each time stock is updated can that be achieved?

For example, if I only have 5 Stock Items;

Stock A
Stock B
Stock C
Stock D
Stock E

Each time Stock A is updated instead of creating a new entry can the first/original entry for Stock A only be updated?

Best Regards

4. ## Re: Creating a basic stock inventory sheet

Not without VBA. A cell can contain either a value or a formula, not both. Besides, if you keep track of your stock the way I showed, you could maybe do analysis on which 1 sells more, which to focus on, etc

5. ## Re: Creating a basic stock inventory sheet

update your new item details in master sheet, enter your receiving & delivery in data sheet then refresh your sheet to update pivottable.

thanks

6. ## Re: Creating a basic stock inventory sheet

Ok, many thanks for your help.

Best

7. ## Re: Creating a basic stock inventory sheet

This is great- many thanks, very much appreciated.

Best regards

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1