How to tell if cell contains a FORMULA or user-entered number?

I have a spreadsheet to maintain truck weights.

If the weighmaster enters a Gross, and a Secondary Gross, the spreadsheet
should calculate the amount of shrink (Gross - Secondary Gross).

If the weighmaster enters ONLY a Gross and a Tare weight, the spreadsheet
should give an ESTIMATED shrink calculation, then show the result of Gross -
Estimated Shrink in the Secondary Gross column.

How can I do this??? Only way I could think of is with some way to
determine whether Secondary Gross column cell contains a USER ENTERED NUMBER,
or still has the formula to arrive at the ESTIMATED secondary weight. If
Secondary Gross has a formula, then Shrink should be Gross * .005, and
Secondary Gross should show result of Gross - Shrink.

If BOTH Gross AND Secondary Gross cells contain user entered numbers, Shrink
should contain result of Gross - Secondary Gross.

Btw... My employer has Excel 2000. So please, if you have a solution, try
to make it one I can implement in Excel 2000.

You could set up a formula for secondary gross to test Tare to see if it is
0. If the secondary gross is known, you could key over the formula.

A B C D

Gross 2ndary Gross Tare Shrink


B2: =IF(C20,A2*(1-0.005),0)
D2: A2-B2


Or, you could set up an Estimated Secondary Gross column

A B C D
E
Gross 2ndary Gross Est 2ndaryGross Tare Shrink



C2: =IF(D20,A2*(1-0.005),0)
E2: =IF(B20,A2-B2,A2-C2)


If Tare is not numeric you can change the 0 test to "" or use
ISBLANK(D2)=FALSE. I don't know of a way (without using VBA) to test a cell
for a formula versus a user entered value.








"rcmodelr" wrote:

I have a spreadsheet to maintain truck weights.

If the weighmaster enters a Gross, and a Secondary Gross, the spreadsheet
should calculate the amount of shrink (Gross - Secondary Gross).

If the weighmaster enters ONLY a Gross and a Tare weight, the spreadsheet
should give an ESTIMATED shrink calculation, then show the result of Gross -
Estimated Shrink in the Secondary Gross column.

How can I do this??? Only way I could think of is with some way to
determine whether Secondary Gross column cell contains a USER ENTERED NUMBER,
or still has the formula to arrive at the ESTIMATED secondary weight. If
Secondary Gross has a formula, then Shrink should be Gross * .005, and
Secondary Gross should show result of Gross - Shrink.

If BOTH Gross AND Secondary Gross cells contain user entered numbers, Shrink
should contain result of Gross - Secondary Gross.

Btw... My employer has Excel 2000. So please, if you have a solution, try
to make it one I can implement in Excel 2000.

That would work... EXCEPT that the secondary Gross cells by default have the
formula to subtract contents of Shrink from Gross to arrive at an Estimated
Secondary gross. So until a secondary gross, or both a Gross AND tare weight
are entered, Secondary Gross is Null. Forgot to include. If No secondary
Gross is entered, the Estimated Shrink is calculated from the NET Weight (
Gross - Tare).

So right now, I have 2 spreadsheet setup files... One for when the scale at
the plant is working, and a second that figures strictly an estimated shrink
as long as the trucks need to be weighed elsewhere.

a b C D E
Gross Secondary Tare Net Shrink

Default cell contents

A Blank
B Formula to give estimated secondary gross =A2-E2
C Blank
D Formula (assuming in row 2) =A2 -C2
E. on sheet for estimated shrink =D2 * .005

Ideally, the formula for shrink should determine if secondary Gross still
has the default formula or user entered number since THAT would determine how
to calculate Shrink

If Secondary Gross is user-entered, Shrink should be Gross - Secondary Gross
(=A2-B2)
If Secondary Gross still contains default formula, Shrink should use
Estimate (=D2*.005)

If nothing else, if you know how to do this with a user defined VBA function
that could be used in the spreadsheet, tell me.

I already programmed the spreadsheet setup in VBA so to set up the sheet,
the user only has to know the lot number, farm name, how many loads, and the
catch count, and the VBA coding will set up the spreadsheet with properly
placed total lines, grand totals, and correctly format the shift related
summary page of the spreadsheet. So I'm by no means afraid to do this with
an added in VBA coded spreadsheet function to test whether B2 contains a
Formula.


