Constructing Range Name calls with Concatenate

C

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.
 
P

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
 
C

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
 
P

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 -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top