Results 1 to 11 of 11
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Hairy IF Statement?

    WinnersGood.xlsm is a bit to large to upload to the Lounge. You can download it HERE.

    The MMA table on the Winners tab is used to summarize results from several other sheets, wk1-wk14. If one of those sheets is missing, n/a appears in all the cells that apply to it.

    The formulas in the cells in the MMA table are of the form

    =IFERROR(INDIRECT("wk1!AA20")," n/a")

    That formula puts a number into MMA if a corresponding value exists in the cell it references. The cell is blank if the cell it references contains a zero or is blank.

    I'd like to have the cell in MMA contain a dash under those conditions.

    Is there a way to do that?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Lou,

    After looking at your workbook I don't understand the use of Indirect in your formula above. It works just fine as =IFERROR('wk1'!AA24," n/a")

    Usually indirect is used to pick up a cell address or rangename to be used in the formula. Could you enlighten me because it just makes things more complicated. With out the Indirect I think this formula would solve your needs:
    =IF(IFERROR('WK1'!AA20,"N/A")="N/A","N/A",IF('WK1'!AA20=0,"-",'WK1'!AA20))

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I don't understand the INDIRECT, either!!

    It came from a previous discussion about the same workbook HERE. I was trying to overcome the problems that arise when a wkN sheet is missing, which most of them are, most of the time.

    I implemented the solution without understanding it (a thing I don't like to do, but which I find myself doing more and more these days). It worked, so I moved on. There is a price to pay for that, of course.

    Your solution to this problem, not involving INDIRECT, might just be perfect. Certainly it will be simpler. I'll give it a try.
    Last edited by Lou Sander; 2016-01-04 at 12:00. Reason: Happy to Sad
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  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
    Your formula does not actually return a blank cell when linked to a blank or 0 cell, it returns 0 but the display of the zeroes has been turned off in the Options. You could achieve what you want by turning the display of zeroes back on and then choosing a custom number format that simply displays "-" for zero values, such as:

    [$$-409]#,##0;[$$-409]-#,##0;"-";@
    Regards,
    Rory

    Microsoft MVP - Excel

  5. The Following User Says Thank You to rory For This Useful Post:

    Lou Sander (2016-01-04)

  6. #5
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    RetiredGeek: I put your formula into the entire Winners tab, and everything works as I want it to.

    rory: Good idea, but those exotic number formats drive me crazy. I guess I need to study them more, and I'll do so, I suppose, if it's ever absolutely necessary. In the meantime, I think I have a solution.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  7. #6
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Whoa!!!

    With RetiredGeek's version, when I paste a formula into another worksheet, I get a window that says "Update values - wkN", and which offers me the contents of my Documents folder. What's going on here?

    I don't think this happens with the "INDIRECT" version.

    UPDATE: There are LOTS of problems with the non-INDIRECT version, when copying it to other workbooks, when adding and deleting wkN sheets, etc. It looks like there might have been some good reasons to use INDIRECT way back when.

    I'm going to go with the INDIRECT version and try rory's suggestion about getting the dashes in the blank/zero cells.
    Last edited by Lou Sander; 2016-01-04 at 20:08.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Lou,

    When you copy a formula from one workbook to another creating a reference back to the originating workbook is the standard operation! Using Indirect to get around this is something I've never heard of before.

    BTW: to get around this behavior you can just copy the formula into Notepad then copy it out of there into the new workbook or of course just type it into the new workbook. It is also easy to break the link using the link manager. IMHO it can be dangerous using what basically amount to hacks to get around features as the next person who has to work on the workbook won't know about it and you'll probably forget about six months from now which will only cause problems down the line. Case in point, I sure didn't know why the INDIRECT was there as it was a case of indirection for me!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #8
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    This stuff IS fun to fool around with, even at my (intermediate?) level of understanding.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  10. #9
    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
    Lou,
    A built-in accounting format should also give you pretty much what you need though it would include a $ sign with the dash.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #10
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I've read up on custom number formats. The one I made is

    $#,##0.00_);[Red]($#,##0.00);"- ";@
    (there are actually 5 spaces after the dash, but they don't show up here)

    This gives me a dollar sign and two decimals for positive values, the same in red with parentheses for the very unlikely negative values, and a dash with five spaces for zeroes, and text if there's text in the cell. It makes the big, sparsely populated, tables very easy to read.

    I learned from my studies that custom number formats are part of the workbook, so if I need this one elsewhere, I'll have to come back to this workbook and pick it up.

    I learned a little about the [$-409] that tends to show up in custom formats: 1) it has to do with formats in different languages, and 2) it is VERY hard to find anything about it.

    I'm pretty sure that my original need has been met. You can see the results by going HERE and downloading WinnersTest010516.xlsm. I THINK it responds well to adding and deleting worksheets.

    Thanks to everyone for their help in this learning experience.
    Last edited by Lou Sander; 2016-01-05 at 12:02. Reason: Clarify
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  12. #11
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Well, never be too sure. When testing my work with real data, I found that I had messed up the formulas on the PBA sheet. Sheesh!

    It wasn't too hard to fix. (But I didn't bother to fix it on the uploaded file.)

    Live and learn.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

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