"=IF(INDIRECT(""" & ctl.Caption & _
"!A""&ROW()+11)=0,"" "",INDIRECT(""" & ctrl.Caption & _
"!A""&ROW()+11))"

--
Regards,
Tom Ogilvy


"Robbyn" wrote in message
...
The following snippet of code works, but I'd like to simplify it. In the
formula, I'd like to change the sheet name to ctl.Caption, but no clue on
the syntax of it. I've tried a million different things, so I'm starting to
wonder if it's even possible. Thanks for any help you can give.(again)

For each ctl in grpClasses.Controls
If ctl.Value = True Then
Unload Me
'On Error GoTo e:
Set wsTest = Worksheets.Add
wsTest.Name = "Report" & ctl.caption
With Worksheets(ctl.Caption)
Range("a4").Select
ActiveCell.FormulaR1C1 = _
"=IF(INDIRECT(""1a!A""&ROW()+11)=0,""
"",INDIRECT""1a!""&ROW ()+11))"
Selection.AutoFill Destination:=Range("A4:A28"),
Type:=xlFillDefault
End With

Robbyn