"JMB" wrote:

You could set up a formula for secondary gross to test Tare to see if it is
0. If the secondary gross is known, you could key over the formula.

A B C D

Gross 2ndary Gross Tare Shrink


B2: =IF(C20,A2*(1-0.005),0)
D2: A2-B2


Or, you could set up an Estimated Secondary Gross column

A B C D
E
Gross 2ndary Gross Est 2ndaryGross Tare Shrink



C2: =IF(D20,A2*(1-0.005),0)
E2: =IF(B20,A2-B2,A2-C2)


If Tare is not numeric you can change the 0 test to "" or use
ISBLANK(D2)=FALSE. I don't know of a way (without using VBA) to test a cell
for a formula versus a user entered value.








"rcmodelr" wrote:

I have a spreadsheet to maintain truck weights.

If the weighmaster enters a Gross, and a Secondary Gross, the spreadsheet
should calculate the amount of shrink (Gross - Secondary Gross).

If the weighmaster enters ONLY a Gross and a Tare weight, the spreadsheet
should give an ESTIMATED shrink calculation, then show the result of Gross -
Estimated Shrink in the Secondary Gross column.

How can I do this??? Only way I could think of is with some way to
determine whether Secondary Gross column cell contains a USER ENTERED NUMBER,
or still has the formula to arrive at the ESTIMATED secondary weight. If
Secondary Gross has a formula, then Shrink should be Gross * .005, and
Secondary Gross should show result of Gross - Shrink.

If BOTH Gross AND Secondary Gross cells contain user entered numbers, Shrink
should contain result of Gross - Secondary Gross.

Btw... My employer has Excel 2000. So please, if you have a solution, try
to make it one I can implement in Excel 2000.

copy this into a VBA code module

Function ISFormula(Target As Range)
ISFormula = Target.HasFormula
End Function


In your worksheet for Shrink, enter
=IF(ISFormula(B2),D2*.005,A2-B2)




"rcmodelr" wrote:

That would work... EXCEPT that the secondary Gross cells by default have the
formula to subtract contents of Shrink from Gross to arrive at an Estimated
Secondary gross. So until a secondary gross, or both a Gross AND tare weight
are entered, Secondary Gross is Null. Forgot to include. If No secondary
Gross is entered, the Estimated Shrink is calculated from the NET Weight (
Gross - Tare).

So right now, I have 2 spreadsheet setup files... One for when the scale at
the plant is working, and a second that figures strictly an estimated shrink
as long as the trucks need to be weighed elsewhere.

a b C D E
Gross Secondary Tare Net Shrink

Default cell contents

A Blank
B Formula to give estimated secondary gross =A2-E2
C Blank
D Formula (assuming in row 2) =A2 -C2
E. on sheet for estimated shrink =D2 * .005

Ideally, the formula for shrink should determine if secondary Gross still
has the default formula or user entered number since THAT would determine how
to calculate Shrink

If Secondary Gross is user-entered, Shrink should be Gross - Secondary Gross
(=A2-B2)
If Secondary Gross still contains default formula, Shrink should use
Estimate (=D2*.005)

If nothing else, if you know how to do this with a user defined VBA function
that could be used in the spreadsheet, tell me.

I already programmed the spreadsheet setup in VBA so to set up the sheet,
the user only has to know the lot number, farm name, how many loads, and the
catch count, and the VBA coding will set up the spreadsheet with properly
placed total lines, grand totals, and correctly format the shift related
summary page of the spreadsheet. So I'm by no means afraid to do this with
an added in VBA coded spreadsheet function to test whether B2 contains a
Formula.


"JMB" wrote:

You could set up a formula for secondary gross to test Tare to see if it is
0. If the secondary gross is known, you could key over the formula.

A B C D

Gross 2ndary Gross Tare Shrink


B2: =IF(C20,A2*(1-0.005),0)
D2: A2-B2


Or, you could set up an Estimated Secondary Gross column

A B C D
E
Gross 2ndary Gross Est 2ndaryGross Tare Shrink



C2: =IF(D20,A2*(1-0.005),0)
E2: =IF(B20,A2-B2,A2-C2)


If Tare is not numeric you can change the 0 test to "" or use
ISBLANK(D2)=FALSE. I don't know of a way (without using VBA) to test a cell
for a formula versus a user entered value.








