Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    More on Define Name (Excel XP)

    Next step in the current process I'm working on is to restructure the way I've got my cell names defined.

    Say for example in Column A I've got 20 individual items listed one above the other. That is, something like "tables" in A1, "chairs" in A2, "foot stools" in A3 etc. The next step is to define each of those cells with a name that matches what is in the cell. So, A1 would be defined as "chairs" since "chairs" is in A1.

    This is all easily accomplished. What I didn't expect though is that after the names are all created, and I want to sort the column alphabetically, the definded names stay with the CELL instead of traveling to the new location of the item as determined by the alphabetic sort. So now "chairs" , which was in A1 is now in perhaps A8, but the NAME "chairs" remained in cell A1 where there is now a different item due to the sort.

    I know that the list of cell names appears alphabetically in the cell identification window drop down at the top left, but it would be nice to be able to arrange the list alphabetically as well.

    Have I explained this clearly enough?

    Thanks,
    BH

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: More on Define Name (Excel XP)

    Personally, I think it is a bit too much to name individual cells in this manner and is only really powerful for ranges (A1:A20). My reason...I think you have discovered it yourself <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I am not exactly sure what you are trying to do but we may have to look at another way...possibly vlookups...but let us see what you are attempting.
    Jerry

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: More on Define Name (Excel XP)

    As Jezza notes, you've run into a limitation of naming individual cells. Instead of using named cells, I'd use a separate column with the names, like this:

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>1</td><td align=center>Item</td><td align=center>Quantity</td><td align=center>2</td><td>Tables</td><td align=right>7</td><td align=center>3</td><td>Chairs</td><td align=right>28</td><td align=center>4</td><td>Foot stools</td><td align=right>11</td></table>
    You can sort this table by item name or by quantity, and the other column will sort with it correctly. You won't be able to refer directly to 'Chairs' in a formula, but if necessary, you could use MATCH or VLOOKUP to retrieve the quantity for an item.

    Note: similar questions have been asked in the newsgroups. An Excel MVP came up with some VBA code to sort a range with named cells, but he warned that it is very fragile and should actually be avoided.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: More on Define Name (Excel XP)

    Okay, here is what is going on. The furniture was just a simple quick example. In reality I'm doing a straight forward database sort of customer names and address thing.

    The customers and their addresses arelisted in the worksheet.....each customer with his own row. The SEND TO QUOTE button then does a COPY/PASTE SPECIAL to bring the name, address1, address2, city/state/zip to the quote form.

    IGNORE THE SEND TO INVOICE button macro.....it is yet to be properly set up.

    What I did with this list in my old system was assign the cells where a customer's information is located with that cutomer's name. Then, I set up cell A1 to show the CUSTOMER_NAMES in the CELL IDENTIFICATION WINDOW (top left of screen) when cell A! is selected. This drop down list in the cell identification window automatically comes up alphabetized. Select the right customer from the list and the appropriate cell is automatically selected. The using the SEND TO QUOTE button macro pastes the customer data into the Quote (and later my Invoice) form.

    I'm sure there is another way to do this. However, if it is a lot more complex I can live with this sytem and just not alphabetize the customer data. It shows up in the cell identification window alphabetized anyway.

    Names and addresses have been changed to protect the guilty (late paying deadbeat customers !!!) heh heh........

    Thanks!
    BH
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: More on Define Name (Excel XP)

    Perhaps you can use the attached as a starting point. I've made Customer_Names a dynamic range, and added data validation to cell A1 of type List with this dynamic range as list.
    A Worksheet_Change event procedure in the worksheet module jumps to the cell whose content is selected in A1.
    You can sort the range etc.
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: More on Define Name (Excel XP)

    Hans,

    Now that's pretty slick. I just pasted my original customer data back and in and it worked perfect.

    The only problem is I don't have a CLUE what is going on~!!

    Is there someplace I can get some info on the "dynamic range" and "valiation". I'll do a Google search after I check in Excel help a bit (for what good that will be!).

    THANKS !!!!!

    BH

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: More on Define Name (Excel XP)

    See Named Ranges on Chip Pearson's site for detailed information about named ranges in general and dynamic named ranges in particular.

    See Excel -- Data Validation -- Introduction on Debra Dalgleish's site for info about validation.

    Right-click the sheet tab and select View Code from the popup menu to see the VBA code for the Worksheet_Change event procedure.

Posting Permissions

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