MrExcel Publishing
Your One Stop for Excel Tips & Solutions

simple?


Posted by Osman Pero on September 22, 2001 2:49 AM

hello there,
following problem, i want to have variables like if i type in one cell number 8, that it takes values from f.e. A8, or when i type 17, it should takes values from A17. I already tried with =$A$A25 (A25 is cell where i want to write values) and many other combos but i did not get it. Think you could help?
Thx in advance


Posted by Aladin Akyurek on September 22, 2001 3:17 AM

Osman,

You can't type a number into a cell that already contains a formula. New entry will wipe out the formula.

You need a task-specific VBA code to do what you want.

A formula-based solution would require additional cells.

Suppose you want B1 to take the value of A8.

In B1 enter: =INDIRECT(address(C1,1))
In C1 enter: = 8

Aladin

=======

Posted by Ivan F Moala on September 22, 2001 3:22 AM

Have a look @ index function eg
assuming value inputed in C1 then
=INDEX(A:A,C1) gives you the value in A C1 value

Have a look @ online help for info


Ivan

Posted by Jay on September 22, 2001 3:24 AM

Assuming the data is in column "A" and the row number is in cell C1, try the following;

=INDIRECT("A"&C1)

If C1 contails the number 4 and cell A4 contains the number 217, then whatever cell has the above equation will equal 217.

Jay

Posted by Osman Pero on September 22, 2001 3:26 AM

i don't want to wipe formula, i want to enter number in cell, number which will some other cell use as part of formula for taking values like this --- = ($A[and here number i wrote in another cell]), means if i write 5, it takes value from A5 and if i write 6 it takes vakue from A6, the cell where i write numbers has no formula

Posted by Aladin Akyurek on September 22, 2001 3:35 AM

Still unsure about what you want, but try out INDIRECT, or Ivan's INDEX approach. Jay's reply is the same as mine.

Aladin

Posted by Osman Pero on September 22, 2001 3:36 AM


that was what i was looking for, thank you very much