"rcmodelr" wrote:

I have a spreadsheet to maintain truck weights.

If the weighmaster enters a Gross, and a Secondary Gross, the spreadsheet
should calculate the amount of shrink (Gross - Secondary Gross).

If the weighmaster enters ONLY a Gross and a Tare weight, the spreadsheet
should give an ESTIMATED shrink calculation, then show the result of Gross -
Estimated Shrink in the Secondary Gross column.

How can I do this??? Only way I could think of is with some way to
determine whether Secondary Gross column cell contains a USER ENTERED NUMBER,
or still has the formula to arrive at the ESTIMATED secondary weight. If
Secondary Gross has a formula, then Shrink should be Gross * .005, and
Secondary Gross should show result of Gross - Shrink.

If BOTH Gross AND Secondary Gross cells contain user entered numbers, Shrink
should contain result of Gross - Secondary Gross.

Btw... My employer has Excel 2000. So please, if you have a solution, try
to make it one I can implement in Excel 2000.

Many thanks,

I was wracking my brains with the same problem.

Do you happen to know why IsRef doesn't equate to this? I tried to use
IsRef, but it responds True when the cell contains a number that's not a
formula.

Ed

"JMB" wrote:

copy this into a VBA code module

Function ISFormula(Target As Range)
ISFormula = Target.HasFormula
End Function


In your worksheet for Shrink, enter
=IF(ISFormula(B2),D2*.005,A2-B2)




"rcmodelr" wrote:

That would work... EXCEPT that the secondary Gross cells by default have the
formula to subtract contents of Shrink from Gross to arrive at an Estimated
Secondary gross. So until a secondary gross, or both a Gross AND tare weight
are entered, Secondary Gross is Null. Forgot to include. If No secondary
Gross is entered, the Estimated Shrink is calculated from the NET Weight (
Gross - Tare).

So right now, I have 2 spreadsheet setup files... One for when the scale at
the plant is working, and a second that figures strictly an estimated shrink
as long as the trucks need to be weighed elsewhere.

a b C D E
Gross Secondary Tare Net Shrink

Default cell contents

A Blank
B Formula to give estimated secondary gross =A2-E2
C Blank
D Formula (assuming in row 2) =A2 -C2
E. on sheet for estimated shrink =D2 * .005

Ideally, the formula for shrink should determine if secondary Gross still
has the default formula or user entered number since THAT would determine how
to calculate Shrink

If Secondary Gross is user-entered, Shrink should be Gross - Secondary Gross
(=A2-B2)
If Secondary Gross still contains default formula, Shrink should use
Estimate (=D2*.005)

If nothing else, if you know how to do this with a user defined VBA function
that could be used in the spreadsheet, tell me.

I already programmed the spreadsheet setup in VBA so to set up the sheet,
the user only has to know the lot number, farm name, how many loads, and the
catch count, and the VBA coding will set up the spreadsheet with properly
placed total lines, grand totals, and correctly format the shift related
summary page of the spreadsheet. So I'm by no means afraid to do this with
an added in VBA coded spreadsheet function to test whether B2 contains a
Formula.


"JMB" wrote:

You could set up a formula for secondary gross to test Tare to see if it is
0. If the secondary gross is known, you could key over the formula.

A B C D

Gross 2ndary Gross Tare Shrink


B2: =IF(C20,A2*(1-0.005),0)
D2: A2-B2


Or, you could set up an Estimated Secondary Gross column

A B C D
E
Gross 2ndary Gross Est 2ndaryGross Tare Shrink



C2: =IF(D20,A2*(1-0.005),0)
E2: =IF(B20,A2-B2,A2-C2)


If Tare is not numeric you can change the 0 test to "" or use
ISBLANK(D2)=FALSE. I don't know of a way (without using VBA) to test a cell
for a formula versus a user entered value.








"rcmodelr" wrote:

I have a spreadsheet to maintain truck weights.

If the weighmaster enters a Gross, and a Secondary Gross, the spreadsheet
should calculate the amount of shrink (Gross - Secondary Gross).

If the weighmaster enters ONLY a Gross and a Tare weight, the spreadsheet
should give an ESTIMATED shrink calculation, then show the result of Gross -
Estimated Shrink in the Secondary Gross column.

