+ Reply to Thread
Results 1 to 22 of 22

How to make the resulting value becomes a NAME

  1. #1
    Registered User
    Join Date
    06-29-2007
    Posts
    15

    How to make the resulting value becomes a NAME

    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.
    Last edited by Mike Chester; 03-22-2012 at 09:14 PM. Reason: (CLOSED)

  2. #2
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,101

    Re: How to make the resulting value becomes a NAME

    Hello Mike Chester,

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

  3. #3
    Registered User
    Join Date
    06-29-2007
    Posts
    15

    Re: How to make the resulting value becomes a NAME

    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.

  4. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,340

    Re: How to make the resulting value becomes a NAME

    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
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  5. #5
    Registered User
    Join Date
    06-29-2007
    Posts
    15

    Re: How to make the resulting value becomes a NAME

    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.

  6. #6
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,340

    Re: How to make the resulting value becomes a NAME

    =indirect(A1)

  7. #7
    Registered User
    Join Date
    06-29-2007
    Posts
    15

    Re: How to make the resulting value becomes a NAME

    Hi Vlady, it is giving me #REF! result

  8. #8
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,340

    Re: How to make the resulting value becomes a NAME

    like this???

    indirect.xlsx

  9. #9
    Registered User
    Join Date
    06-29-2007
    Posts
    15

    Re: How to make the resulting value becomes a NAME

    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

  10. #10
    Registered User
    Join Date
    06-29-2007
    Posts
    15

    Re: How to make the resulting value becomes a NAME

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

  11. #11
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,101

    Re: How to make the resulting value becomes a NAME

    Hello Mike,

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

  12. #12
    Registered User
    Join Date
    06-29-2007
    Posts
    15

    Re: How to make the resulting value becomes a NAME

    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.

  13. #13
    Registered User
    Join Date
    06-29-2007
    Posts
    15

    Re: How to make the resulting value becomes a NAME

    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.

  14. #14
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,101

    Re: How to make the resulting value becomes a NAME

    Hello Mike,

    See attached WorkBook. Maybe we are over complicating things a bit.
    Attached Files Attached Files
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  15. #15
    Registered User
    Join Date
    06-29-2007
    Posts
    15

    Re: How to make the resulting value becomes a NAME

    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.
    Last edited by Mike Chester; 03-22-2012 at 03:11 AM.

  16. #16
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,101

    Re: How to make the resulting value becomes a NAME

    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?

  17. #17
    Registered User
    Join Date
    06-29-2007
    Posts
    15

    Re: How to make the resulting value becomes a NAME

    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)

  18. #18
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,340

    Re: How to make the resulting value becomes a NAME

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

  19. #19
    Registered User
    Join Date
    06-29-2007
    Posts
    15

    Re: How to make the resulting value becomes a NAME

    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.

  20. #20
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,134

    Re: How to make the resulting value becomes a NAME

    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

  21. #21
    Registered User
    Join Date
    06-29-2007
    Posts
    15

    Re: How to make the resulting value becomes a NAME

    Hi Pete,

    Do you have any solution please? Thanks.

  22. #22
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,134

    Re: How to make the resulting value becomes a NAME

    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
    Last edited by Pete_UK; 03-23-2012 at 06:31 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1