How to set selected value to a particular cell in a given range of values

[Praneeth]

Dear All,

Please let me know how to set a selected value to appear in a particular cell from a given range of values.

Practical background,

......A .....B ..C
1 12001 AB ???
2 12002 PC
3 12003 KC
4 12005 DD
5 12035 AB
6 12045 MD
7
.
.
.
.
150

My data range is Column A, up to 150 rows (A1:A150).
I want to get the selected cell value from that range to appear in C1 Cell and hold until I Click another cell in that range.
Meanwhile I want to access the other cells as well without interrupting to the C1 value; since the Value appeared in C1 is being used to the other cells to link some other sheets.

Please suggest me a proper solution for this purpose.

Thank you.

 

[Sammi]

If you access the other cells then wouldn't you be selecting them instead?

 

[Praneeth]

Dear Sammi,

Actually, I intend to use the selected value(from A1:A150) in hyperlinks in some other cells. Once i select a value from that range the "C1" should not be changed until I select another value from (A1:A150).

 

[Sammi]

So that you can select cells not in that range. I think it might be easier to have two cells. One where you enter the cell you want to 'select' and another where you use the indirect formula to return the value of the 'selected' cell.

For example in cell C1 you could type A3

Then in cell C2 you could type =INDIRECT(C1) this would return 12003 in cell C2.

Does that help?

 

[Praneeth]

give me a moment..

 

[Sammi]

Not sure I understand here. If you changed around the steps I mentioned above - then the value (12003) would be shown in C1 and C2 would be where you type the reference to the cell you want to 'select.

So you could type A3 into C2 then in cell C1 you could type =INDIRECT(C2) this would return 12003 in cell C1.

You could then use the 12003 in C1 in the hyperlinks as you wanted by adding C1 as part of the link location =HYPERLINK(....&C1&.....)

The value in C1 wouldn't change until you changed the referenced cell in C2 to something else.

 

[Praneeth]

Dear Sammi,

It is not working,
It should appear any value(only when click on a cell in the given range), in the cell "C1".

can you brief me if the last suggested solution is working at your end?

 

[Sammi]

Without writing a macro (and I'm not sure that a macro would work for this), I don't think what you're suggesting is possible. Instead if you type the name of the cell you want to select into C2 and =INDIRECT(C2) into cell C1 it will work as shown. You can then use the formula =HYPERLINK(.....&C1) to include that value into your hyperlink. Each time you update C2 the value in C1 will change. E.G. If you change C2 to A5 then C1 will change to 12035.