If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Automatically Fill In Pirces
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. |
#2
|
|||
|
|||
Automatically Fill In Pirces
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(Prod uctCode!$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)),"",VLOOK UP($A2,MasterTable,2,0)) and into next column (C2) the formula =IF(ISERROR(VLOOKUP($A2,MasterTable,3,0)),"",VLOOK UP($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" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Object defaults (e.g., default fill color) | Jeff | Powerpoint | 2 | June 5th, 2004 03:56 AM |
automatically fill colour | Nelson | Worksheet Functions | 1 | February 10th, 2004 06:02 PM |
Fill down automatically (problem) 3rd posting | Lorrie | Worksheet Functions | 4 | January 6th, 2004 10:34 PM |
Copy and fill automatically | Daniel | Worksheet Functions | 3 | October 13th, 2003 06:25 PM |