Excel list and table formulas

[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

 

[Bernard Liengme]

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

 

[IgorM]

Thanks very much. Just what I wanted to know.

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

 

[IgorM]

One more thing. What if the source table (the source for validation range)
is in another sheet - same workbook.