Excel help requested

[ktehmok]

I have the following formula:

=D5+(B5*E5/10)+(F12+F8)+(C12*'C'!G3)/G12 in cell G5, G6, G7 and on down (The row numbers change accordingly).

A value is enter into column B5 (and B6, etc..). Is there a way to have the display in the G column in other rows stay blank if no value is entered in a different row on column B? Only one value will be entered at a time in the B column.

Thanks,

Edit: Correction, values are entered into other cells. But I would like the cells in column B to determine if anything is shown in the cells in column G.

 

[edcarman]

You could try using the IF command in Excel.
Say you want G5 to have some value based on B5, you would write in G5:

=IF(ISBLANK(B5),NA(),D5+(B5*E5/10)+ etc.)

This checks if B5 is blank and displays #N/A if it is and the result of your formula if it isn't.

If you are only going to have one value in the B column, then you might consider using an absolute reference to a single cell. If B5 has value you are interested in, then you can reference it as $B$5. This means that when you autofill your formulas, they will always reference B5, rather than B6,B7 etc.

 

[ktehmok]

Thank you for the response. I came up with this last night after I posted:

=IF(B5=0,"",B5*E5/10)+D5+(F12+F8)+(C12*'C'!G3)/G12

Now it shows #VALUE! instead of a numerical amount. That was the key thing I was looking for, so other people don't read the wrong amount. I'll try yours when I get to work today.

Thanks again.

 

[edcarman]

That would also work. Just be careful where you've got you brackets. At the moment the IF statement is only evaluating B5*E5/10, so when the B5 is 0, the formula looks something like this: = " " + D5 +(F12 +F8) etc. - hence #VALUE in the cell.

Rather write it as:

=IF(B5=0,"",(B5*E5/10)+D5+(F12+F8)+(C12*'C'!G3)/G12)

so the whole expression is evaluated. This way, if you get the #VALUE error for some other reason you will be able to pick it up and not dismiss it as normal