How to write a quote formula using only the cells selected with va

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Novice - 29 Oct 2006 17:10 GMT

I am trying to create a quote sheet using several cells in a string. While I
have figured out how to consult values in another workbook, I am unable to
write the formula so that it only calcuates those cells in the string that is
selected with a value. Example:
=B10*'[otherworkbook]!'$C$8+C10*'[otherworkbook]!'$C$9 etc..
B10 and C10 representing cells on my quote sheet that should only be
calculated when a value is entered. At this time the outcome of the formula
has a value before there is a value entered in any cell at all.
I hope you can help, and I appologize for my inexperiance with excel.
Thank you

Reply to this Message

Otto Moehrbach - 29 Oct 2006 23:19 GMT

I don't follow everything you are saying, but this formula will produce a
blank cell if either B10 or C10 are blank. Is this what you mean? HTH
Otto
=IF(AND(B10="",C10=""),"",your formula here)

>I am trying to create a quote sheet using several cells in a string. While
>I
[quoted text clipped - 9 lines]
> I hope you can help, and I appologize for my inexperiance with excel.
> Thank you

Reply to this Message

Novice - 30 Oct 2006 00:08 GMT

This is the formula I've been playing with.(D2)='[Interior AIC cost
worksheet.xls]Sheet1'!$B$11*A4+'[Interior AIC cost
worksheet.xls]Sheet1'!$B$11+'[Interior AIC cost
worksheet.xls]Sheet1'!$J$13*B4+'[Interior AIC cost
worksheet.xls]Sheet1'!$B$11+'[Interior AIC cost worksheet.xls]Sheet1'!$J$14*C4
If you'll notice the + is the end of the function for A4 in the destonation
workbook, but only to be added to the totals of B4 and C4 if a value
(quantity) is offered in said cell. If not I don't want a cell without a
value (quantity) to be calculated. I hope I'm making sense.
A.......B........C.......D
4.........2.................calculated without cell C2
Thank you for your response, Ill keep watching this subject to try and
learn. I may be going about this all wrong. Thanks again.

> I don't follow everything you are saying, but this formula will produce a
> blank cell if either B10 or C10 are blank. Is this what you mean? HTH
[quoted text clipped - 14 lines]
> > I hope you can help, and I appologize for my inexperiance with excel.
> > Thank you

Reply to this Message

Otto Moehrbach - 30 Oct 2006 14:07 GMT

Maybe this will help:
=IF(AND(B10="",C10=""),your formula if blank,your formula if not)
Keep in mind that the argument AND(B10="",C10="") is that BOTH B10 and C10
are blank. If you want the "your formula if blank" to operate if EITHER ONE
is blank, change the "AND" to "OR". HTH Otto

> This is the formula I've been playing with.(D2)='[Interior AIC cost
> worksheet.xls]Sheet1'!$B$11*A4+'[Interior AIC cost
[quoted text clipped - 33 lines]
>> > I hope you can help, and I appologize for my inexperiance with excel.
>> > Thank you

Reply to this Message