Reverse Two way Lookup

[kuldeepjainesl]

I want to get a value updated in a cell that is intersection of two coloum headings. Writing will required a lot of word this please refer the file.

Reverse Lookup :

1) On Selection in the range B3:AC10.

2) User will click on any Cell for example : L8

3) Now this gives the Row and Coloum Heading as Apr-11 and F

4) Now Go to Sheet 2 and Find the Row and coloum

5) A12 and G1 are the targent row and coloum

6) Find the intercect and got G12

7) Please this text from Sheet 2 G12 to J16 on this sheet

Regards,
Kuldeep

 

[NARAYANK991]

Hi Kuldeep ,

See if this is acceptable.

Narayan

 

[kuldeepjainesl]

Great...That what i imagined to get.... Thanks a lot Narayan

You may please explain the two VBA lines which update the row and coloum header. Rest is understood.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
            If Application.Intersect(Target, Range("B3:AC10")) Is Nothing Then Exit Sub
            [SelectedCellRowHeader] = Me.Cells(Target.Row, 1)
            [SelectedCellColumnHeader] = Me.Cells(2, Target.Column)
           
           
End Sub

 

[NARAYANK991]

Hi ,

The code is merely storing the row header and column header corresponding to the active cell.

The Worksheet_SelectionChange macro is an event macro which is triggered when ever the cursor is moved and the active cell is changed. To Take your example , suppose the cursor is placed in cell L8 ; the two named ranges will then contain F and April-2011 , where F is the row header , since it is the contents of cell A8 , and April-2011 is the column header , since it is the contents of cell L2.

Since these two crucial items are now available within the worksheet , the cell J16 merely needs to have a formula using INDEX and MATCH , to retrieve the data from Sheet2.

Narayan