Nested if's limit and Named formulae

[Fred Newton]

This is the second time trying to get an answer, with more detail than
last time, so here's hoping

I have a cost estimate spreadsheet, comprising ten or so worksheets,
the first one of which (Details) is a summary worksheet with a changes
log at the bottom of it. This log contains the values Draft1-10 in
column B, the Date of change for each in column C, the worksheet
affected in column D and the nature of the change in column E.
Further up the worksheet is a field that I want to populate to show
the current version. I can pick this up by checking to see whether
there is a date in column C and text in column E as follows.

=IF(AND(E72<>"",C72<>""),B72,IF(AND(E71<>"",C71<>""),B71,IF(AND(E70<>"",C70<>""),B70,IF(AND(E69<>"",C69<>""),B69,IF(AND(E68<>"",C68<>""),B68,IF(AND(E67<>"",C67<>""),B67,IF(AND(E66<>"",C66<>""),B66,0)))))))

All was fine until someone came along with a request for more than 7
drafts as this breached the nested if's limit.

I've tried setting up 2 named formulae as follows :

DraftTentoSix
=IF(AND($E$75<>"",$C$75<>""),$B$75,IF(AND($E$74<>"",$C$74<>""),$B$74,IF(AND($E$73<>"",$C$73<>""),$B$73,IF(AND($E$72<>"",$C$72<>""),$B$72,IF(AND($E$71<>"",$C$71<>""),$B$71,0)))))

DraftFivetoOne
=IF(AND($E$70<>"",$C$70<>""),$B$70,IF(AND($E$69<>"",$C$69<>""),$B$69,IF(AND($E$68<>"",$C$68<>""),$B$68,IF(AND($E$67<>"",$C$67<>""),$B$67,IF(AND($E$66<>"",$C$66<>""),$B$66,0)))))

but when I view them in the names window I see truncated formulae and,
what appears to be line-return characters (square box characters)
after the first and second AND statements - "む" & "S" in the 2
"formulae" below.

DraftTentoSix
=AND(Details!$E$75<>"",Details!$C$75<>"")むDetails!$B$75
AND(Details!$E$74<>"",Details!$C$74<>"")SDetails!$B$74
AND(Details!$E$73<>"",Details!$C$73<>"")

DraftFivetoOne
=AND(Details!$E$70<>"",Details!$C$70<>"")むDetails!$B$70
AND(Details!$E$69<>"",Details!$C$69<>"")SDetails!$B$69
AND(Details!$E$68<>"",Details!$C$68<>"")

The result is that, when I put the formula
=If(DraftTentoSix,DraftTentoSix,DraftFivetoOne) I end up with #VALUE!

Whereas, if I put the formula
=IF(ISERROR(IF(DraftTentoSix,DraftTentoSix,DraftFivetoOne)),
DraftTentoSix,DraftFivetoOne) I get the right answer.

Can anyone help/explain where i'm going wrong please

Thanks in advance
Fred Newton
Zurich Financial Services

 

[Earl Kiosterud]

Fred,

Break your IF into separate formulas. I've put the second part of your
example into H70:

""),B70,IF(AND(E69<>"",C69<>""),B69,IF(AND(E68<>"",C68<>""),B68,H70)))))

in H70:

IF(AND(E67<>"",C67<>""),B67,IF(AND(E66<>"",C66<>""),B66,0))

Column H could be hidden.

Untested.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

This is the second time trying to get an answer, with more detail than
last time, so here's hoping

I have a cost estimate spreadsheet, comprising ten or so worksheets,
the first one of which (Details) is a summary worksheet with a changes
log at the bottom of it. This log contains the values Draft1-10 in
column B, the Date of change for each in column C, the worksheet
affected in column D and the nature of the change in column E.
Further up the worksheet is a field that I want to populate to show
the current version. I can pick this up by checking to see whether
there is a date in column C and text in column E as follows.

""),B70,IF(AND(E69<>"",C69<>""),B69,IF(AND(E68<>"",C68<>""),B68,IF(AND(E67<
"",C67<>""),B67,IF(AND(E66<>"",C66<>""),B66,0)))))))

All was fine until someone came along with a request for more than 7
drafts as this breached the nested if's limit.

I've tried setting up 2 named formulae as follows :

DraftTentoSix

=IF(AND($E$75<>"",$C$75<>""),$B$75,IF(AND($E$74<>"",$C$74<>""),$B$74,IF(AND(

DraftFivetoOne

=IF(AND($E$70<>"",$C$70<>""),$B$70,IF(AND($E$69<>"",$C$69<>""),$B$69,IF(AND(

 

[Jack Schitt]

=INDIRECT(ADDRESS(MAX(ROW(C66:C72)*(C66:C72<>"")*(E66:E72<>"")),2))

entered as an array formula (control+shift+enter when entering the formula
.... if successfully entered should be displayed in the formula bar
surrounded by curley brackets, ie {=formula})

--
Return email address is not as DEEP as it appears

This is the second time trying to get an answer, with more detail than
last time, so here's hoping

I have a cost estimate spreadsheet, comprising ten or so worksheets,
the first one of which (Details) is a summary worksheet with a changes
log at the bottom of it. This log contains the values Draft1-10 in
column B, the Date of change for each in column C, the worksheet
affected in column D and the nature of the change in column E.
Further up the worksheet is a field that I want to populate to show
the current version. I can pick this up by checking to see whether
there is a date in column C and text in column E as follows.

""),B70,IF(AND(E69<>"",C69<>""),B69,IF(AND(E68<>"",C68<>""),B68,IF(AND(E67<
"",C67<>""),B67,IF(AND(E66<>"",C66<>""),B66,0)))))))

All was fine until someone came along with a request for more than 7
drafts as this breached the nested if's limit.

I've tried setting up 2 named formulae as follows :

DraftTentoSix

=IF(AND($E$75<>"",$C$75<>""),$B$75,IF(AND($E$74<>"",$C$74<>""),$B$74,IF(AND(

DraftFivetoOne

=IF(AND($E$70<>"",$C$70<>""),$B$70,IF(AND($E$69<>"",$C$69<>""),$B$69,IF(AND(

 

[Jack Schitt]

Uff, that produces #VALUE! when there are no hits (desired zero)
But that is fairly trivial to fix. Test the whole expression for ISERROR(),
but there may be a better solution.

 

[Fred Newton]

Earl/Jack,

Thanks for the suggestions. Thus far i've opted for Earl's solution
as I can get my mind around straight forward formulae, however I will
try out the Array formula solution as it's something new to learn.

Thanks again
Regards
Fred Newton.