Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

Codman

Technical User
Nov 25, 2003
44
GB
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


Cell_reference_eykftr.png
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
 
Thanks Skip,

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

Many thank for you advice.

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

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

Part and Inventory Search

Sponsor

Back
Top