Excel list and table formulas

  • Thread starter Thread starter IgorM
  • Start date Start date
I

IgorM

Hi
Two questions
1.
Is it possible to create a list in cell (using data validation menu) from
values in table (the new 2007 table). Lets say the table is named
'price_list' and the column which contains the values that I want to return
in the list in a specific cell has a header called 'items'.
2.
How to use these formulas (table formulas) in functions like HLOOKUP.

Kind regards
Igor
 
Starting in E1 enter this data
fruit price
apple 1.25
banana 2.45
orange 4.68
pear 2.05
plum 3.15

Select E1:F6 ; open the Insert tab; on the far left in the Tables group,
clcik onTables
Now the range E1:F6 will be formatted with (generally) blue bands and will
be Table1

Click on A1; on the Data tab locate Validation, in the dialog in the Allow
box specify "List" and in the Source use the mouse to select E2:E6 (not F6)
Test the validation - click A1 and slect an item from the dropdown list
Now add more data to E7:F7 (grapes, 4.25) and not the the Validation
dropdown includes the new grapes

In B1 enter =VLOOKUP(A1,Table1,2,FASLE) and note how this picks up the
price of the item
best wishes
 
Thanks very much. Just what I wanted to know.

Bernard Liengme said:
Starting in E1 enter this data
fruit price
apple 1.25
banana 2.45
orange 4.68
pear 2.05
plum 3.15

Select E1:F6 ; open the Insert tab; on the far left in the Tables group,
clcik onTables
Now the range E1:F6 will be formatted with (generally) blue bands and will
be Table1

Click on A1; on the Data tab locate Validation, in the dialog in the Allow
box specify "List" and in the Source use the mouse to select E2:E6 (not
F6)
Test the validation - click A1 and slect an item from the dropdown list
Now add more data to E7:F7 (grapes, 4.25) and not the the Validation
dropdown includes the new grapes

In B1 enter =VLOOKUP(A1,Table1,2,FASLE) and note how this picks up the
price of the item
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email
Click to expand...
 
One more thing. What if the source table (the source for validation range)
is in another sheet - same workbook.
 
Back
Top