Converting to R1C1 in FormulaArray
 

G'day there One & All, it's me again...

This time around I'm having a problem converting one of John
Walkenshaw's Array Formulae to R1C1 addressing as the Help file tells me
I'm supposed to.

It's not that difficult, but I can't figure out how to put the
references in. Here's what I just tried. I didn't expect it to work, but
the combinations of quotes (",',`) and ampersands I've already used
didn't work either. I'm sure someone out there knows how it's done.

All assistance will be gratefully accepted.

Selection.FormulaArray = "=index(data,small(if(match(data,data,0)
=row(indirect(R2C8:R[rows(data)]C8)),match(data,data,0),''),row(indirect
(R2C8:R[rows(data)]C8))))"


Thanks in advance,
Ken McLennan
Qld, Australia

Converting to R1C1 in FormulaArray
 

G'day there One & All, it's me again...

This time around I'm having a problem converting one of John
Walkenshaw's Array Formulae to R1C1 addressing as the Help file tells me
I'm supposed to.

Please disregard this request. I didn't even get the question
right this time around =).

For some reason I can't get the formula into the range object,
after several changes, but I'm still working on it =)

See ya,
Ken McLennan
Qld, Australia

Converting to R1C1 in FormulaArray
 

Try John Walkenbach's arrayformula in A1 reference style.

You might be pleasantly surprised.





Ken McLennan wrote:

G'day there One & All, it's me again...

This time around I'm having a problem converting one of John
Walkenshaw's Array Formulae to R1C1 addressing as the Help file tells me
I'm supposed to.

It's not that difficult, but I can't figure out how to put the
references in. Here's what I just tried. I didn't expect it to work, but
the combinations of quotes (",',`) and ampersands I've already used
didn't work either. I'm sure someone out there knows how it's done.

All assistance will be gratefully accepted.

Selection.FormulaArray = "=index(data,small(if(match(data,data,0)
=row(indirect(R2C8:R[rows(data)]C8)),match(data,data,0),''),row(indirect
(R2C8:R[rows(data)]C8))))"

Thanks in advance,
Ken McLennan
Qld, Australia

--

Dave Peterson

Converting to R1C1 in FormulaArray
 

G'day there Dave,

Try John Walkenbach's arrayformula in A1 reference style.

Doh!!! I got his name wrong!!!

Sorry about that John!


You might be pleasantly surprised.

OK then. I'll follow your advice and work with A1 to see what
happens. I hope it won't result in a loud explosion though. I've got a
headache =)

See ya
Ken McLennan
Qld Australia

Converting to R1C1 in FormulaArray
 

It surprised me when I found this top secret info, too.

<vbg

Ken McLennan wrote:

G'day there Dave,

Try John Walkenbach's arrayformula in A1 reference style.

Doh!!! I got his name wrong!!!

Sorry about that John!


You might be pleasantly surprised.

OK then. I'll follow your advice and work with A1 to see what
happens. I hope it won't result in a loud explosion though. I've got a
headache =)

See ya
Ken McLennan
Qld Australia

--

Dave Peterson