Announcement

Collapse
No announcement yet.

Is there ANY way to use a space in named range

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Is there ANY way to use a space in named range



    Hi,

    This is really super mega urgent and I'll be very grateful for anyone's quick reply.

    Leaving the lengthy details, I need to use some like "Food & Entertainment" and "Home (Misc. Expenses)" as a named range but Excel ('97) does not allow me to do this. Is there any way like using a ALT+ something combination which inserts a hidden character and Excel can treat that as a valid character

    Is there any solution at all?

    Many many thanks.
    Thanks

    - J

  • #2
    sorry, bad news...

    extract from M$ Help

    Guidelines for naming cells, formulas, and constants in Microsoft Excel

    What characters are allowed? The first character of a name must be a letter or an underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters.

    Can names be cell references? Names cannot be the same as a cell reference, such as Z$100 or R1C1.

    Can more than one word be used?
    Yes, but spaces are not allowed. Underscore characters and periods may be used as word separators for example, Sales_Tax or First.Quarter.

    How many characters? A name can contain up to 255 characters.

    Note If a name defined for a range contains more than 253 characters, you cannot select it from the Name box.

    Are names case sensitive? Names can contain uppercase and lowercase letters. Microsoft Excel does not distinguish between uppercase and lowercase characters in names. For example, if you have created the name Sales and then create another name called SALES in the same workbook, the second name will replace the first one.
    Kind Regards, Will Riley

    LinkedIn: Will Riley

    Comment


    • #3
      Oh noooooo :cry:

      Anyway...thanks for the reply Will.
      Thanks

      - J

      Comment


      • #4
        out of curiosity... why did you need names like that...? perhaps there's a different sort of workaround...
        Kind Regards, Will Riley

        LinkedIn: Will Riley

        Comment


        • #5
          Originally posted by WillR
          out of curiosity... why did you need names like that...? perhaps there's a different sort of workaround...
          Actually my users are a bit "picky" and want to see, for example, 'Food & Entertainment' as is rather than 'FoodEntertainment' or 'Food_Entertainment' or 'Food.Entertainment'.

          Actually I am using this in the Data Validation feature and depending on what the user has selected in a column called "Main Category", I use the INDIRECT function and then in a column named "Sub Category", I am trying to display (in a list form) all the Sub-Categories belonging to the selected Category...

          I hope this explains the reason for my request? If not, please let me know and I can try to explain more. I have "Main Category" and then "Sub Category" and then a column called "Items" falling under the selected Category and Sub Category. This way, what I am trying to achieve is to help the user to select filtered data from the list and minimize any chances of incorrect data.... Does this explain????

          Thanks.
          Thanks

          - J

          Comment


          • #6
            Could you use the names with the spaces in the data validationlist, then run a piece of code to strip them out to get the range name in a subsequent cell?

            Comment


            • #7
              Originally posted by Seti
              Could you use the names with the spaces in the data validationlist, then run a piece of code to strip them out to get the range name in a subsequent cell?
              Hi Seti,

              That sounds like an idea but I do not know how to do that. WOuld it be possible for your to guide me please...

              VBA and stuff is not my stength

              Will appreciate your help.
              Thanks

              - J

              Comment


              • #8
                Following up on Seti's suggestion, if A2 is the cell with the name you want to use in indirect (without the blanks) just replace A2 in your indirect argument with SUBSTITUTE(A2," ","")
                Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

                Comment


                • #9
                  And if you also are worried about the & character, not just spaces, then I would use:

                  =SUBSTITUTE(SUBSTITUTE(A2," ",""),"&","")

                  Comment


                  • #10


                    Hi Derk and Seti,

                    Didn't get time till now to SINCERELY thank you both for your help and the INGENIOUS workaround that you gave.... Took me a while but your suggestion did the trick and has been a life saver.

                    Trillions & Billions & Millions & Thousands & Hundres of thanks to both of you
                    Thanks

                    - J

                    Comment

                    Working...
                    X