Linking data between sheets

[jhodge]

I am linking sheets together and have hit a snag. I have referenced
cell in another sheet, (='Cheese Vat Sheet'!D2) that is the result of
formula on that sheet. The problem I am having is that when I use the
"IF" function in another cell, (B3) based on the linked cell I kee
getting a, "false" result. Here is the formula I entered.

=IF(B3=8,"(#3)",IF(B3=7,"(#3&4)",IF(B3=9,"0 ")))

I've looked through my excel bible 2000 but havent really seen i
referenced there.

idea's?

thanks

Ji

 

[Andy B]

Hi

If you are getting a FALSE response, I would guess it is because none of
your criteria apply to B3 - ie that is is not 8 or 7 or 9. One way to get
round this is to use the FALSE option in your IF formula:
=IF(B3=8,"(#3)",IF(B3=7,"(#3&4)",IF(B3=9,"0 ","")))

Hope this helps.

 

[jr]

Hi,

The only way I know that you'd get a false with
your formula is if your referenced cell (B3)produces
a value other than 7 ,8, or 9. If it is linked
to the other sheet's cell, then that one must
be producing a non-7,8,or 9 value. Double check
your linked and referenced cells to see where
a non-7,8,9 value is generated. If you just want
a blank instead of "false" when the value is non-7,8,9
then complete the formula for the "false" side:

=IF(B3=8,"(#3)",IF(B3=7,"(#3&4)",IF(B3=9,"0 ","")))

jr

 

[Peter Atherton]

The False resuly occurs when there Cheese VAT Sheet D2 is
empty.

The revised formula will return a space when this occurs.
=IF($B$3=8,"(#3)",IF($B$3=7,"(#3&4)",IF($B$3=9,"0 ","")))

You could place a message between the Double Quotes at the
End to help the user - e.g. "Enter 7,8 or 9 in D2 Cheese
VAT Sheet"

Regards
Peter

 

[jhodge]

Thanks, that helped me with that part but I'm still not getting the
results I'd hoped for. When I enter the number directly it works fine
but when I link it to the other sheet it doesn't work. the cell I've
linked to is formatted to round to the whole number could this be the
problem? or is there something else I should do since the cell value is
the result of a formula?

thanks

Jim

 

[Andy B]

Hi

If your cell is formatted to show a whole number, then it will not equal 7
or 8 or 9 - hence your problem. It might SHOW 7 or 8 or 9 but it does not
equal the same.
You could try using ROUND:
=IF(ROUND(B3,0)=8,"(#3)",IF(ROUND(B3,0)=7,"(#3&4)",IF(ROUND(B3,0)=9,"0
","")))
or INT depending on what you need.