Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Weird behavious - INDEX and Form/Combo (2002)

    This problem has been sneaking up on me all day, and I think that I finally have it isolated.

    The spreadsheet has two Combo box controls from the Forms toolbox. They are formated like this:
    Combo1
    - InputRange: SourceAuxilary
    - link cell: $C$35

    Combo 2
    - InputRange: SourceBaler
    - link Cell: $C$36

    A nearby cell contains this formula:
    =INDEX(SourceAuxiliary,C35,15)

    All is well up to this point. The INDEX formula works properly. Next step is to copy its formula into the cell immediately below in preparation of controlling it with Combo2. But when I do that, the formating on Combo2 immediately changes and becomes identical with the formatting in Combo1. In other words, the Input Range and Linked Cell on Combo2 are changed. When it first happened, I thought that I had done something wrong, and manually edited the format back to its original settings (actually, this is an abbreviated explanation -- I edited *dozens* of combo's before I understood what was happening). But I have repeated this several times and there is no doubt that the simple copying of the INDEX formula into the "link cell" causes the formatting of the combo to change.

    I tried to repeat the problem on a fresh spreadsheet, but it works properly -- ie, it does not exhibit this strange behaviour.

    What's going on? If it was an Access file, I would Repair/Compact the file, but I don't know of an equivalent for Excel. Can anyone offer some advice how to fix it?
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Weird behavious - INDEX and Form/Combo (2002)

    Sounds like a corruption indeed. Have a look at the bottom of this page:

    http://www.jkp-ads.com/CorruptFiles.htm
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Weird behavious - INDEX and Form/Combo (2002)

    Thanks for the info, but it turned out not to be a corruption after all. Unknown to me (until now!!), copying the cell with the INDEX() function also copied the combo box that "referenced" it. When I pasted the cell, the new combo box was placed exactly over the original combo box, thus obscuring it. Since I had copy/pasted many times, I had a large stack of combos, all exactly aligned with one another.

    The eventual solution was to PasteSpecial > Formulas and Number Formats which omits copying the combo box.

    It's somewhat odd, because the cell that I copied was not referenced directly by the values in the combo box format. The copied cell referenced a cell that was itself referenced by the combo. I don't know how far back Excel would trace the references before it decided not to copy the combo box...

    Live and learn...
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

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