1. ## Hairy IF Statement?

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?

2. 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

3. 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.

4. 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;"-";@

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

Lou Sander (2016-01-04)

6. 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.

7. 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.

8. 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

9. This stuff IS fun to fool around with, even at my (intermediate?) level of understanding.

10. Lou,
A built-in accounting format should also give you pretty much what you need though it would include a \$ sign with the dash.

11. 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.

12. 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.

#### Posting Permissions

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