• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to return a certain cell value (Ax) from the active row on another sheet

N

nico

New Member
Hi everyone,


My situation is this:

---------------------

worksheet1 I have rows with data and column 1 contains a reference number.

worksheet2 I have a cel in where you can fill in a reference number from the other worksheet. If you do that than I retrieve the other values from the data row by doing a vertical or horizontal lookup. I place the retrieved values in a certain markup on worksheet2 and than you can use this to print a list of values from worksheet1.


What I would like to do is:

---------------------------

When you have a certain row selected on worksheet1, then automatically copy/fill the reference number on worksheet2 with the value of column1 of that row so people do not have to remember the value from worksheet1.


If this is possible than I can make a button on worksheet1 to print worksheet2 from worksheet1 with the right values.


That would make me very happy :)


Any other tips / solutions are more than welcome.


I allready did some searching and found this posting :

http://chandoo.org/forums/topic/how-to-refer-current-active-cells-address

which looks like something that is usefull.


But unfortunately I can't get it to work.
 
whoops

This is better:

=INDEX($A$2:$A$36,SUMPRODUCT(($E$2>=$B$2:$B$36)*($E$2<=$C$2:$C$36)*ROW(A2:A36))-1)
 
This might work. Allows user to double click on a cell in col A of worksheet 1, and it will go to worksheet 2. To install, right click on worksheet1 tab, view code, paste this in:

Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

'Give the name of worksheet 2 and cell to use

Worksheets("Sheet2").Range("A2") = Target.Value


'This line cancels out the double click.

Cancel = True

End Sub
 
Hi Luke M,


Thanks you very much for your response, I got it to work!


A question about the cell that the value is copied to: can you also copy it in 2 different cells (I made another worksheet with a different print layout that would benefit from this action (if possible).


If it is nog possible, than I'm still happy :)
 
Sure, just add additional lines of code like this:

Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

'Give the name of worksheet 2 and cell to use

Worksheets("Sheet2").Range("A2") = Target.Value


'More lines as needed

Worksheets("Sheet2").Range("B2") = Target.Value

Worksheets("Sheet3").Range("A2") = Target.Value

Worksheets("Sheet3").Range("C2") = Target.Value

'etc, etc, etc


'This line cancels out the double click.

Cancel = True

End Sub
 
You must log in or register to reply here.
Back
Top