Stripping the Quotes from Concatenate Result to Use in Formula

[mbabla]

So, I have used concatenate often in my spreadsheets. But I have encountered this situation in using it.

I have an output from the Concatenate formula that is like this,
$A5:$P5

It is dynamically generated with the input being two MATCH functions, i.e.,

=CONCATENATE($A",MATCH(B1,MainSht!$E1:$E1000,0),":$P",MATCH(B1,MainSht!$E1:$E7000,0))

<tbody>
</tbody>

I am trying to use that as an input in a formula
Large($A5:$P5,1)

to dynamically find the largest value in a row, or the 2nd largest, etc.

I can get Large($A5:$P5,1) to work
and I can get the output of the concatenate to be
Large($A5:P5,1)

but when I do

=Large(CONCATENATE($A",MATCH(B1,MainSht!$E1:$E1000,0),":$P",MATCH(B1,MainSht!$E1:$E7000,0)),1) it does not work, and I found out its because the output of concatenate is a text string with quotes, i.e., "$A5:$P5" and the array formula cannot read that. It needs to strips the quotes. Any ideas? Best, Bob

<tbody>
</tbody>

 

[NeonRedSharpie]

=LARGE(INDIRECT(CONCATENATE("$A",MATCH(B1,MainSht!$E1:$E1000,0),":$P",MATCH(B1,MainSht!$E1:$E7000,0))),1)

Try that.

 

[mbabla]

Hi NeonRedSharpie,

Thanks for your response. That does not work.

In fact,

the subset to that,


=INDIRECT(CONCATENATE("$A",MATCH(B1,MainSht!$E1:$E1000,0),":$P",MATCH(B1,MainSht!$E1:$E7000,0)))</pre> returns an error, i.e., the problem is still that the output of concatenate is a text string with quotes around it, i.e.,
"$A1:$P1000"
and not what any formula input wants, which is
$A1:$P1000

What I am looking for is a way to convert that text output, to get rid of the quotes around it, so that it can be used in a formula.

Thanks,

Bob

 

[NeonRedSharpie]

Indirect takes a text string (something with quotes) and returns it as a cell reference. Try this:

=SUM(INDIRECT(CONCATENATE("A","5",":","P","5")))

You will see it returns a value (assuming A5:P5 is populated with numbers).

Indirect's entire job is to take a text reference to a cell and turn it into a direct reference to said cell.

 

[mbabla]

Hi NeonRedSharpie:

Thanks! You are right. It works. I had a sheet reference that I did not include in the e.g. earlier - putting it back in, it did work.

Thanks again for your help.

Best,

Bob