Data validation and drop-down lists

[Guest]

Hi all
Background: I'm setting up an inventory that records the usage of corporate
gifts. I've set up 2 worksheets. These are a 'Source' worksheet containing
lists of gifts, gift values and staff names; and a 'Record Usage' worksheet
where staff record their usage of gifts. 'Record Usage' column headers are
Month, Gift Item, Gift Value, Qty taken, Staff Name, Date, Description of
Purpose, Country of recipient. Gift Item and Staff Name can be selected from
drop-down lists (linked to 'source' lists using data validation). I want the
corresponding Gift Value to populate when 'Gift Item' is selected.

Question: How do I get 'Gift Value' in the 'Record Usage' worksheet to
automatically populate when a 'Gift Item' is selected? They need to be in
separate columns because I am then creating a pivot table from the data.

 

[Guest]

Try using VLOOKUP to get the data on the Record Usage worksheet from the
Source worksheet.

 

[Gord Dibben]

VLOOKUP formulas will pull the data to appropriate cells.

See Debra Dalgleish's site for more on VLOOKUP and Data
Validation lists for entering the choices.

http://www.contextures.on.ca/xlFunctions02.html

http://www.contextures.on.ca/xlDataVal01.html

Note the section on using DV lists from another worksheet by naming the list.


Gord Dibben MS Excel MVP