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.

Hi Harish,

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
  A     B
 CodeNo StockQty
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.

About Excel
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


All Answers

Answers by Expert:

Ask Experts




Can answer basic worksheet related questions like formulae usage, connecting to RDBMS from server to extract rows from tables, basic macro construction, pivot table creation, dynamic updation of worksheet data from Server RDBMS..


3 years

©2016 About.com. All rights reserved.