i have two excel sheet in first sheet(central ware house stock position) A column (code no)B column stock qty.
seccond sheet (branch stock position)A column (code no)B column stock qty.
i have task daily morning i have to compare branch stock position with central warehouse stock position ,to know which item is short in branch.
at present i am printing both sheet and comapare manullay . i want to help from you, how to i know which item is out stock in branch pls provide any formula.
This would not be a simple task that I can tell over an email. I can provide a sample guideline. You would need to build up further on that.
Suppose the first sheet(sheeta) has the following layout
A B C
CodeNo Stockqty BranchQty
1 aaa 5
2 bbb 10
The second sheet (sheetb) contains
1 aaa 2
2 bbb 5
I guess you would want excel to warn you when the stockqty goes below a certain qty in the branch
In sheeta Branchqty column, you can have this formula(refer to help for the exact formula) =vlookup(a1,sheetb!$a1..$b2, 2,false). This will give you the value of stockqty for aaa in sheetb. You can then use the conditional formatting to show that value in red if it goes below a certain value.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here