-1

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.

1 Answer 1

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.

2
  • I thought it could be done without macros, maybe with Vlookup. Nov 12, 2012 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, 2012 at 14:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .