Hello from Steved

How would I go about putting the below into VBA please.

=IF(ISNUMBER(SMALL(IF($S$6:$S$106="","",ROW($S$6:$ S$106)),ROW(1:1))),INDIRECT("Summary!"&CHAR(COLUMN ()+64)&SMALL(IF($S$6:$S$106="","",ROW($S$6:$S$106) ),ROW(1:1))),"")

Thankyou.

will this work for you?

Sub test()
Range("A1").Formula = "=IF(ISNUMBER(SMALL(IF($S$6:$S$106="""",""""," & _
"ROW($S$6:$S$106)),ROW(1:1))),INDIRECT(""Summary!" "&CHAR(COLUMN()+64)&SMALL(IF($S$6:$S$106="""","""" ,"
& _
"ROW($S$6:$S$106)),ROW(1:1))),"""")"

End Sub

--


Gary


"Steved" wrote in message
...
Hello from Steved

How would I go about putting the below into VBA please.

=IF(ISNUMBER(SMALL(IF($S$6:$S$106="","",ROW($S$6:$ S$106)),ROW(1:1))),INDIRECT("Summary!"&CHAR(COLUMN ()+64)&SMALL(IF($S$6:$S$106="","",ROW($S$6:$S$106) ),ROW(1:1))),"")

Thankyou.

Hello Gary from Steved

Gary The below is run from the sheet I have named summary

I have ten sheets hence I need a event procedure, so if
somebody types in summary sheet it is then updated in another sheet

Each sheet is represented by a City name and have a formula in each off the
sheets
to put the data only associated with the city. ie if somebody types in the
Summary sheet something for say a sheet i've called Washington the the data
will go into that sheet.

Hopefully you have alook at this further.

Thankyou.

"Gary Keramidas" wrote:

will this work for you?

Sub test()
Range("A1").Formula = "=IF(ISNUMBER(SMALL(IF($S$6:$S$106="""",""""," & _
"ROW($S$6:$S$106)),ROW(1:1))),INDIRECT(""Summary!" "&CHAR(COLUMN()+64)&SMALL(IF($S$6:$S$106="""","""" ,"
& _
"ROW($S$6:$S$106)),ROW(1:1))),"""")"

End Sub

--


Gary


"Steved" wrote in message
...
Hello from Steved

How would I go about putting the below into VBA please.

=IF(ISNUMBER(SMALL(IF($S$6:$S$106="","",ROW($S$6:$ S$106)),ROW(1:1))),INDIRECT("Summary!"&CHAR(COLUMN ()+64)&SMALL(IF($S$6:$S$106="","",ROW($S$6:$S$106) ),ROW(1:1))),"")

Thankyou.