Return cell value from an active row to a cell on same sheet

[rogcoy]

XXXXXXX


year Period Week Widgets Finished

2011 1 1 1374932 1404454

2011 1 2 1682296 1721592

2011 1 3 1756761 1833206

2011 1 4 1572590 1612353

2011 2 5 1612296 1687144

2011 2 6 1457374 1571107

2011 2 7 1498288 1565851


I want the Period to show up in the XXXXXX cell no matter where I am in the active row. So if I click on the last number on the right, I want 2 to show up on top.

 

[keymaster]

Hi Rogcoy,


You can use Worksheet_SelectionChange event to handle this.


First select your entire Year...Finished table and give it a name like myList.


Then right click on the sheet, view code.

Now select Worksheet from drop down and VBE will create selectionchange event by default.


Then use this code inside

[pre]

if not application.intersect(activecell,range("myList")) is nothing then
range("A1").value = range("myList").cells(activecell.row - range("myList").cells(1,1).row + 1, 2).value

end if

[/pre]

For more on this technique, see http://chandoo.org/forums/tags/worksheet_selectionchange