Strange Excel 2007 Behavior

Factual Questions
1

I have a workbook. It might or might not be used to score a football pool. :wink:

Anyway, I have a summation sheet. That sheet has 14 rows. The first column of each row is a number. The second column has a formula like this:

=IF(ISERROR(INDIRECT(CONCATENATE("’",$A1,"’!A5:A60"))),0,COUNTA(INDIRECT(CONCATENATE("’",$A1,"’!A5:A60"))))

So, take the value in $A5, make that a sheet name and count non-blanks from A5:A60.

Here’s the weird thing, this formual ONLY works from if it’s contained in a cell farther down than row 5!

The EXACT same formula returns 0 if it’s contained in cells B1-B4, but suddenly returns the correct count if it’s placed in B5. The hell?

I’ve just recently switched over to 2007 and this formula worked fine in 2003.

Any help?

2

First, there’s an unintentional space :A 60, but that’s not what you meant. Are you adding/deleting rows in this sheet? Otherwise the INDIRECT-statement is useless. Why don’t you just type $A5 in your formula? Now, copied down it would change to $A6 - if not the purpose, then $A$5 will do it. If the names of worksheets are something like name1, name2, use the row()-function instead.

3

I’m not sure about your ‘INDIRECT statement is useless’ comment. How does a row() command replace INDIRECT?

My biggest concern was that it appeared that the behavior of INDIRECT had changed from 2003 to 2007, but after further experimentation, the problem appears localized to this one sheet (which is a conversion from '03 type to '07 xlsx).

Anyway, I did figure it out and it appears it’s not INDIRECT which is the issue. It’s ISERROR. ISERROR appears to be returning an error based on the fact that I’m feeding it a range of cells. Why this should be I don’t know, but I change the formula to:

=IF(ISERROR(INDIRECT(CONCATENATE("’",$A2,"’!A5"))),0,COUNTA(INDIRECT(CONCATENATE("’",$A2,"’!A5:A60"))))

All behavior is back to normal. Weird…

4

I’m glad you got the problem solved, but why don’t you try this

=COUNTA(INDIRECT(CONCATENATE(“Pool”,ROW(),"!$A$5:$A$60")))

which should give you the count of non-zero cells of sheets “Pool1”, “Pool2” and so on. ROW() is not substituting INDIRECT, but giving you only “1” if you’re on row#1 and “2” on row#2…
Now, I think I just lost the original problem:smack:

5

Ahhh…got you. Yeah, that could’ve worked too, except that if you do it that way and then insert a row above your formula, it’s now potentially broken. Especially since my “chart” started on row 2 (title row dontcha know). The reason I wrapped it in all that text is that I don’t add the later sheets until the week of. So my workbook starts with two sheets (Summary and Week 1), the next week I add the Week 2 sheet and so on and so forth. I do think this is an undocumented change in the ISERROR behavior thought since the formula as originally posted used to work fine.

6

…is such a long function name. You can use &-operator to make your formula shorter ie.

“Sheet1!A”&ROW()