Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Indirect Validation formula getting corrupted - part 2

    Well, I didn't think I'd be here a year later but I am. It's costume ordering time again!

    She did buy a new computer - alas it was another MAC!

    And I haven't converted this to some DB program.

    And rather than buying a new version of Office, the Apple Store just copied her old hard disk onto the new hard disk. So she's running Excel 2008 - the worst version of Excel ever per Rory.

    And the problem described in the previous thread (couldn't reply to that since it's > 320 days old), see

    http://windowssecrets.com/forums/sho...ting-corrupted

    still exists.

    In particular, after some unknown set of steps, the validation formula given in my original post becomes

    =OFFSET(INDIRECT("chest_tbl"&#REF!),0,2,8,1)

    (the original formula referred to a cell in col D of the same row where there was a # to concatenate to the string "chest_tbl" in place of #REF!

    Note that "chest_tbl1", "chest_tbl2", etc are all named ranges.

    One of the things that may not have been clear in my original post or any of my subsequent responses is that this happens "randomly". What I mean by that is as follows: she selects a class to be sized - this process involves:

    - selecting the vendor chosen for this class's costumes

    - copying the names of all students in the class from a class roster sheet to the "sizing" sheet

    - other formulas in the sizing sheet then look up the measurements of all students (from another sheet where each student name and their measurements are stored), and gets the appropriate sizes from the vendor's sizing chart (yet another sheet) by comparing each student's measurements to that vendor's size chart. The resulting size for this student from this vendor is a SUGGESTION for what size to order

    - there is a size Override col for her to override the suggested size; the formula mentioned above is a validation that gives all the sizes for THAT vendor that she may choose from (knowing the student's actual "shape") for the override

    - if not already done in the first few steps above, she erases the overrides from the previous class before overriding any sizes for the current class.

    MAC Excel 2008, being what it is, you can't just select a range of cells and hit delete; you either have to delete 1 override at a time (hitting backspace / return) or select the range and use Edit | Clear | Contents

    Now thinking that last command may be the problem (not a rational thought), I've checked a few times when she did that and the formulas were still intact. I said it was not a rational thought bcs if that was the culprit, I'd think the entire formula would be wiped out. It's not; it's just corrupted with the #REF! in place of the reference to col D.

    Another puzzle is that there are rows for 50 students - bigger than her biggest class. But the problem, when it occurs, affects ALL the validation formulas in ALL 50 rows, not just one that she's working on. So it seems the corruption is happening before she even starts to do any overriding.

    I've thought of another way to provide the list of valid sizes for that vendor: just set off an area on the sheet somewhere and populate it with the sizes for that vendor once the vendor selection is made and have the individual row's validation just refer to that area. This would be a simpler validation formula but I'm not sure why this would work any better.

    Or it just may be as Rory said: the worst Excel ever.

    Any thoughts?

    TIA

    Fred
    Last edited by fburg; 2015-11-23 at 19:35.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Quote Originally Posted by fburg View Post
    MAC Excel 2008, being what it is, you can't just select a range of cells and hit delete; you either have to delete 1 override at a time (hitting backspace / return) or select the range and use Edit | Clear | Contents
    That doesn't tally with my recollections. Using the delete key should work fine, even in 2008.

    My suspicion would be that at some point she actually deletes that cell (rather than clearing its contents) or cut and pastes (not copy /paste) another cell over the top. That would cause exactly the symptoms you describe, as best I can follow.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Rory,

    Thanks for the thoughts.

    I've tried selecting multiple cells on her PC and then pressing the "Delete" key. Only 1 cell's contents are deleted. I just tried it now: select a bunch of cells (by dragging over them); press Delete. First cell's contents are deleted but Excel is waiting to see if you want to type something else in that cell. When you hit return, the first cell's contents are deleted and Excel advances to the 2nd cell in the selection.

    This is similar to the Windows Excel action of selecting cells and entering data in the first cell. When you hit return, Excel advances to the 2nd cell in the selection.

    As to the theory on the validation rules getting corrupted:
    1. If she was deleting the cell (which appears right under the Edit menu item for clear or above the Clear on the right-click menu), she'd get the message about what to do with the cells. At that point, she'd ask me what does that mean (this is not a power user). Even if she chose on her own (move up?), I'd notice the structure of the spreadsheet being messed up - which it's not. Moreover, since the sheet is protected, Delete is not even an option.

    2. I tried your theory on the cut-paste. The cell that's cut loses it's drop down box but the cell onto which it's pasted has the drop down with the correct values. Moreover, the problem, when it happens, affects ALL cells in that col. As mentioned, there's 50 cells in that column and ALL are affected (corrupted) even though she's not working with anywhere near that number of rows/students. What happens in my case is that all cells still have the drop down arrow when you click on any of them but nothing drops down since the formula has been corrupted.

    If you're interested in taking a look for trouble shooting, I'll send you the file privately (I really don't want to share this publicly since it's not really mine to share). However, right now I don't have a broken version; also per my post, I've implemented an alternative which is working fine so far. The alternative doesn't even use a drop down but allows her to type in the Override col (the one where the formula was getting corrupted, which has now been deleted). I provide a list of valid sizes for her to see based on the company. I then check in the "Final" selection column that the choice is one of the ones in the list.

    Fred

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Fred,

    That must be a backspace key, not a delete key.

    Re cut and paste, did you cut and paste over the cell that was referred to in the DV formula:
    =OFFSET(INDIRECT("chest_tbl"&#REF!),0,2,8,1)
    i.e. the cell that was there before the REF error, not the cell containing the validation.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Rory,

    Delete key: nope, it's marked "Delete". It's in the upper right corner, just like on the main part of a Windows keyboard (where there are both "Delete" and "Backspace". It just works like a backspace. After all, who ever said that "Delete" had to work to the right? Some Windows maven! But I do find it very frustrating.

    I read Steve Jobs' biography by Isaacson. Steve was somewhat spartan in his designs - nothing more than absolutely necessary. I recall this issue of 2 keys vs 1 key was even mentioned in there.

    As to your theory on cutting and pasting over the cell that was referred to in the DV formula: no for several reasons
    - that cell (in col D) for all rows is protected, so she couldn't disturb that
    - she'd have no business in that cell (or anywhere in col D) since she probably doesn't understand the use of that column (why isn't it hidden? bcs I was using it to make sure things were working correctly and never bothered to hide it)
    - that would certainly explain if the DV formula for that row was then corrupted but not why the entire column of DV formulas got corrupted at the same time

    We moved on to an alternative approach to overriding the size, as mentioned near the end of my original post in this thread. As a result, we found an error (actually having nothing to do with this) when the data for the vendor tables were entered last year that, for some reason, was never caught. But it would be nice to know why DV was getting corrupted.

    Fred

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I don't think that Delete key behaviour is normal (even for a Mac), but I will try and test it with 2008 later.

    Are you sure she couldn't have deleted the whole column D?
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Actually, your question about col D prompted me to think of something else (but no she wouldn't have deleted all of col D).

    The drop down depends on a few other cells:
    - col C, which is hidden, so she can't get to that
    - cell B2, which has a drop down to choose the costume company for the class, and B4, which has a drop down to choose whether the default size chart for the class is Adult or Child.

    Cells B11:B50, allow her to override the size chart for a particular student (eg, most of the class is "child" size but one or two bigger students are sized according to the "adult" chart).

    Col C, again hidden, creates the table name based on concatenating the costume company name and the size chart (either from the override or from B4).

    Cells D1150 look up the table number based on col C.

    The DV formula depends on col D.

    So if I delete B4 or B2 that looks like it could create the problem. BUT:
    - she says she doesn't delete the entries in B2 or B4 bcs, as soon as she clicks on either one, the drop down appears and she picks the appropriate values
    - I tried it and it seemed to work (ie, create the #REF!). But I couldn't get it to work consistently.
    - Moreover, one would think that if I entered a valid value in B2 and B4 after the deletion that the DV formula should be restored. I tried it and it was.

    So still a mystery but maybe not as big a mystery.

    Thanks.

    Fred

Posting Permissions

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