Hi,

I am facing some problem in regards to formula.

I have two sheets

1. Schedule
2. Input

In Shedule Sheet I have four columns:

Ref No Project Dwg. Unit Qty
01 KEVENTER KOLKATA PC-7001-3 6
02 CIPLA LTD, BADDI 7049-1A 1
03 CIPLA LTD, BADDI 7049-2A 1
04 CIPLA LTD, BADDI 7049-3A 1
05 CIPLA LTD, BADDI 7049-5A 1
06 CIPLA LTD, BADDI 7049-9A 1

the data of Schedule Work is entered in the this sheet.

Now In put sheet we have below mentioned columns:

1. Drg No
2. Project Name
3. FH
4. FL
5. RD
6. FPI
7. QTY
8. Ref No

Now I want a macro through which when ever i enter Ref No in the Input
sheet it should pick the value of Drg No/Project Name/Qty: from
Shedule sheet and paste it autometically in the Input sheet so that
the user had not to copy it again and again.

Right now i am using the belwo mentioned Formula...
=IF(TRIM(H5)="","",LOOKUP(H5,SCHEDULE!A3:A5000,SCH EDULE!C3:C5000))

I want a macro becoz the formula gets deleted by the users by mistake.
Then i had to sit to correct the Format everytime.

Awaiting for your help.

Thanks in Advance.

Regards

Akash

Put the following in the Input sheet's code module.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bFound As Boolean
Dim iEnd As Integer
Dim c As Range
Dim rng As Range

If Target.Column = 8 Then
If Target = "" Then
Target.Offset(0, -7) = ""
Target.Offset(0, -6) = ""
Target.Offset(0, -1) = ""
Else
iEnd = Sheets("Schedule").Range("A2").End(xlDown).Row
Set rng = Sheets("Schedule").Range("A2:A" & iEnd)
For Each c In rng
If Target = c Then
Target.Offset(0, -7) = c.Offset(0, 2)
Target.Offset(0, -6) = c.Offset(0, 1)
Target.Offset(0, -1) = c.Offset(0, 3)
bFound = True
Exit For
End If
Next c
If bFound = False Then
MsgBox ("Ref No not found.")
Target = ""
End If
End If
End If
End Sub

Hth,
Merjet

Maybe you could apply Data|filter|autofilter to the first worksheet.

Then filter only the values/rows you want copied and then copy them to the
second sheet.

If you really need a macro, you could record one when you did it manually.

This works pretty well when both sheets are laid out the same. I'm not sure
that's what you have, though.

Akash wrote:

Hi,

I am facing some problem in regards to formula.

I have two sheets

1. Schedule
2. Input

In Shedule Sheet I have four columns:

Ref No Project Dwg. Unit Qty
01 KEVENTER KOLKATA PC-7001-3 6
02 CIPLA LTD, BADDI 7049-1A 1
03 CIPLA LTD, BADDI 7049-2A 1
04 CIPLA LTD, BADDI 7049-3A 1
05 CIPLA LTD, BADDI 7049-5A 1
06 CIPLA LTD, BADDI 7049-9A 1

the data of Schedule Work is entered in the this sheet.

Now In put sheet we have below mentioned columns:

1. Drg No
2. Project Name
3. FH
4. FL
5. RD
6. FPI
7. QTY
8. Ref No

Now I want a macro through which when ever i enter Ref No in the Input
sheet it should pick the value of Drg No/Project Name/Qty: from
Shedule sheet and paste it autometically in the Input sheet so that
the user had not to copy it again and again.

Right now i am using the belwo mentioned Formula...
=IF(TRIM(H5)="","",LOOKUP(H5,SCHEDULE!A3:A5000,SCH EDULE!C3:C5000))

I want a macro becoz the formula gets deleted by the users by mistake.
Then i had to sit to correct the Format everytime.

Awaiting for your help.

Thanks in Advance.

Regards

Akash

--

Dave Peterson

On Feb 22, 10:34 am, "Akash" wrote:
Hi,

I am facing some problem in regards to formula.

I have two sheets

1. Schedule
2. Input

In Shedule Sheet I have four columns:

Ref No Project Dwg. Unit Qty
01 KEVENTER KOLKATA PC-7001-3 6
02 CIPLA LTD, BADDI 7049-1A 1
03 CIPLA LTD, BADDI 7049-2A 1
04 CIPLA LTD, BADDI 7049-3A 1
05 CIPLA LTD, BADDI 7049-5A 1
06 CIPLA LTD, BADDI 7049-9A 1

the data of Schedule Work is entered in the this sheet.

Now In put sheet we have below mentioned columns:

1. Drg No
2. Project Name
3. FH
4. FL
5. RD
6. FPI
7. QTY
8. Ref No

Now I want a macro through which when ever i enter Ref No in the Input
sheet it should pick the value of Drg No/Project Name/Qty: from
Shedule sheet and paste it autometically in the Input sheet so that
the user had not to copy it again and again.

Right now i am using the belwo mentioned Formula...
=IF(TRIM(H5)="","",LOOKUP(H5,SCHEDULE!A3:A5000,SCH EDULE!C3:C5000))

I want a macro becoz the formula gets deleted by the users by mistake.
Then i had to sit to correct the Format everytime.

Awaiting for your help.

Thanks in Advance.

Regards

Akash

thanks a tonn..

it has been a very helpful tool which you had provided me...

Thanks

Akash