Excel help, i require good pastelink macro suggestion?

Question

in excel i have many sheets in my file and first one is INDEX and in index i fill what i want to fill in all other sheets in one perticuler column.

so in the INDEX sheet in first column i will give sheet name and in second column i will give column name and in third column i will give row number and in fourth column i will give some alphanumeric data i want to pastelink that data in that perticuler sheet number, row number and column number written previosly.

what formula will work (if then, goto, pastelink)?

i want preset formula, i don't want to pastelink every time, it should understand and pastelink everytime automatically.

Answer 1

Go to Tools -> Macro -> Visual Basic Editor

Under Microsoft Excel Objects goto your "Index" sheet and paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim TargetRow As Integer

Dim TargetCol As Integer

Dim ws As Worksheet

TargetCol = Target.Column

If TargetCol = 4 Then

    Temp = Target.Value

    TargetRow = Target.Row

    SheetName = Cells(TargetRow, TargetCol - 3).Value

    ColName = Cells(TargetRow, TargetCol - 2).Value

    RowName = Cells(TargetRow, TargetCol - 1).Value

    Sheets(SheetName).Cells(RowName, ColName).Value = Temp

End If

End Sub

While writing this code I assumed that In your INDEX sheet, the sheet name comes under col A, Col Name in Col B, Row in Col C and value/data in col D.