Convert Concatenated Text to Formula

**locyip** · 02-04-2010, 12:57 PM

Hi

I have a formula that I create using concatenate and I get the right formula as below:

Cell B63
="=MATCH(B61,A"&N(1+B)&":A50,FALSE)"

The result comes like this:
=MATCH(B61,A9:A50,FALSE)

I would like to use this as a formula, however I cant figure this out
=Indirect(B63)
give me a #REF error.

Requesting your help.

Cheers

**JBeaucaire** · 02-04-2010, 01:12 PM

If entered as a normal formula, this would work:

=MATCH(B61,INDIRECT("A"&N(1+8)&":A50"),FALSE)

I changed your "B" to an 8 for now, since I don't know how the letter B can resolve to a value of 8. If you explain that, we can fix that so it works, too. Is B a cell value somewhere?

**locyip** · 02-04-2010, 01:50 PM

Sorry

A minor mistake
the original statement would look like:
"=MATCH(B61,A"&N(1+B24)&":A50,FALSE)"

I am deriving the value B24 using another calculation.

**JBeaucaire** · 02-04-2010, 01:55 PM

Do you need the N() function? Perhaps:

=MATCH(B61,INDIRECT("A" & N(B24)+1 & ":A50"),FALSE)

Remember, this is a normal formula, don't put that in a text string.

**locyip** · 02-04-2010, 02:05 PM

The idea looked good. however it is not working..

Using the exact same statement - I get a #N/A answer

Original: =MATCH(B61,INDIRECT("A" & N(B24)+1 & ":A50"),FALSE)
I checked the final statement looks like this:
=MATCH(B61,A9:A50,FALSE)
and there is definitely a match of B61 in A9 to A50

**locyip** · 02-04-2010, 02:07 PM

wait.. its done..

sorry.. my mistake..

awesome man.. thanks so much for your help.

Cheers !

**JBeaucaire** · 02-04-2010, 02:08 PM

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)