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,rangname 3,"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,rangnam e3,"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
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,rangnam e3,"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



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,rangnam e3,"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 -