How can I do this??? Only way I could think of is with some way to
determine whether Secondary Gross column cell contains a USER ENTERED NUMBER,
or still has the formula to arrive at the ESTIMATED secondary weight. If
Secondary Gross has a formula, then Shrink should be Gross * .005, and
Secondary Gross should show result of Gross - Shrink.

If BOTH Gross AND Secondary Gross cells contain user entered numbers, Shrink
should contain result of Gross - Secondary Gross.

Btw... My employer has Excel 2000. So please, if you have a solution, try
to make it one I can implement in Excel 2000.

I've never used the ISREF function - so I don't know much about it. But it
seems that

=ISREF(A1) returns True, since it is a reference to cell A1, while
=ISREF("A1") returns False
=ISREF(2) returns False

It appears that ISREF does not evaluate the target cell to see if it is a
reference, only ISREF's immediate argument.

I s'pose one use could be to see if a dynamic named range exists. Consider
the dynamic named range Test, which is defined as
=OFFSET(Sheet2!$A$1,0,0,COUNT(Sheet2!$A:$A),1)

Basically, the named range doesn't exist until numeric data is entered into
column A of sheet2. ISREF could be used to determine if the named range
exists.

=IF(ISREF(Test), "Range Exists", "Range Does Not Exist")


"Ed Canuck" wrote:

Many thanks,

I was wracking my brains with the same problem.

Do you happen to know why IsRef doesn't equate to this? I tried to use
IsRef, but it responds True when the cell contains a number that's not a
formula.

Ed

"JMB" wrote:

copy this into a VBA code module

Function ISFormula(Target As Range)
ISFormula = Target.HasFormula
End Function


In your worksheet for Shrink, enter
=IF(ISFormula(B2),D2*.005,A2-B2)




"rcmodelr" wrote:

That would work... EXCEPT that the secondary Gross cells by default have the
formula to subtract contents of Shrink from Gross to arrive at an Estimated
Secondary gross. So until a secondary gross, or both a Gross AND tare weight
are entered, Secondary Gross is Null. Forgot to include. If No secondary
Gross is entered, the Estimated Shrink is calculated from the NET Weight (
Gross - Tare).

So right now, I have 2 spreadsheet setup files... One for when the scale at
the plant is working, and a second that figures strictly an estimated shrink
as long as the trucks need to be weighed elsewhere.

a b C D E
Gross Secondary Tare Net Shrink

Default cell contents

A Blank
B Formula to give estimated secondary gross =A2-E2
C Blank
D Formula (assuming in row 2) =A2 -C2
E. on sheet for estimated shrink =D2 * .005

Ideally, the formula for shrink should determine if secondary Gross still
has the default formula or user entered number since THAT would determine how
to calculate Shrink

If Secondary Gross is user-entered, Shrink should be Gross - Secondary Gross
(=A2-B2)
If Secondary Gross still contains default formula, Shrink should use
Estimate (=D2*.005)

If nothing else, if you know how to do this with a user defined VBA function
that could be used in the spreadsheet, tell me.

I already programmed the spreadsheet setup in VBA so to set up the sheet,
the user only has to know the lot number, farm name, how many loads, and the
catch count, and the VBA coding will set up the spreadsheet with properly
placed total lines, grand totals, and correctly format the shift related
summary page of the spreadsheet. So I'm by no means afraid to do this with
an added in VBA coded spreadsheet function to test whether B2 contains a
Formula.


"JMB" wrote:

You could set up a formula for secondary gross to test Tare to see if it is
0. If the secondary gross is known, you could key over the formula.

A B C D

Gross 2ndary Gross Tare Shrink


B2: =IF(C20,A2*(1-0.005),0)
D2: A2-B2


Or, you could set up an Estimated Secondary Gross column

A B C D
E
Gross 2ndary Gross Est 2ndaryGross Tare Shrink



C2: =IF(D20,A2*(1-0.005),0)
E2: =IF(B20,A2-B2,A2-C2)


If Tare is not numeric you can change the 0 test to "" or use
ISBLANK(D2)=FALSE. I don't know of a way (without using VBA) to test a cell
for a formula versus a user entered value.








"rcmodelr" wrote:

I have a spreadsheet to maintain truck weights.

If the weighmaster enters a Gross, and a Secondary Gross, the spreadsheet
should calculate the amount of shrink (Gross - Secondary Gross).

