How to correct a "#VALUE!"
 

I just completed a workbook (two worksheet) with help from some very nice
group members.
Now I want to create a master template. In the user input cells,I deleted
the data used to create the workbook in the first place. Now where the
answers should be I get this "#VALUE!" and one of the cell has a "#DIV/0" and
other cells that has the same formula, shows a "0" which I don't mind. Why
would I get this answer in some cells and not in other cells.
Also I would like to lock this workbook to prevent changes of the formulas
by other staff members and still allowing them to use the workbook.
Can someone help with my request?

Many thanks in advance.
Serge

How to correct a "#VALUE!"
 

You should have copied the answers and "paste special" with "values" to
maintain the answers. By deleting the old data, you removed the references.

"Serge" wrote:

I just completed a workbook (two worksheet) with help from some very nice
group members.
Now I want to create a master template. In the user input cells,I deleted
the data used to create the workbook in the first place. Now where the
answers should be I get this "#VALUE!" and one of the cell has a "#DIV/0" and
other cells that has the same formula, shows a "0" which I don't mind. Why
would I get this answer in some cells and not in other cells.
Also I would like to lock this workbook to prevent changes of the formulas
by other staff members and still allowing them to use the workbook.
Can someone help with my request?

Many thanks in advance.
Serge

How to correct a "#VALUE!"
 

Hello Jshendel,

I tried your suggestion but somehow I not getting the result I should. All
it shows is copied value of a cell, not the formula that was in that previous
cell.

"Jshendel" wrote:

You should have copied the answers and "paste special" with "values" to
maintain the answers. By deleting the old data, you removed the references.

"Serge" wrote:

I just completed a workbook (two worksheet) with help from some very nice
group members.
Now I want to create a master template. In the user input cells,I deleted
the data used to create the workbook in the first place. Now where the
answers should be I get this "#VALUE!" and one of the cell has a "#DIV/0" and
other cells that has the same formula, shows a "0" which I don't mind. Why
would I get this answer in some cells and not in other cells.
Also I would like to lock this workbook to prevent changes of the formulas
by other staff members and still allowing them to use the workbook.
Can someone help with my request?

Many thanks in advance.
Serge

How to correct a "#VALUE!"
 

Just a guess, but if you had "deleted" the data by hitting the spacebar, then
you may see #Value! errors for formulas that refer to that cell.

And if you empty a cell or make it equal 0 and then try to divide by that cell,
you'll get the #Div/0! error.

I think I'd be more careful "deleting" the data and use a different formula:

=if(a1=0,"",b1/a1)
or
=if(iserror(b1/a1),"",b1/a1)

