How to return actual cell contents?

[Guest]

I have defined a named range for a single cell and when I call that name I
get the contents of that cell. In another cell I would like to be able to
get the actual named range shown in the cell instead of the contents called
by the named range in order to manipulate it. Anyone know if that's possible?

Example:

Cell A1 is named Address and contains: 555
In cell B1 is the formula: =Address which displays: 555
Cell C1 is named AddressStreet and contains: Maple

Problem: I want to be able to obtain the real contents of cell B1, =Address
and not what it refers to, 555, so I can concatenate Address with Street and
create in Cell D1 the named range =AddressStreet and display: Maple from
cell C1.

Any ideas? Thanks in advance.

 

[Arvi Laanemets]

Hi

On separate sheet, p.e. Adresses, create a table
Address Street
555 Maple
..........

I assume adresses table is in range Addresses!A2:B100 in my example

On your sheet into column A enter address codes
Into cell D1 enter formula
=VLOOKUP(A1,Addresses!$A$2:$B$100,2,0)
and copy it down. You have street name displayed for every addres code.

Now define a named range Adresses
=Adresses!$A$2:$B$100
and modify the formula on your sheet
D1=VLOOKUP(A1,Addresses,2,0)
and copy it down (overwrite old formulas)


Arvi Laanemets

 

[Guest]

Arvi,

Thanks for the reply, but that is not what I need. I'm trying to manipulate
the actual name of the named range to create the name of another named range.
VLOOKUP is one way to do it, but for this particular spreadsheet it is not
what I wanted to do.