Insert Text When Blank

[Chuck N]

Lets see if i can explain this. I am doing a certified weight cert and you input values on one sheet and vlookup functions call the information onto the actual certificate.

There are three values that we are working with, Gross Weight, Tare Weight, and Net Weight. Gross is F2, Tare is G2 and Net is H2. When a value is only entered in Tare, Net says TARE ONLY as the function reads

=IF((F2-G2)>0, F2-G2, "TARE ONLY")
I now need F2 to read "TARE ONLY" as well if F2 is blank.
Conversly, if G2 is blank, F2 and H2 need to read "GROSS ONLY"

Thanks for your help.

[Dave Peterson]

Maybe...

=IF(COUNTA(F2:H2)<>1,"",IF(F2<>"","Gross only",
IF(G2<>"","Tare Only","Net Only")))

(all one cell)

Chuck N wrote:
>
> Lets see if i can explain this. I am doing a certified weight cert and
> you input values on one sheet and vlookup functions call the
> information onto the actual certificate.
>
> There are three values that we are working with, Gross Weight, Tare
> Weight, and Net Weight. Gross is F2, Tare is G2 and Net is H2. When a
> value is only entered in Tare, Net says TARE ONLY as the function reads
>
> =IF((F2-G2)>0, F2-G2, "TARE ONLY")
> I now need F2 to read "TARE ONLY" as well if F2 is blank.
> Conversly, if G2 is blank, F2 and H2 need to read "GROSS ONLY"
>
> Thanks for your help.
>
> --
> Chuck N
> ------------------------------------------------------------------------
> Chuck N's Profile: http://www.excelforum.com/member.php...o&userid=27057
> View this thread: http://www.excelforum.com/showthread...hreadid=497626

--

Dave Peterson

[Chuck N]

hmm, couldn't get that to work. Any other suggestions?

[Dave Peterson]

What happened that didn't work when you tried it?


Chuck N wrote:
>
> hmm, couldn't get that to work. Any other suggestions?
>
> --
> Chuck N
> ------------------------------------------------------------------------
> Chuck N's Profile: http://www.excelforum.com/member.php...o&userid=27057
> View this thread: http://www.excelforum.com/showthread...hreadid=497626

--

Dave Peterson

[Chuck N]

i put it in cell J2, and when i deleted the value for G2, the formula just calculated the weight over and when i deleted the value for F2 it said TARE ONLY in H2 because of the forumula I already created.

If i change the formula where it has the COUNTA(F2:H2)<> to 2, then it Shows the values i need in J2. I need them to show up in F2, G2, or H2. Does that make sense? The formula looks like this now:

=IF(COUNTA(F2:H2)<>2,"",IF(F2<>"","Gross only",IF(G2<>"","Tare Only","Net Only")))

I feel we're getting there!

[Dave Peterson]

It doesn't make sense to me.

If you put something in F2, then why would you want to overwrite that value with
the string "Gross only"?

And if you do decide to keep the formula in a separate cell (the only way it'll
work), you'll want to change that check back to 1.



Chuck N wrote:
>
> i put it in cell J2, and when i deleted the value for G2, the formula
> just calculated the weight over and when i deleted the value for F2 it
> said TARE ONLY in H2 because of the forumula I already created.
>
> If i change the formula where it has the COUNTA(F2:H2)<> to 2, then it
> Shows the values i need in J2. I need them to show up in F2, G2, or
> H2. Does that make sense? The formula looks like this now:
>
> =IF(COUNTA(F2:H2)<>2,"",IF(F2<>"","Gross only",IF(G2<>"","Tare
> Only","Net Only")))
>
> I feel we're getting there!
>
> --
> Chuck N
> ------------------------------------------------------------------------
> Chuck N's Profile: http://www.excelforum.com/member.php...o&userid=27057
> View this thread: http://www.excelforum.com/showthread...hreadid=497626

--

Dave Peterson

[Chuck N]

If there is no value in F2 then "TARE ONLY" needs to show up in F2 so that you cannot handwrite a value in over it when you print out a certificate. I hope this makes sense.

[Dave Peterson]

Each cell can contain a formula or a value--but not both at the same time.
(Yes, the formula will evaluate to a value...)

I think I'd just fill in the "empty" cells with XXXXXXXXXX's. Then the user
will have to overwrite them to enter the real value. If they don't overwrite
them, then you'll see the x's.

Chuck N wrote:
>
> If there is no value in F2 then "TARE ONLY" needs to show up in F2 so
> that you cannot handwrite a value in over it when you print out a
> certificate. I hope this makes sense.
>
> --
> Chuck N
> ------------------------------------------------------------------------
> Chuck N's Profile: http://www.excelforum.com/member.php...o&userid=27057
> View this thread: http://www.excelforum.com/showthread...hreadid=497626

--

Dave Peterson