In Stock Lookup Question

**PhilWalton** · 02-27-2011, 10:37 AM

Hi there

I am wanting to know if the following is possible within Excel, and if so which formula/command I should be looking at using. I have only basic skills here so your help would be greatly appreciated.

I have 2 files.

The first one is a stock inventory and has just 2 columns - 1 entitled Style, the other called stock status. The style is basically a 5 digit unique code number and the second column simply has the words IN STOCK or OUT OF STOCK next to the style number.

The second file is a product list also contains a column labelled Style containing the same numbers in the inventory list. It also contains other information such product name etc.

Ideally, when I open up both files I want all style numbers that are shown as IN STOCK in the inventory file to automatically get highlighted in the product list file. This way I can easily see wwhich items are in stock.

Can this be done and if so some guidance would be greatly appreciated.

Many thanks
Phil

**MarvinP** · 02-27-2011, 11:48 AM

Hi Phil and welcome to the forum.

Yes this can be done. Put the two attached files on your desktop. Open book1 and see the In/Out status. I have a named range in book1 to make the formula in book2 easier. Then Open book2 and see the formual in column C. When you open the formulas it will ask to find Book1, you need to point to it. OK?

**PhilWalton** · 02-27-2011, 06:12 PM

Hi

Thanks for the reply - it is really appreciated.

I've added some extra lines into Book1 and extended the formula down, however the results do not seem to match correctly.

For example no 654 is showing as 'In' in Book1 and Out in book2.

Something I didn't explain before was that the Style/code numbers may be on different lines within the spreadsheet in each of the 2 spreadsheets. eg Style 20111 maybe on line 10 in the inventory but line 50 in the product sheet.

Your guidance would be appreciated.
Thanks
Phil