Hi folks. I have done this before and should know it. It is a simple
thing, I just cannot recall at this time.

I want to enter a range name in a cell so the value of that named range
shows up (single cell named ranges) i.e. =RangeName

This works. I now want to dynamically construct that call using other
single cell range names, so I use concatenate to "assemble" the values of
those named ranges together into one text string that also happens to
match a named range single cell value.

All I see in the cell is the concatenated value, not the resolved range
name cell value

I use a Y/N cell to turn it on, so I use:

=IF(B13="Y",Concatenate(Rngname1,rngname2,rangname3,"text"))

All I get is the concatenated text,and I want the constructed range
name to resolve. I have done this before and even been hand held through
it, but I cannot remember it for the life of me. I think I alter the
result with "TEXT()" or something similarly easy. I could put that text
in another cell, and call it directly, which does not need additional
conversion.

hi,

=IF(B13="Y",Rngname1&Rngname2&Rngname3,"")


--
isabelle

I think you need to do this:

=IF(B13="Y",INDIRECT(Rngname1&rngname2&rangname3),"")

assuming those 3 named ranges when concatenated will form a fourth
named range.

Hope this helps.

Pete

On Oct 25, 1:59*pm, CellShocked
<cellshoc...@thecellvalueattheendofthespreadsheet.org> wrote:
> * Hi folks. *I have done this before and should know it. It is a simple
> thing, I just cannot recall at this time.
>
> * I want to enter a range name in a cell so the value of that named range
> shows up (single cell named ranges) i.e. =RangeName
>
> * This works. I now want to dynamically construct that call using other
> single cell range names, so I use concatenate to "assemble" the values of
> those named ranges together into one text string that also happens to
> match a named range single cell value.
>
> * All I see in the cell is the concatenated value, not the resolved range
> name cell value
>
> *I use a Y/N cell to turn it on, so I use:
>
> *=IF(B13="Y",Concatenate(Rngname1,rngname2,rangname3,"text"))
>
> * All I get is the concatenated text,and I want the constructed range
> name to resolve. *I have done this before and even been hand held through
> it, but I cannot remember it for the life of me. *I think I alter the
> result with "TEXT()" or something similarly easy. *I could put that text
> in another cell, and call it directly, which does not need additional
> conversion.

Ahhh yes... INDIRECT() That is the one I was after!

Dang! I cannot believe that I couldn't recall it.

Only proves that you guys know that I do not do spreadsheets as part of
my job (per se). I do them to improve my workflow, but not by supervisory
direction.

Thanks


On Tue, 25 Oct 2011 12:11:15 -0700 (PDT), Pete_UK
<(E-Mail Removed)> wrote:

>I think you need to do this:
>
> =IF(B13="Y",INDIRECT(Rngname1&rngname2&rangname3),"")
>
>assuming those 3 named ranges when concatenated will form a fourth
>named range.
>
>Hope this helps.
>
>Pete
>
>On Oct 25, 1:59*pm, CellShocked
><cellshoc...@thecellvalueattheendofthespreadsheet.org> wrote:
>> * Hi folks. *I have done this before and should know it. It is a simple
>> thing, I just cannot recall at this time.
>>
>> * I want to enter a range name in a cell so the value of that named range
>> shows up (single cell named ranges) i.e. =RangeName
>>
>> * This works. I now want to dynamically construct that call using other
>> single cell range names, so I use concatenate to "assemble" the values of
>> those named ranges together into one text string that also happens to
>> match a named range single cell value.
>>
>> * All I see in the cell is the concatenated value, not the resolved range
>> name cell value
>>
>> *I use a Y/N cell to turn it on, so I use:
>>
>> *=IF(B13="Y",Concatenate(Rngname1,rngname2,rangname3,"text"))
>>
>> * All I get is the concatenated text,and I want the constructed range
>> name to resolve. *I have done this before and even been hand held through
>> it, but I cannot remember it for the life of me. *I think I alter the
>> result with "TEXT()" or something similarly easy. *I could put that text
>> in another cell, and call it directly, which does not need additional
>> conversion.

You're welcome - thanks for feeding back.

Pete

On Oct 26, 2:10*am, CellShocked
<cellshoc...@thecellvalueattheendofthespreadsheet.org> wrote:
> * Ahhh yes... *INDIRECT() *That is the one I was after!
>
> * Dang! *I cannot believe that I couldn't recall it.
>
> *Only proves that you guys know that I do not do spreadsheets as part of
> my job (per se). I do them to improve my workflow, but not by supervisory
> direction.
>
> *Thanks
>
> On Tue, 25 Oct 2011 12:11:15 -0700 (PDT), Pete_UK
>
>
>
> <pete.ashu...@yahoo.com> wrote:
> >I think you need to do this:
>
> > =IF(B13="Y",INDIRECT(Rngname1&rngname2&rangname3),"")
>
> >assuming those 3 named ranges when concatenated will form a fourth
> >named range.
>
> >Hope this helps.
>
> >Pete
>
> >On Oct 25, 1:59*pm, CellShocked
> ><cellshoc...@thecellvalueattheendofthespreadsheet.org> wrote:
> >> * Hi folks. *I have done this before and should know it. It is a simple
> >> thing, I just cannot recall at this time.
>
> >> * I want to enter a range name in a cell so the value of that named range
> >> shows up (single cell named ranges) i.e. =RangeName
>
> >> * This works. I now want to dynamically construct that call using other
> >> single cell range names, so I use concatenate to "assemble" the valuesof
> >> those named ranges together into one text string that also happens to
> >> match a named range single cell value.
>
> >> * All I see in the cell is the concatenated value, not the resolved range
> >> name cell value
>
> >> *I use a Y/N cell to turn it on, so I use:
>
> >> *=IF(B13="Y",Concatenate(Rngname1,rngname2,rangname3,"text"))
>
> >> * All I get is the concatenated text,and I want the constructed range
> >> name to resolve. *I have done this before and even been hand held through
> >> it, but I cannot remember it for the life of me. *I think I alter the
> >> result with "TEXT()" or something similarly easy. *I could put that text
> >> in another cell, and call it directly, which does not need additional
> >> conversion.- Hide quoted text -
>
> - Show quoted text -