Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Simple Stock Control Sheet
Im Trying To Write A Simple Sheet That Basicly Lists All The Components In Stock With There Quantities, And Then Have A Cell Dedicated To Selecting A Component And Entering A Quantity That Has Been Removed From The Total Individual Amount.i.e B1=(item)msc213 C1=(qty)23 G1=(item Selector, Select Say)msc213 H1=(qty Removed From Stores)2 That Would Then Subtract The Amount From The Right Cell 2-23=21. As Im A Beginnner Any Help With The Formula Would Be Great!!! Many Thanks -- alanled ------------------------------------------------------------------------ alanled's Profile: http://www.excelforum.com/member.php...o&userid=30949 View this thread: http://www.excelforum.com/showthread...hreadid=510993 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Simple Stock Control Sheet
Hi Alan,
I use Excel 2003. I'm not sure if I'm truly clear on what you're seeking to do with your spreadsheet, but here's my understanding (for what it's worth! LOL). I'm guessing that you'd like to be able to enter a code such as "msc213" into a cell such as G2, and a number like "2" into a cell such as H2, which would always deduct the stock sold from the appropriate category of each of the identified inventories automatically. I'm not an Excel whiz by any means, but if my understanding is correct about what you'd like to do, here's how I might approach that challenge in Excel 2003 (although my method may not be practical and I'm sure there are a lot of experts here who can help you in a much better way). There may even be some software programs that can work around this problem in a much simpler way. Let's say you've got all your inventory items described in column B...using 3 different names: "msc213" (B3), "xyz214"(B4) and "nnn204"(B5), and they're listed down column B in those rows. (I put a blank row B2 between the title in B1 and the first data input in row B3). In your next column "C" you've got the original quantities for each. Again C3, C4 and C5 that correspond with those rows in the B column. Let's say you want to create a column "D" which represents the "final" total in each category of inventory...again...D3, D4, D5. (All the rows correspond with one another). I'm understanding that you may want to be able to update the totals in each of these cells in column "D" just by typing a simple name into cell "G2" and a number into cell "H2" which would make a deduction to the corresponding total in the "D" (total) column of each category. In this case, I think that cells "G2" and H2" would need to be constants (identified by a $ sign), and that each of your rows of inventory could only reflect the latest changes you made to them. (Again, remember that I am just a novice myself, and really can't guarantee this would work...but heck, it may be worth a try, right?) :) Okay so here's what forumula I would place in cell D3 for example: =IF($G2="msc213",C3-$H2,IF($G2="xyz214",D3,IF($G2="nnn204",D3))) In cell D4: =IF($G2="xyz214",C4-$H2,IF($G2="msc213",D4,IF($G2="nnn204",D4))) In cell D5: =IF($G2="nnn204",C5-$H2,IF($G2="msc213",D5,IF($G2="xyz214",D5,IF($G2=" nnn204",D5)))) Basically I guess what I was thinking is that if you want to insert an indentifying name into cell G2 and a quantity into cell H2 and have it reflect the changes to different rows at any given time, for each of the rows, you need to let that particular cell "D" know the difference between leaving it alone or updating it. I'm sure this is a dinosaur's way of doing things, but not being an Excel expert, I can't think of any other workaround! Hopefully someone will be along soon to resolve your issue in a much easier way. Best regards and good luck! Karen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I'm creating a simple spread sheet | Excel Discussion (Misc queries) | |||
stock control system | Charts and Charting in Excel | |||
Copying multiple sheets from one book 2 another and undertake spec | Excel Discussion (Misc queries) | |||
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) | |||
Simple Stock Watchlist Chart | Charts and Charting in Excel |