If the weighmaster enters ONLY a Gross and a Tare weight, the spreadsheet
should give an ESTIMATED shrink calculation, then show the result of Gross -
Estimated Shrink in the Secondary Gross column.

How can I do this??? Only way I could think of is with some way to
determine whether Secondary Gross column cell contains a USER ENTERED NUMBER,
or still has the formula to arrive at the ESTIMATED secondary weight. If
Secondary Gross has a formula, then Shrink should be Gross * .005, and
Secondary Gross should show result of Gross - Shrink.

If BOTH Gross AND Secondary Gross cells contain user entered numbers, Shrink
should contain result of Gross - Secondary Gross.

Btw... My employer has Excel 2000. So please, if you have a solution, try
to make it one I can implement in Excel 2000.

This is great!! I have been wracking my brain trying to figure out a way to
do this! This also works great with conditional formatting so I can tell
which cells have been written over with text.
Thanks!
Valerie

"JMB" wrote:

copy this into a VBA code module

Function ISFormula(Target As Range)
ISFormula = Target.HasFormula
End Function


In your worksheet for Shrink, enter
=IF(ISFormula(B2),D2*.005,A2-B2)




"rcmodelr" wrote:

That would work... EXCEPT that the secondary Gross cells by default have the
formula to subtract contents of Shrink from Gross to arrive at an Estimated
Secondary gross. So until a secondary gross, or both a Gross AND tare weight
are entered, Secondary Gross is Null. Forgot to include. If No secondary
Gross is entered, the Estimated Shrink is calculated from the NET Weight (
Gross - Tare).

So right now, I have 2 spreadsheet setup files... One for when the scale at
the plant is working, and a second that figures strictly an estimated shrink
as long as the trucks need to be weighed elsewhere.

a b C D E
Gross Secondary Tare Net Shrink

Default cell contents

A Blank
B Formula to give estimated secondary gross =A2-E2
C Blank
D Formula (assuming in row 2) =A2 -C2
E. on sheet for estimated shrink =D2 * .005

Ideally, the formula for shrink should determine if secondary Gross still
has the default formula or user entered number since THAT would determine how
to calculate Shrink

If Secondary Gross is user-entered, Shrink should be Gross - Secondary Gross
(=A2-B2)
If Secondary Gross still contains default formula, Shrink should use
Estimate (=D2*.005)

If nothing else, if you know how to do this with a user defined VBA function
that could be used in the spreadsheet, tell me.

I already programmed the spreadsheet setup in VBA so to set up the sheet,
the user only has to know the lot number, farm name, how many loads, and the
catch count, and the VBA coding will set up the spreadsheet with properly
placed total lines, grand totals, and correctly format the shift related
summary page of the spreadsheet. So I'm by no means afraid to do this with
an added in VBA coded spreadsheet function to test whether B2 contains a
Formula.


"JMB" wrote:

You could set up a formula for secondary gross to test Tare to see if it is
0. If the secondary gross is known, you could key over the formula.

A B C D

Gross 2ndary Gross Tare Shrink


B2: =IF(C20,A2*(1-0.005),0)
D2: A2-B2


Or, you could set up an Estimated Secondary Gross column

A B C D
E
Gross 2ndary Gross Est 2ndaryGross Tare Shrink



C2: =IF(D20,A2*(1-0.005),0)
E2: =IF(B20,A2-B2,A2-C2)


If Tare is not numeric you can change the 0 test to "" or use
ISBLANK(D2)=FALSE. I don't know of a way (without using VBA) to test a cell
for a formula versus a user entered value.








"rcmodelr" wrote:

I have a spreadsheet to maintain truck weights.

If the weighmaster enters a Gross, and a Secondary Gross, the spreadsheet
should calculate the amount of shrink (Gross - Secondary Gross).

If the weighmaster enters ONLY a Gross and a Tare weight, the spreadsheet
should give an ESTIMATED shrink calculation, then show the result of Gross -
Estimated Shrink in the Secondary Gross column.

How can I do this??? Only way I could think of is with some way to
determine whether Secondary Gross column cell contains a USER ENTERED NUMBER,
or still has the formula to arrive at the ESTIMATED secondary weight. If
Secondary Gross has a formula, then Shrink should be Gross * .005, and
Secondary Gross should show result of Gross - Shrink.

