F
Fred Newton
I am making an attempt to use named formulas in anger for the first
time. I've read CPearson's pages and found them very helpful and all
was going well until ....
I've set up my formulae in cells on Excel(97) so I can copy & paste
them into a name when I define it
=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)))))
and
=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)))))
I click Insert - Name - Define, type in the name for the first formula
(DraftTentoSix) and then paste in the first formula (above) into the
RefersTo window and then click OK, and then repeat the process for the
second formula (named DraftFivetoOneTen)
When I go to check what's there, it has inserted the worksheet name
(Details) before each cell reference, inserted what appear to be
line-break characters at various points and truncated the formula. I
leave well alone and set up the "master formula"
=IF(DraftTentoSix,DraftTentoSix,DraftFivetoOne).
This formula is designed to show what Draft version of a Cost Estimate
is in use from a table at the foot of the Details worksheet where the
change history is held.
What I end up with is #VALUE!. Looking at the evaluation of the
formula, it shows that LogicalTest returns #VALUE!, Value if true
returns "Draft 10" and Value if false returns "Draft 5"
Cells B66:75 contain "Draft 1" - "Draft 10" - General format
C66:75 contain the date of the change - Date format dd-mmm-yy
E66:75 contain a summary of the change - Text format
Is there an obvious flaw in my formulae/setting up of the named
formulae ?
Any help much appreciated
Regards
Fred Newton
Zurich Financial Services
time. I've read CPearson's pages and found them very helpful and all
was going well until ....
I've set up my formulae in cells on Excel(97) so I can copy & paste
them into a name when I define it
=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)))))
and
=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)))))
I click Insert - Name - Define, type in the name for the first formula
(DraftTentoSix) and then paste in the first formula (above) into the
RefersTo window and then click OK, and then repeat the process for the
second formula (named DraftFivetoOneTen)
When I go to check what's there, it has inserted the worksheet name
(Details) before each cell reference, inserted what appear to be
line-break characters at various points and truncated the formula. I
leave well alone and set up the "master formula"
=IF(DraftTentoSix,DraftTentoSix,DraftFivetoOne).
This formula is designed to show what Draft version of a Cost Estimate
is in use from a table at the foot of the Details worksheet where the
change history is held.
What I end up with is #VALUE!. Looking at the evaluation of the
formula, it shows that LogicalTest returns #VALUE!, Value if true
returns "Draft 10" and Value if false returns "Draft 5"
Cells B66:75 contain "Draft 1" - "Draft 10" - General format
C66:75 contain the date of the change - Date format dd-mmm-yy
E66:75 contain a summary of the change - Text format
Is there an obvious flaw in my formulae/setting up of the named
formulae ?
Any help much appreciated
Regards
Fred Newton
Zurich Financial Services