Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

There are for ex. 10 rows in one worksheet. When user clicks on one row it should be presented on another worksheet. Is it possible? Any help to do it?

EDIT: To clarify: In one sheet are presented for example student exam marks on first year: John 10 8 10 7 Nick 8 9 8 9 Maria 7 8 8 7

On 2nd sheet there are student informations on the second year: John 9 9 10 8 Nick 8 8 9 7 Maria 7 6 8 8

I want to have give some kind of final certificate for student so summary information should be presented on the third sheet. I doesn't need to be on click. There could be drop down list on the third sheet.

share|improve this question

1 Answer 1

This would be a quick macro to do so:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  With Sheet2.UsedRange
    'note, this won't copy to row 1, leaving headers intact.
    Target.EntireRow.Copy (.Rows(.Rows.Count + 1).EntireRow)
  End With
End Sub

However, using it with selection change, can be quite annoying, because it will be executed every time you select another cell - so you might want to think about using some sort of backchecking or another way to execute this.

Maybe just use the code as a custom macro and assign a shortkey.

Edit

When you want to do this only with formulas, you'll have to setup each cell of your destination row with a formula. I know of no way to change a cell directly by using a formula, except of course the cell with the formula.

My suggestion then would be, to use eigther named-ranges, VLOOKUP or MATCH.

A named-range might get quite close to what you stated:

On destination-sheet:

A1=14

A2=MYRANGE
B2=MYRANGE
.
.
.
IV2=MYRANGE

MYRANGE is defined as

=INDIRECT("SourceSheet!"&DestSheet!$A$1&":"&DestSheet!$A$1)

This would then give you row 14 of the destination sheet. This will also work, if A1 is on the SourceSheet , just edit it accordingly.

Just as easily you could use two named-ranges:

MYDESTRANGE=SourceSheet!MYSOURCERANGE

By changing MYSOURCERANGE to another row, you would then effectivly change the source of MYDESTRANGE, while using the same row as before.

share|improve this answer
    
I thought it could be done without macros, maybe with Vlookup. –  Иван Бишевац Nov 12 '12 at 14:27
    
"when user clicks on one row" -> this is why I assumed you needed a macro. You will need to change this requirement, if you want to avoid using VBA. To be more specific, my interpretation was: user clicks on one of 10 rows, this row gets copied to another worksheet. This is only possible with VBA. –  Jook Nov 12 '12 at 14:28
    
I offered two non-VBA solutions while still not clear about requirements (which I agree might well have been for code, though not tagged that way) and a further two solutions (including VLOOKUP) after OP's slight clarification - but none suited, and no comment why not. (A rather bulky three screenfuls, including eight images so since deleted.) Maybe OP will come across someone better at guessing? –  pnuts Jan 8 '13 at 22:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.