If BOTH Gross AND Secondary Gross cells contain user entered numbers, Shrink
should contain result of Gross - Secondary Gross.

Btw... My employer has Excel 2000. So please, if you have a solution, try
to make it one I can implement in Excel 2000.

quite welcome - glad it helped.

"Valerie" wrote:

This is great!! I have been wracking my brain trying to figure out a way to
do this! This also works great with conditional formatting so I can tell
which cells have been written over with text.
Thanks!
Valerie

"JMB" wrote:

copy this into a VBA code module

Function ISFormula(Target As Range)
ISFormula = Target.HasFormula
End Function


In your worksheet for Shrink, enter
=IF(ISFormula(B2),D2*.005,A2-B2)




"rcmodelr" wrote:

That would work... EXCEPT that the secondary Gross cells by default have the
formula to subtract contents of Shrink from Gross to arrive at an Estimated
Secondary gross. So until a secondary gross, or both a Gross AND tare weight
are entered, Secondary Gross is Null. Forgot to include. If No secondary
Gross is entered, the Estimated Shrink is calculated from the NET Weight (
Gross - Tare).

So right now, I have 2 spreadsheet setup files... One for when the scale at
the plant is working, and a second that figures strictly an estimated shrink
as long as the trucks need to be weighed elsewhere.

a b C D E
Gross Secondary Tare Net Shrink

Default cell contents

A Blank
B Formula to give estimated secondary gross =A2-E2
C Blank
D Formula (assuming in row 2) =A2 -C2
E. on sheet for estimated shrink =D2 * .005

Ideally, the formula for shrink should determine if secondary Gross still
has the default formula or user entered number since THAT would determine how
to calculate Shrink

If Secondary Gross is user-entered, Shrink should be Gross - Secondary Gross
(=A2-B2)
If Secondary Gross still contains default formula, Shrink should use
Estimate (=D2*.005)

If nothing else, if you know how to do this with a user defined VBA function
that could be used in the spreadsheet, tell me.

I already programmed the spreadsheet setup in VBA so to set up the sheet,
the user only has to know the lot number, farm name, how many loads, and the
catch count, and the VBA coding will set up the spreadsheet with properly
placed total lines, grand totals, and correctly format the shift related
summary page of the spreadsheet. So I'm by no means afraid to do this with
an added in VBA coded spreadsheet function to test whether B2 contains a
Formula.


"JMB" wrote:

You could set up a formula for secondary gross to test Tare to see if it is
0. If the secondary gross is known, you could key over the formula.

A B C D

Gross 2ndary Gross Tare Shrink


B2: =IF(C20,A2*(1-0.005),0)
D2: A2-B2


Or, you could set up an Estimated Secondary Gross column

A B C D
E
Gross 2ndary Gross Est 2ndaryGross Tare Shrink



C2: =IF(D20,A2*(1-0.005),0)
E2: =IF(B20,A2-B2,A2-C2)


If Tare is not numeric you can change the 0 test to "" or use
ISBLANK(D2)=FALSE. I don't know of a way (without using VBA) to test a cell
for a formula versus a user entered value.








"rcmodelr" wrote:

I have a spreadsheet to maintain truck weights.

If the weighmaster enters a Gross, and a Secondary Gross, the spreadsheet
should calculate the amount of shrink (Gross - Secondary Gross).

If the weighmaster enters ONLY a Gross and a Tare weight, the spreadsheet
should give an ESTIMATED shrink calculation, then show the result of Gross -
Estimated Shrink in the Secondary Gross column.

How can I do this??? Only way I could think of is with some way to
determine whether Secondary Gross column cell contains a USER ENTERED NUMBER,
or still has the formula to arrive at the ESTIMATED secondary weight. If
Secondary Gross has a formula, then Shrink should be Gross * .005, and
Secondary Gross should show result of Gross - Shrink.

If BOTH Gross AND Secondary Gross cells contain user entered numbers, Shrink
should contain result of Gross - Secondary Gross.

Btw... My employer has Excel 2000. So please, if you have a solution, try
to make it one I can implement in Excel 2000.

Thanks very much! I have also often wished for a formula-detecting formula!
Can this VBA be used to create custom formulas, so long as the function
makes sense in VBA?

Thanks, again.
Amarpas

"JMB" wrote:

quite welcome - glad it helped.

"Valerie" wrote:

