Automatically Fill In Pirces

D

Denise

I am wanting to set up a spread sheet to track charges that are lost monthly. I will be entering a product number from charge stickers that are found in the trash, floor, etc.

Is there a way to set this up so that when I enter a number it can pull the item and price from a "master" list?

Thank you.
 
A

Arvi Laanemets

Hi

MasterSheet: ProductCode, ProductName, ProductPrice (headers in row 1)

Define named ranges
Code=OFFSET(ProductCode!$A$2,,,COUNTIF(ProductCode!$A:$A,"<>")-1,1)
MasterTable=OFFSET(ProductCode!$A$2,,,COUNTIF(ProductCode!$A:$A,"<>")-1,3)

On your tracking sheet, select a range p.e. in column A, where you want to
enter/select product numbers. From menu select Data.Validation and allow
List with source:
=Code
Into some cell in next column (B2) enter the formula
=IF(ISERROR(VLOOKUP($A2,MasterTable,2,0)),"",VLOOKUP($A2,MasterTable,2,0))
and into next column (C2) the formula
=IF(ISERROR(VLOOKUP($A2,MasterTable,3,0)),"",VLOOKUP($A2,MasterTable,3,0))
and copy formulas in both cells up/down for as much rows as you need.

It's all.

--
Arvi Laanemets
(Don't use my reply address - it's spam-trap)



Denise said:
I am wanting to set up a spread sheet to track charges that are lost
Click to expand...
monthly. I will be entering a product number from charge stickers that are
found in the trash, floor, etc.
Is there a way to set this up so that when I enter a number it can pull
Click to expand...
the item and price from a "master" list?
 
Top