Need Help convert text to actual cell range reference address

[liang.yuwei]

hi all

I have wrote up a function that returned text string "B1:B18". What
function can I use so this text string can be used as an actual cell
range reference?

Specifically, I want to make =match(C1,[a function that returned
"B1:B18"],false) work, so that match can see "B1:B18" as actual cell
range B1:B18

 

[Ken Johnson]

hi all

I have wrote up a function that returned text string "B1:B18". What
function can I use so this text string can be used as an actual cell
range reference?

Specifically, I want to make =match(C1,[a function that returned
"B1:B18"],false) work, so that match can see "B1:B18" as actual cell
range B1:B18

Maybe the INDIRECT function. If your function that returns "B1:B18" is
in A1 then...

=MATCH(C!,INDIRECT(A1),0)

Ken Johnson

 

[Ken Johnson]

hi all

I have wrote up a function that returned text string "B1:B18". What
function can I use so this text string can be used as an actual cell
range reference?

Specifically, I want to make =match(C1,[a function that returned
"B1:B18"],false) work, so that match can see "B1:B18" as actual cell
range B1:B18

Maybe the INDIRECT function. If your function that returns "B1:B18" is
in A1 then...

=MATCH(C!,INDIRECT(A1),0)

Ken Johnson

That should have been C1, not C!

Ken Johnson

 

[liang.yuwei]

On Jun 6, 9:02 am, (e-mail address removed) wrote:

hi all
I have wrote up a function that returned text string "B1:B18". What
function can I use so this text string can be used as an actual cell
range reference?
Specifically, I want to make =match(C1,[a function that returned
"B1:B18"],false) work, so that match can see "B1:B18" as actual cell
range B1:B18

Maybe the INDIRECT function. If your function that returns "B1:B18" is
in A1 then...

Ken Johnson

That should have been C1, not C!

Ken Johnson

Indirect does not work on cell range. Besides, I don't want return of
value in the cell, I want the actual cell reference

 

[Peo Sjoblom]

Maybe the INDIRECT function. If your function that returns "B1:B18" is

Indirect does not work on cell range. Besides, I don't want return of
value in the cell, I want the actual cell reference

Did you even try it? If A1 holds B1:B18 then Ken's formula works.


--


Regards,


Peo Sjoblom

 

[liang.yuwei]

Did you even try it? If A1 holds B1:B18 then Ken's formula works.

--

Regards,

Peo Sjoblom

I know what he means... but I can't really afford to use an extra cell
to store the resulting text, because this match is just 1 nesting
piece of a complex function set. I'm looking to be able to do this
directly using another function, instead of another cell + function.

 

[liang.yuwei]

I know what he means... but I can't really afford to use an extra cell
to store the resulting text, because this match is just 1 nesting
piece of a complex function set. I'm looking to be able to do this
directly using another function, instead of another cell + function.

oh wait...

Looks like it works even w/o using A1... weird didn't happen
yesterday...

Thanks for the help Ken

 

[Peo Sjoblom]

I know what he means... but I can't really afford to use an extra cell
to store the resulting text, because this match is just 1 nesting
piece of a complex function set. I'm looking to be able to do this
directly using another function, instead of another cell + function.

You can use the formula that returns the B1:B18 as well

=MATCH(C1,INDIRECT(MyFunction),0)


--


Regards,


Peo Sjoblom

 

[liang.yuwei]

You can use the formula that returns the B1:B18 as well

=MATCH(C1,INDIRECT(MyFunction),0)

--

Regards,

Peo Sjoblom

Thanks Peo