This is great!! I have been wracking my brain trying to figure out a way to
do this! This also works great with conditional formatting so I can tell
which cells have been written over with text.
Thanks!
Valerie

"JMB" wrote:

copy this into a VBA code module

Function ISFormula(Target As Range)
ISFormula = Target.HasFormula
End Function


In your worksheet for Shrink, enter
=IF(ISFormula(B2),D2*.005,A2-B2)




"rcmodelr" wrote:

That would work... EXCEPT that the secondary Gross cells by default have the
formula to subtract contents of Shrink from Gross to arrive at an Estimated
Secondary gross. So until a secondary gross, or both a Gross AND tare weight
are entered, Secondary Gross is Null. Forgot to include. If No secondary
Gross is entered, the Estimated Shrink is calculated from the NET Weight (
Gross - Tare).

So right now, I have 2 spreadsheet setup files... One for when the scale at
the plant is working, and a second that figures strictly an estimated shrink
as long as the trucks need to be weighed elsewhere.

a b C D E
Gross Secondary Tare Net Shrink

Default cell contents

A Blank
B Formula to give estimated secondary gross =A2-E2
C Blank
D Formula (assuming in row 2) =A2 -C2
E. on sheet for estimated shrink =D2 * .005

Ideally, the formula for shrink should determine if secondary Gross still
has the default formula or user entered number since THAT would determine how
to calculate Shrink

If Secondary Gross is user-entered, Shrink should be Gross - Secondary Gross
(=A2-B2)
If Secondary Gross still contains default formula, Shrink should use
Estimate (=D2*.005)

If nothing else, if you know how to do this with a user defined VBA function
that could be used in the spreadsheet, tell me.

I already programmed the spreadsheet setup in VBA so to set up the sheet,
the user only has to know the lot number, farm name, how many loads, and the
catch count, and the VBA coding will set up the spreadsheet with properly
placed total lines, grand totals, and correctly format the shift related
summary page of the spreadsheet. So I'm by no means afraid to do this with
an added in VBA coded spreadsheet function to test whether B2 contains a
Formula.


"JMB" wrote:

You could set up a formula for secondary gross to test Tare to see if it is
0. If the secondary gross is known, you could key over the formula.

A B C D

Gross 2ndary Gross Tare Shrink


B2: =IF(C20,A2*(1-0.005),0)
D2: A2-B2


Or, you could set up an Estimated Secondary Gross column

A B C D
E
Gross 2ndary Gross Est 2ndaryGross Tare Shrink



C2: =IF(D20,A2*(1-0.005),0)
E2: =IF(B20,A2-B2,A2-C2)


If Tare is not numeric you can change the 0 test to "" or use
ISBLANK(D2)=FALSE. I don't know of a way (without using VBA) to test a cell
for a formula versus a user entered value.








"rcmodelr" wrote:

I have a spreadsheet to maintain truck weights.

If the weighmaster enters a Gross, and a Secondary Gross, the spreadsheet
should calculate the amount of shrink (Gross - Secondary Gross).

If the weighmaster enters ONLY a Gross and a Tare weight, the spreadsheet
should give an ESTIMATED shrink calculation, then show the result of Gross -
Estimated Shrink in the Secondary Gross column.

How can I do this??? Only way I could think of is with some way to
determine whether Secondary Gross column cell contains a USER ENTERED NUMBER,
or still has the formula to arrive at the ESTIMATED secondary weight. If
Secondary Gross has a formula, then Shrink should be Gross * .005, and
Secondary Gross should show result of Gross - Shrink.

If BOTH Gross AND Secondary Gross cells contain user entered numbers, Shrink
should contain result of Gross - Secondary Gross.

Btw... My employer has Excel 2000. So please, if you have a solution, try
to make it one I can implement in Excel 2000.

Thanks very much! I have also often wished for a formula-detecting
formula!
Can this VBA be used to create custom formulas, so long as the function
makes sense in VBA?

Yes, you can create a formula-detecting formula using VBA. In the VBA
editor, click Insert/Module and copy/paste this code into the code window
that appears...

Function CellHasFormula(CellReference As Range) As Boolean
CellHasFormula = CellReference.HasFormula
End Function

You can now use CellHasFormula just like any other worksheet function. For
example...

=IF(CellHasFormula(A1),"Yes","No")

Rick