If you really want to cheat (I wouldn't use this technique), you could apply
format|conditional formatting to make it look like the cells that evaluate to
errors are empty.

With B1 the activecell in the selected cells
Format|conditional formatting
formula is: =ISERROR(B1)
make it a white font on a white fill (or something like that)

And you can select any cells you want, just write the formula using the
activecell.




Serge wrote:

I just completed a workbook (two worksheet) with help from some very nice
group members.
Now I want to create a master template. In the user input cells,I deleted
the data used to create the workbook in the first place. Now where the
answers should be I get this "#VALUE!" and one of the cell has a "#DIV/0" and
other cells that has the same formula, shows a "0" which I don't mind. Why
would I get this answer in some cells and not in other cells.
Also I would like to lock this workbook to prevent changes of the formulas
by other staff members and still allowing them to use the workbook.
Can someone help with my request?

Many thanks in advance.
Serge

--

Dave Peterson

How to correct a "#VALUE!"
 

Hello Dave,
In H27 the answer is #DIV/0! Because now the cells I10, I37 & G29 are empty.
H27=DEGREES(ATAN2((I10-(I37*0.0833))/2,G29))

In C46 the answer is #VALUE! Because C18 & C32 are empty.
C46=SUM(C18+C32)

Other similar cells have the same situation.
Eventually those cell will get data input.

"Dave Peterson" wrote:

Just a guess, but if you had "deleted" the data by hitting the spacebar, then
you may see #Value! errors for formulas that refer to that cell.

And if you empty a cell or make it equal 0 and then try to divide by that cell,
you'll get the #Div/0! error.

I think I'd be more careful "deleting" the data and use a different formula:

=if(a1=0,"",b1/a1)
or
=if(iserror(b1/a1),"",b1/a1)

If you really want to cheat (I wouldn't use this technique), you could apply
format|conditional formatting to make it look like the cells that evaluate to
errors are empty.

With B1 the activecell in the selected cells
Format|conditional formatting
formula is: =ISERROR(B1)
make it a white font on a white fill (or something like that)

And you can select any cells you want, just write the formula using the
activecell.




Serge wrote:

I just completed a workbook (two worksheet) with help from some very nice
group members.
Now I want to create a master template. In the user input cells,I deleted
the data used to create the workbook in the first place. Now where the
answers should be I get this "#VALUE!" and one of the cell has a "#DIV/0" and
other cells that has the same formula, shows a "0" which I don't mind. Why
would I get this answer in some cells and not in other cells.
Also I would like to lock this workbook to prevent changes of the formulas
by other staff members and still allowing them to use the workbook.
Can someone help with my request?

Many thanks in advance.
Serge

--

Dave Peterson

How to correct a "#VALUE!"
 

=sum(c18+c32)
could be replaced with
=sum(c18,c32)

But if =sum(c18+c32) returned an error, then I don't think c18 and c32 are
really both empty.


And I would expect G29 being empty as the real problem with the first formula.



Serge wrote:

Hello Dave,
In H27 the answer is #DIV/0! Because now the cells I10, I37 & G29 are empty.
H27=DEGREES(ATAN2((I10-(I37*0.0833))/2,G29))

In C46 the answer is #VALUE! Because C18 & C32 are empty.
C46=SUM(C18+C32)

Other similar cells have the same situation.
Eventually those cell will get data input.

"Dave Peterson" wrote:

Just a guess, but if you had "deleted" the data by hitting the spacebar, then
you may see #Value! errors for formulas that refer to that cell.

And if you empty a cell or make it equal 0 and then try to divide by that cell,
you'll get the #Div/0! error.

I think I'd be more careful "deleting" the data and use a different formula:

=if(a1=0,"",b1/a1)
or
=if(iserror(b1/a1),"",b1/a1)

If you really want to cheat (I wouldn't use this technique), you could apply
format|conditional formatting to make it look like the cells that evaluate to
errors are empty.

With B1 the activecell in the selected cells
Format|conditional formatting
formula is: =ISERROR(B1)
make it a white font on a white fill (or something like that)

And you can select any cells you want, just write the formula using the
activecell.




Serge wrote:

I just completed a workbook (two worksheet) with help from some very nice
group members.
Now I want to create a master template. In the user input cells,I deleted
the data used to create the workbook in the first place. Now where the
answers should be I get this "#VALUE!" and one of the cell has a "#DIV/0" and
other cells that has the same formula, shows a "0" which I don't mind. Why
would I get this answer in some cells and not in other cells.
Also I would like to lock this workbook to prevent changes of the formulas
by other staff members and still allowing them to use the workbook.
Can someone help with my request?

Many thanks in advance.
Serge

--

Dave Peterson


--

Dave Peterson

How to correct a "#VALUE!"
 

Hello again Dave,
I tried the comma, no luck.


In C18=C17*0.90718
In C17=IF(ISNA(VLOOKUP(B7,R9:S16,2,0)),"",VLOOKUP(B7, R9:S16,2,0)*C13/2000)
In C32=C31*0.90718
In C31=IF(ISNA(VLOOKUP(B7,R9:S16,2,0)),"",VLOOKUP(B7, R9:S16,2,0)*C27/2000)
Everything works well as long there data in the correct cells or changing
data in the user input cell.
But to start with a "blank" sheet ready for user input I needed to empty
those particular cells.

"Dave Peterson" wrote:

=sum(c18+c32)
could be replaced with
=sum(c18,c32)

But if =sum(c18+c32) returned an error, then I don't think c18 and c32 are
really both empty.


And I would expect G29 being empty as the real problem with the first formula.



Serge wrote:

Hello Dave,
In H27 the answer is #DIV/0! Because now the cells I10, I37 & G29 are empty.
H27=DEGREES(ATAN2((I10-(I37*0.0833))/2,G29))

In C46 the answer is #VALUE! Because C18 & C32 are empty.
C46=SUM(C18+C32)

Other similar cells have the same situation.
Eventually those cell will get data input.

"Dave Peterson" wrote:

Just a guess, but if you had "deleted" the data by hitting the spacebar, then
you may see #Value! errors for formulas that refer to that cell.

And if you empty a cell or make it equal 0 and then try to divide by that cell,
you'll get the #Div/0! error.

I think I'd be more careful "deleting" the data and use a different formula:

=if(a1=0,"",b1/a1)
or
=if(iserror(b1/a1),"",b1/a1)

If you really want to cheat (I wouldn't use this technique), you could apply
format|conditional formatting to make it look like the cells that evaluate to
errors are empty.

With B1 the activecell in the selected cells
Format|conditional formatting
formula is: =ISERROR(B1)
make it a white font on a white fill (or something like that)

And you can select any cells you want, just write the formula using the
activecell.




Serge wrote:

I just completed a workbook (two worksheet) with help from some very nice
group members.
Now I want to create a master template. In the user input cells,I deleted
the data used to create the workbook in the first place. Now where the
answers should be I get this "#VALUE!" and one of the cell has a "#DIV/0" and
other cells that has the same formula, shows a "0" which I don't mind. Why
would I get this answer in some cells and not in other cells.
Also I would like to lock this workbook to prevent changes of the formulas
by other staff members and still allowing them to use the workbook.
Can someone help with my request?

Many thanks in advance.
Serge

--

Dave Peterson


--

Dave Peterson

How to correct a "#VALUE!"
 

C18 contains a formula that looks at C17. If c17 = "", then c18 will be the
value error.

I'd use:
=if(iserror(yourformula),"",yourformula)

But if c18 and c32 both evaluated to "", then
=sum(c18,c32)
returned 0 for me.



Serge wrote:

Hello again Dave,
I tried the comma, no luck.

In C18=C17*0.90718
In C17=IF(ISNA(VLOOKUP(B7,R9:S16,2,0)),"",VLOOKUP(B7, R9:S16,2,0)*C13/2000)
In C32=C31*0.90718
In C31=IF(ISNA(VLOOKUP(B7,R9:S16,2,0)),"",VLOOKUP(B7, R9:S16,2,0)*C27/2000)
Everything works well as long there data in the correct cells or changing
data in the user input cell.
But to start with a "blank" sheet ready for user input I needed to empty
those particular cells.

"Dave Peterson" wrote:

=sum(c18+c32)
could be replaced with
=sum(c18,c32)

But if =sum(c18+c32) returned an error, then I don't think c18 and c32 are
really both empty.


And I would expect G29 being empty as the real problem with the first formula.



Serge wrote:

Hello Dave,
In H27 the answer is #DIV/0! Because now the cells I10, I37 & G29 are empty.
H27=DEGREES(ATAN2((I10-(I37*0.0833))/2,G29))

In C46 the answer is #VALUE! Because C18 & C32 are empty.
C46=SUM(C18+C32)

Other similar cells have the same situation.
Eventually those cell will get data input.

"Dave Peterson" wrote:

Just a guess, but if you had "deleted" the data by hitting the spacebar, then
you may see #Value! errors for formulas that refer to that cell.

And if you empty a cell or make it equal 0 and then try to divide by that cell,
you'll get the #Div/0! error.

I think I'd be more careful "deleting" the data and use a different formula:

=if(a1=0,"",b1/a1)
or
=if(iserror(b1/a1),"",b1/a1)

If you really want to cheat (I wouldn't use this technique), you could apply
format|conditional formatting to make it look like the cells that evaluate to
errors are empty.

With B1 the activecell in the selected cells
Format|conditional formatting
formula is: =ISERROR(B1)
make it a white font on a white fill (or something like that)

And you can select any cells you want, just write the formula using the
activecell.




Serge wrote:

I just completed a workbook (two worksheet) with help from some very nice
group members.
Now I want to create a master template. In the user input cells,I deleted
the data used to create the workbook in the first place. Now where the
answers should be I get this "#VALUE!" and one of the cell has a "#DIV/0" and
other cells that has the same formula, shows a "0" which I don't mind. Why
would I get this answer in some cells and not in other cells.
Also I would like to lock this workbook to prevent changes of the formulas
by other staff members and still allowing them to use the workbook.
Can someone help with my request?

Many thanks in advance.
Serge

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

How to correct a "#VALUE!"
 

Hello Dave,
I'm very confused at the moment, I have been busy at this since your last
reply.
I'm having difficulty with resolving C18 & C32.

"Dave Peterson" wrote:

C18 contains a formula that looks at C17. If c17 = "", then c18 will be the
value error.

I'd use:
=if(iserror(yourformula),"",yourformula)

But if c18 and c32 both evaluated to "", then
=sum(c18,c32)
returned 0 for me.



Serge wrote:

Hello again Dave,
I tried the comma, no luck.

In C18=C17*0.90718
In C17=IF(ISNA(VLOOKUP(B7,R9:S16,2,0)),"",VLOOKUP(B7, R9:S16,2,0)*C13/2000)
In C32=C31*0.90718
In C31=IF(ISNA(VLOOKUP(B7,R9:S16,2,0)),"",VLOOKUP(B7, R9:S16,2,0)*C27/2000)
Everything works well as long there data in the correct cells or changing
data in the user input cell.
But to start with a "blank" sheet ready for user input I needed to empty
those particular cells.

"Dave Peterson" wrote:

=sum(c18+c32)
could be replaced with
=sum(c18,c32)

But if =sum(c18+c32) returned an error, then I don't think c18 and c32 are
really both empty.


And I would expect G29 being empty as the real problem with the first formula.



Serge wrote:

Hello Dave,
In H27 the answer is #DIV/0! Because now the cells I10, I37 & G29 are empty.
H27=DEGREES(ATAN2((I10-(I37*0.0833))/2,G29))

In C46 the answer is #VALUE! Because C18 & C32 are empty.
C46=SUM(C18+C32)

Other similar cells have the same situation.
Eventually those cell will get data input.

"Dave Peterson" wrote:

Just a guess, but if you had "deleted" the data by hitting the spacebar, then
you may see #Value! errors for formulas that refer to that cell.

And if you empty a cell or make it equal 0 and then try to divide by that cell,
you'll get the #Div/0! error.

I think I'd be more careful "deleting" the data and use a different formula:

=if(a1=0,"",b1/a1)
or
=if(iserror(b1/a1),"",b1/a1)

If you really want to cheat (I wouldn't use this technique), you could apply
format|conditional formatting to make it look like the cells that evaluate to
errors are empty.

With B1 the activecell in the selected cells
Format|conditional formatting
formula is: =ISERROR(B1)
make it a white font on a white fill (or something like that)

And you can select any cells you want, just write the formula using the
activecell.




Serge wrote:

I just completed a workbook (two worksheet) with help from some very nice
group members.
Now I want to create a master template. In the user input cells,I deleted
the data used to create the workbook in the first place. Now where the
answers should be I get this "#VALUE!" and one of the cell has a "#DIV/0" and
other cells that has the same formula, shows a "0" which I don't mind. Why
would I get this answer in some cells and not in other cells.
Also I would like to lock this workbook to prevent changes of the formulas
by other staff members and still allowing them to use the workbook.
Can someone help with my request?

Many thanks in advance.
Serge

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

How to correct a "#VALUE!"
 

With no other information, I don't have another suggestion.

Serge wrote:

Hello Dave,
I'm very confused at the moment, I have been busy at this since your last
reply.
I'm having difficulty with resolving C18 & C32.

"Dave Peterson" wrote:

C18 contains a formula that looks at C17. If c17 = "", then c18 will be the
value error.

I'd use:
=if(iserror(yourformula),"",yourformula)

But if c18 and c32 both evaluated to "", then
=sum(c18,c32)
returned 0 for me.



Serge wrote:

Hello again Dave,
I tried the comma, no luck.

In C18=C17*0.90718
In C17=IF(ISNA(VLOOKUP(B7,R9:S16,2,0)),"",VLOOKUP(B7, R9:S16,2,0)*C13/2000)
In C32=C31*0.90718
In C31=IF(ISNA(VLOOKUP(B7,R9:S16,2,0)),"",VLOOKUP(B7, R9:S16,2,0)*C27/2000)
Everything works well as long there data in the correct cells or changing
data in the user input cell.
But to start with a "blank" sheet ready for user input I needed to empty
those particular cells.

"Dave Peterson" wrote:

=sum(c18+c32)
could be replaced with
=sum(c18,c32)

But if =sum(c18+c32) returned an error, then I don't think c18 and c32 are
really both empty.


And I would expect G29 being empty as the real problem with the first formula.



Serge wrote:

Hello Dave,
In H27 the answer is #DIV/0! Because now the cells I10, I37 & G29 are empty.
H27=DEGREES(ATAN2((I10-(I37*0.0833))/2,G29))

In C46 the answer is #VALUE! Because C18 & C32 are empty.
C46=SUM(C18+C32)

Other similar cells have the same situation.
Eventually those cell will get data input.

"Dave Peterson" wrote:

Just a guess, but if you had "deleted" the data by hitting the spacebar, then
you may see #Value! errors for formulas that refer to that cell.

And if you empty a cell or make it equal 0 and then try to divide by that cell,
you'll get the #Div/0! error.

I think I'd be more careful "deleting" the data and use a different formula:

=if(a1=0,"",b1/a1)
or
=if(iserror(b1/a1),"",b1/a1)

If you really want to cheat (I wouldn't use this technique), you could apply
format|conditional formatting to make it look like the cells that evaluate to
errors are empty.

With B1 the activecell in the selected cells
Format|conditional formatting
formula is: =ISERROR(B1)
make it a white font on a white fill (or something like that)

And you can select any cells you want, just write the formula using the
activecell.




Serge wrote:

I just completed a workbook (two worksheet) with help from some very nice
group members.
Now I want to create a master template. In the user input cells,I deleted
the data used to create the workbook in the first place. Now where the
answers should be I get this "#VALUE!" and one of the cell has a "#DIV/0" and
other cells that has the same formula, shows a "0" which I don't mind. Why
would I get this answer in some cells and not in other cells.
Also I would like to lock this workbook to prevent changes of the formulas
by other staff members and still allowing them to use the workbook.
Can someone help with my request?

Many thanks in advance.
Serge

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

How to correct a "#VALUE!"
 

Good morning Dave,
It's 7am, I will make an affort to work with the information you already
given me. I'm in better frame pf mind now.
Thanks for all you have done
Serge

"Dave Peterson" wrote:

With no other information, I don't have another suggestion.

Serge wrote:

Hello Dave,
I'm very confused at the moment, I have been busy at this since your last
reply.
I'm having difficulty with resolving C18 & C32.

"Dave Peterson" wrote:

C18 contains a formula that looks at C17. If c17 = "", then c18 will be the
value error.

I'd use:
=if(iserror(yourformula),"",yourformula)

But if c18 and c32 both evaluated to "", then
=sum(c18,c32)
returned 0 for me.



Serge wrote:

Hello again Dave,
I tried the comma, no luck.

In C18=C17*0.90718
In C17=IF(ISNA(VLOOKUP(B7,R9:S16,2,0)),"",VLOOKUP(B7, R9:S16,2,0)*C13/2000)
In C32=C31*0.90718
In C31=IF(ISNA(VLOOKUP(B7,R9:S16,2,0)),"",VLOOKUP(B7, R9:S16,2,0)*C27/2000)
Everything works well as long there data in the correct cells or changing
data in the user input cell.
But to start with a "blank" sheet ready for user input I needed to empty
those particular cells.

"Dave Peterson" wrote:

=sum(c18+c32)
could be replaced with
=sum(c18,c32)

But if =sum(c18+c32) returned an error, then I don't think c18 and c32 are
really both empty.


And I would expect G29 being empty as the real problem with the first formula.



Serge wrote:

Hello Dave,
In H27 the answer is #DIV/0! Because now the cells I10, I37 & G29 are empty.
H27=DEGREES(ATAN2((I10-(I37*0.0833))/2,G29))

In C46 the answer is #VALUE! Because C18 & C32 are empty.
C46=SUM(C18+C32)

Other similar cells have the same situation.
Eventually those cell will get data input.

"Dave Peterson" wrote:

Just a guess, but if you had "deleted" the data by hitting the spacebar, then
you may see #Value! errors for formulas that refer to that cell.

And if you empty a cell or make it equal 0 and then try to divide by that cell,
you'll get the #Div/0! error.

I think I'd be more careful "deleting" the data and use a different formula:

=if(a1=0,"",b1/a1)
or
=if(iserror(b1/a1),"",b1/a1)

If you really want to cheat (I wouldn't use this technique), you could apply
format|conditional formatting to make it look like the cells that evaluate to
errors are empty.

With B1 the activecell in the selected cells
Format|conditional formatting
formula is: =ISERROR(B1)
make it a white font on a white fill (or something like that)

And you can select any cells you want, just write the formula using the
activecell.




Serge wrote:

I just completed a workbook (two worksheet) with help from some very nice
group members.
Now I want to create a master template. In the user input cells,I deleted
the data used to create the workbook in the first place. Now where the
answers should be I get this "#VALUE!" and one of the cell has a "#DIV/0" and
other cells that has the same formula, shows a "0" which I don't mind. Why
would I get this answer in some cells and not in other cells.
Also I would like to lock this workbook to prevent changes of the formulas
by other staff members and still allowing them to use the workbook.
Can someone help with my request?

Many thanks in advance.
Serge

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson