Finding table header and row value from a cell within a table

[Codman]

I have three tables in an Excel worksheet and I wish to return the value of a selected cell. In this example if 5 selected in the top table I want to return the column and row reference in two other cells in the sheet. I have tried Index and MAX formula, but cannot seems to achieve this. Am I asking too much and this then a VBA solution. Any help appreciated

 

[SkipVought]

Hi,

A picture is pretty, but pretty useless if you expect someone to replicate it in order to figure out a solution for you.

First, there's no facility in native Excel spreadsheet functions to detect a Selection. There are, in VBA, means for detecting various Events like the Worksheet_SelectionChange event. If you want a VBA solution, please post your question in Forum707. Once you can detect a change in Selection, determine the value in the column of the first row of the CurrentRegion or the value in the row of the first column of the CurrentRegion is trivial.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Codman]

Thanks Skip,

I'll use VBA, but I wasn't too sure if a formula would suffice.

Many thank for you advice.

Codman

 

[SkipVought]

A function/formula can't put a value in a remote cell.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein