Constructing Range Name calls with Concatenate

[CellShocked]

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.

 

[isabelle]

hi,

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

 

[Pete_UK]

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

 

[CellShocked]

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

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

  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




- Show quoted text -