How can I modify this to make it work for any range in sheet S?

Assume source data is a defined range: MyR

In sheet: 1,

In A1:
=IF(LEFT(INDEX(MyR,ROWS($1:1),COLUMNS($A:A)))="x", COLUMN(),"")
Copy across/fill down to cover the extent of MyR

In a sheet: 2,

In A1:
=INDEX(T(OFFSET(MyR,ROWS($1:1)-1,COLUMN($A:$D)-1)),SMALL('1'!1:1,COLUMNS($A:A)))
Copy across/fill down to cover the extent of MyR

Sheet: R = no change to formulas
Just ensure the B1 copy across & row fill down covers the extent of MyR
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"MCSmarties" wrote
Thanks a lot for the quick reply and the awesome tip, it works!
However, the current setup _requires_ the original data to be starting
at A1.
I think it's because of the INDEX(S!1:1) part of the function in sheet
2.

How can I modify this to make it work for any range in sheet S?
eg, instead of having data from A1:D4 having it for example in G6:J10
(or whatever)
Ideally, one could move the data to somewhere else in S and sheet 2
(and hence sheet R) would be updated. It would make it much more
versatile!

I can of course explicitely define a range INDEX(S!G6:G6) and take it
from there,
but that kills the "adaptability" part. Maybe by using LOOKUP and
OFFSET to
find the initial data - but how can I do this?