Help Linking Data

  • Thread starter Thread starter tractor
  • Start date Start date
T

tractor

I have a spreadsheet with Items , Descriptions , and prices in it.
want to have another sheet that when I enter the item number it wil
fill in the rest for me. This will let me create a customer's orde
fast and give a total very fast also.
Can this be done? If not are there any ideas I plan on using this on
pocket PC
 
Hi

With Item table (Item, Description, Price) on sheet Items, on Orders sheet:
Items!A1:C1 contains headers, let's the Items table be p.e. in range
Items!A1:C100
Orders!A1:C1 contains headers, Orders!A:A contains item numbers
Orders!B2=IF(ISERROR(VLOOKUP($A2,Items!$A$2:$C$100,2,FALSE())),"",VLOOKUP($A
2,Items!$A$2:$C$100,2,FALSE()))
Orders!C2=IF(ISERROR(VLOOKUP($A2,Items!$A$2:$C$100,3,FALSE())),"",VLOOKUP($A
2,Items!$A$2:$C$100,2,FALSE()))
and copy both formulas down

You can enchance this, using named ranges
Create named ranges
Items=OFFSET(Items!$A$2,,,COUNTIF(Items!$A:$A,"<>")-1,1)
ItemsTable=OFFSET(Items!$A$2,,,COUNTIF(Items!$A:$A,"<>")-1,3)
On sheet Orders, format a range in column A using Data.Validation.List with
Source=Items - you get a drop-down in every formatted cell to select item
from Items table, and no entries not present in Items table are allowed.
Orders!B2=IF(ISERROR(VLOOKUP($A2,ItemsTable,2,FALSE())),"",VLOOKUP($A2,Items
Table,2,FALSE()))
Orders!C2=IF(ISERROR(VLOOKUP($A2,ItemsTable,3,FALSE())),"",VLOOKUP($A2,Items
Table,2,FALSE()))
 
Back
Top