Getting active/selected cell in to a reference cell

[Tipx76]

Hi.
In my work I work a lot whit codes, Sometimes in multiple columns. but it’s hard to always know the code description.

To get a clean excel sheet, I’d like to select a cell whit arrow keys ore mouse in these columns of codes and that the selected value is always displayed in a reference cell in the top.

The Idea is to have that cell as a value for a Vlookup to display the description of the current selected code.

Let’s say that reference cell is E2 and the V lookup is found in F2, As I change the selected cell whit mouse or arrows E2 is changing to the current value and is the search value for F2.

Can this be created whit excel functions or is a VB script needed. If so how do I do it?
This should be a quite easy task but no one I’ve talked to have a solution. hoping for a better help in this forum.


Br Fredrik

 

[MakinBacon91]

Let me see if I understand what your saying.

Basically you want to be able to select (click or arrow to) any cell on your spreadsheet, and in another cell, have the formula of that cell as the search criteria for Vlookup?

In other words you want a cell with the formula: "=Vlookup(selected cell formula, Range, Column Index, True/False)"


If that is what you are looking for I think it can be achieved with VBA and look "clean" as you say.
Let me know if I understood correctly, and I can help with the code.

 

[Tipx76]

Let me see if I understand what your saying.

Basically you want to be able to select (click or arrow to) any cell on your spreadsheet, and in another cell, have the formula of that cell as the search criteria for Vlookup?

In other words you want a cell with the formula: "=Vlookup(selected cell formula, Range, Column Index, True/False)"


If that is what you are looking for I think it can be achieved with VBA and look "clean" as you say.
Let me know if I understood correctly, and I can help with the code.

Basically id like the value in one cell and then use that to feed a vlook up in a other cell nex to it.
Just to make it Easy and easy to use. Even if combining the two might be a bit cleaner.
E1 = Selected cell and in F1 have the Vlookup(E1,range,column index,false)

Br Fredrik

 

[MakinBacon91]

Basically id like the value in one cell and then use that to feed a vlook up in a other cell nex to it.
Just to make it Easy and easy to use. Even if combining the two might be a bit cleaner.
E1 = Selected cell and in F1 have the Vlookup(E1,range,column index,false)

Br Fredrik

Okay, I am feeling kind of thick for not understanding yet, but let me see if we are close with an example.


The formula I have in cell F1 is =VLOOKUP(E1,A1:B9,2,FALSE)
And here is what I get:

[TABLE="width: 490"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Horse[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Cat[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Dog[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Fly[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Cat[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Pig[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Bear[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Wolf[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Fish[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Human[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Is this essentially what you are looking for?

Or,

Do you want to be able to click on the cell containing Wolf, have Wolf show up in Cell E1, and VLookup Return 7

 

[Tipx76]

I tryde to make a exampel.
https://drive.google.com/file/d/0BzhS9Mi-C4CSdTZmaUJWRzlweDQ/view
as i move the active cell around in sheet 1 F2 is changing.
so if the actrive cell have no value, F2 is blank, if im like in pictuer have selected C7 value from C7 is found in F2.
Vlookup in G2 uses F2 as search value.
If active cell is change then the result is changes in F2 and G2 makes a new look up.

Br Fredrik