1

Topic: To an ex-fur-tree I write the formula, I want, that instead of values enclosed in the formula were substituted lines from a column.

The task - to receive  number from 1 to 5 (Sheet 1) and nearby in a cell to allocate the text corresponding to this number.
Solved simply: cell A1 = (1; 5)
In an adjacent cell the word or text expression from the list on adjacent  (Sheet 2) should be displayed vegetables, for example, are enumerated,
A1 B1
1 water-melon
2 melon
3 peach
4 plums
5 potato
Now I solve a trace in the image in B1 I write the formula:
=IF ( (REVIEW (A1; {1:2:3:4:5})); ""; REVIEW (A1; {1:2:3:4:5}; {"water-melon": "melon": "peach": "plum": "potato"}))
But the text amount grows permanently and it would be desirable, that replenishing the list on "sheet 2" and minimum changing the formula to "sheet 1". Instead of a word the potato can be the whole sentence silt and two. To inscribe them in the type formula "= ( (REVIEW (A1; {1:2:3:4:5}));" ";..... Not conveniently. Tried to interpose instead of a word"Water-melon"a type cell reference" $ =2! B1$ ", but it did not work, producing as a result at drop - out 1 in A1 value B2 there was a value  formulas" =2! B1 ".
How instead of a word "Water-melon" correctly to interpose a cell address with text value from adjacent sheet?
Or there are different ways?
Adding from 12/19/2016 10:50:
Added a macro:
Sub Knopka1_Shchelchok ()
1.Calculate
End Sub
Private Sub Workbook_Open ()
ActiveSheet. EnableCalculation = False
End Sub

2

Re: To an ex-fur-tree I write the formula, I want, that instead of values enclosed in the formula were substituted lines from a column.

xotelok
How instead of a word "Water-melon" correctly to interpose a cell address with text value from adjacent sheet?
In the English version of Office it vlookup,  as it translated in the Russian

3

Re: To an ex-fur-tree I write the formula, I want, that instead of values enclosed in the formula were substituted lines from a column.

.