How to make the resulting value becomes a NAME

[Mike Chester]

Please, I hope you can help me with my problem.

I would like to know how can I make a resulting value become a NAME RANGE?

For example, I have a value in A1 = ACC

This ACC is also a NAME RANGE which let say ACC=100 (in Name Range)

Therefore if I create a simple function in B1 like =A1, I want that the result should not be the text ACC, but a Name Range ACC.

Do you know how can I do this?

TIA.

[Winon]

Hello Mike Chester,

Do you actually want B1 to show "Name Range ACC", or do you want to create another Name Range in B1?

[Mike Chester]

Hi Winon,

I want that the value that will come out in B1 as ACC (Name Range), not ACC as text.

But the link of B1 is on A1 and A1 has a text value of ACC.

In short I want to convert the ACC text value in A1 as ACC Name Range value in B1.

[vlady]

hello
can u try this...
A1 has a value of text ACC

B1=sum(indirect(A1)) ctrl + shift + enter

***get the sum of you range ACC

[Mike Chester]

Hi Vlady, actually the ACC Name Range is just a value (in this example it is =100), not a range.

I want to know how can I get the value of Name Range ACC (=100) in B1.

thanks.

[vlady]

=indirect(A1)

[Mike Chester]

Hi Vlady, it is giving me #REF! result

[vlady]

like this???

indirect.xlsx

[Mike Chester]

Hi Vlady,

In your Name Manager, you Refer to a cell (Sheet1!$D$1). In my case I refer to a direct value which means that my "Refers To" value is =100

[Mike Chester]

I still couldn't find a way to resolve this, I hope someone here can help me ..Thanks.

[Winon]

Hello Mike,

Please upload a sample WorkBook with what you have, and on Sheet2 show us how you want what.

[Mike Chester]

Hi Winon,

I am attaching a simple spreadsheet here.

In A1, there is a values which is in text "ACC"
Then I create a NAME inside the NAME MANGER with the same acronym ACC
This ACC refers to =100. This means that the NAME ACC is actually a value 100
What I want to have is if I use the ACC "text" value in A1 to another cell, let say for example in B1 where I put a simple function =A1, then what I want as a result is the NAME ACC, not the value ACC as a text.

[Mike Chester]

I am sorry I cannot upload the file since it is restricted in our firewall here. But i hope you can understand my explanation above, and I hope this is already enough to be understood. Thanks.

[Winon]

Hello Mike,

See attached WorkBook. Maybe we are over complicating things a bit.

[Mike Chester]

Hi Winon, honestly I really appreciate all your efforts guy here despite my not so very clear explanation of the matter and I am very thankful about that.

Actually you misunderstood me, what I mean by name is the "NAME RANGE", but instead of me providing a Name Range, only create a NAME that is ACC which is equal to 100.

I am using a computer now outside our network so I can attach the file now.

Book2.xlsx Capture.JPG

If you will see in the attached Excel sample, I would like to have a result like the one in cell B2 but by using cell A1. However, if I will use A1, what i'm getting is the result like the one in cell A1.

[Winon]

Thanks Mike,

Please bear with me, I am getting there slowly. What you have on Sheet 1 is correct. Nothing wrong there. I don't think what you are asking, if I understand it correctly, is possible. Could you perhaps show on Sheet 2, without formulas, how you would like to see the outcome to look like?

[Mike Chester]

Hi Winon,

I'm sorry but I'm back in my network with firewall and couldn't attach again the file.

But anyway, i think there is no need to provide an example in an spreadsheet to expound my point.

the outcome i would like to see is to have the value of ACC directly to the cell. in this case the value is 100.

I know that if i put the =ACC on any particular cell, it will give me 100. but that's not what I want, I want to refer to that ACC (in text) in cell A1 and to convert it to ACC (in Name, ACC=100)

[vlady]

Hello
again mike

named range usually are used as naming reference cells..
if you will use this as constant variable ( ACC = 100) Not a referrence as Acc= (reff. C1 with a value of 100)

you cannot call it like =A1 or any other function like =indirect(A1)

one way to use the value in A1(ACC) is to have a logical statement for e.g. ( if....)
=if(A1="ACC", ACC,"")
this way you can use the named range ACC ( constant value of 100 )



again typing alone =A1 will not be applicable to get the named range ACC because its not a range or reference..(it's constant)

named range constant value is not used a references.

again the main purpose of naming range is naming range of cells.

this is just my own understanding...

[Mike Chester]

Thanks Vlady, probably you are right that I cannot use a constant value in a named range as a references.

So I guess I have to improvise in another way in order to achieve what I need to do. Thanks a lot anyway for your help and also to Winon.

[Pete_UK]

I've just tried three times to respond to this thread, but keep getting the "Forbidden" notice, but if this one gets through then I'll try again.

Pete

[Mike Chester]

Hi Pete,

Do you have any solution please? Thanks.

[Pete_UK]

I have tried many, many times to give you a solution, including trying to PM you last night and trying to edit my post #20, but I keep getting errors. Perhaps it is because the thread is marked as Solved.

I'll see if this one can get through.

Pete

EDIT: tried to post the solution and was unsuccessful again.

EDIT2: one final attempt, then I'm giving up. Set up this user-defined function:

Function Eval(Rng As Range) As Variant
Application.Volatile True
Eval = Evaluate(Rng.Text)
End Function

Then you can put this formula in B1:

=Eval(A1)

and it will show 100.

Hope this helps.

Pete