software

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 quot;#VALUE!quot; and one of the cell has a quot;#DIV/0quot; and
other cells that has the same formula, shows a quot;0quot; 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

You should have copied the answers and quot;paste specialquot; with quot;valuesquot; to
maintain the answers. By deleting the old data, you removed the references.

quot;Sergequot; wrote:

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

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.

quot;Jshendelquot; wrote:

gt; You should have copied the answers and quot;paste specialquot; with quot;valuesquot; to
gt; maintain the answers. By deleting the old data, you removed the references.
gt;
gt; quot;Sergequot; wrote:
gt;
gt; gt; I just completed a workbook (two worksheet) with help from some very nice
gt; gt; group members.
gt; gt; Now I want to create a master template. In the user input cells,I deleted
gt; gt; the data used to create the workbook in the first place. Now where the
gt; gt; answers should be I get this quot;#VALUE!quot; and one of the cell has a quot;#DIV/0quot; and
gt; gt; other cells that has the same formula, shows a quot;0quot; which I don't mind. Why
gt; gt; would I get this answer in some cells and not in other cells.
gt; gt; Also I would like to lock this workbook to prevent changes of the formulas
gt; gt; by other staff members and still allowing them to use the workbook.
gt; gt; Can someone help with my request?
gt; gt;
gt; gt; Many thanks in advance.
gt; gt; Serge

Just a guess, but if you had quot;deletedquot; 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 quot;deletingquot; the data and use a different formula:

=if(a1=0,quot;quot;,b1/a1)
or
=if(iserror(b1/a1),quot;quot;,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:
gt;
gt; I just completed a workbook (two worksheet) with help from some very nice
gt; group members.
gt; Now I want to create a master template. In the user input cells,I deleted
gt; the data used to create the workbook in the first place. Now where the
gt; answers should be I get this quot;#VALUE!quot; and one of the cell has a quot;#DIV/0quot; and
gt; other cells that has the same formula, shows a quot;0quot; which I don't mind. Why
gt; would I get this answer in some cells and not in other cells.
gt; Also I would like to lock this workbook to prevent changes of the formulas
gt; by other staff members and still allowing them to use the workbook.
gt; Can someone help with my request?
gt;
gt; Many thanks in advance.
gt; Serge

--

Dave Peterson

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

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

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

quot;Dave Petersonquot; wrote:

gt; Just a guess, but if you had quot;deletedquot; the data by hitting the spacebar, then
gt; you may see #Value! errors for formulas that refer to that cell.
gt;
gt; And if you empty a cell or make it equal 0 and then try to divide by that cell,
gt; you'll get the #Div/0! error.
gt;
gt; I think I'd be more careful quot;deletingquot; the data and use a different formula:
gt;
gt; =if(a1=0,quot;quot;,b1/a1)
gt; or
gt; =if(iserror(b1/a1),quot;quot;,b1/a1)
gt;
gt; If you really want to cheat (I wouldn't use this technique), you could apply
gt; format|conditional formatting to make it look like the cells that evaluate to
gt; errors are empty.
gt;
gt; With B1 the activecell in the selected cells
gt; Format|conditional formatting
gt; formula is: =ISERROR(B1)
gt; make it a white font on a white fill (or something like that)
gt;
gt; And you can select any cells you want, just write the formula using the
gt; activecell.
gt;
gt;
gt;
gt;
gt; Serge wrote:
gt; gt;
gt; gt; I just completed a workbook (two worksheet) with help from some very nice
gt; gt; group members.
gt; gt; Now I want to create a master template. In the user input cells,I deleted
gt; gt; the data used to create the workbook in the first place. Now where the
gt; gt; answers should be I get this quot;#VALUE!quot; and one of the cell has a quot;#DIV/0quot; and
gt; gt; other cells that has the same formula, shows a quot;0quot; which I don't mind. Why
gt; gt; would I get this answer in some cells and not in other cells.
gt; gt; Also I would like to lock this workbook to prevent changes of the formulas
gt; gt; by other staff members and still allowing them to use the workbook.
gt; gt; Can someone help with my request?
gt; gt;
gt; gt; Many thanks in advance.
gt; gt; Serge
gt;
gt; --
gt;
gt; Dave Peterson
gt;

=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:
gt;
gt; Hello Dave,
gt; In H27 the answer is #DIV/0! Because now the cells I10, I37 amp; G29 are empty.
gt; H27=DEGREES(ATAN2((I10-(I37*0.0833))/2,G29))
gt;
gt; In C46 the answer is #VALUE! Because C18 amp; C32 are empty.
gt; C46=SUM(C18 C32)
gt;
gt; Other similar cells have the same situation.
gt; Eventually those cell will get data input.
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; Just a guess, but if you had quot;deletedquot; the data by hitting the spacebar, then
gt; gt; you may see #Value! errors for formulas that refer to that cell.
gt; gt;
gt; gt; And if you empty a cell or make it equal 0 and then try to divide by that cell,
gt; gt; you'll get the #Div/0! error.
gt; gt;
gt; gt; I think I'd be more careful quot;deletingquot; the data and use a different formula:
gt; gt;
gt; gt; =if(a1=0,quot;quot;,b1/a1)
gt; gt; or
gt; gt; =if(iserror(b1/a1),quot;quot;,b1/a1)
gt; gt;
gt; gt; If you really want to cheat (I wouldn't use this technique), you could apply
gt; gt; format|conditional formatting to make it look like the cells that evaluate to
gt; gt; errors are empty.
gt; gt;
gt; gt; With B1 the activecell in the selected cells
gt; gt; Format|conditional formatting
gt; gt; formula is: =ISERROR(B1)
gt; gt; make it a white font on a white fill (or something like that)
gt; gt;
gt; gt; And you can select any cells you want, just write the formula using the
gt; gt; activecell.
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; Serge wrote:
gt; gt; gt;
gt; gt; gt; I just completed a workbook (two worksheet) with help from some very nice
gt; gt; gt; group members.
gt; gt; gt; Now I want to create a master template. In the user input cells,I deleted
gt; gt; gt; the data used to create the workbook in the first place. Now where the
gt; gt; gt; answers should be I get this quot;#VALUE!quot; and one of the cell has a quot;#DIV/0quot; and
gt; gt; gt; other cells that has the same formula, shows a quot;0quot; which I don't mind. Why
gt; gt; gt; would I get this answer in some cells and not in other cells.
gt; gt; gt; Also I would like to lock this workbook to prevent changes of the formulas
gt; gt; gt; by other staff members and still allowing them to use the workbook.
gt; gt; gt; Can someone help with my request?
gt; gt; gt;
gt; gt; gt; Many thanks in advance.
gt; gt; gt; Serge
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

Hello again Dave,
I tried the comma, no luck.In C18=C17*0.90718
In C17=IF(ISNA(VLOOKUP(B7,R9:S16,2,0)),quot;quot;,VLOOKUP(B7, R9:S16,2,0)*C13/2000)
In C32=C31*0.90718
In C31=IF(ISNA(VLOOKUP(B7,R9:S16,2,0)),quot;quot;,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 quot;blankquot; sheet ready for user input I needed to empty
those particular cells.

quot;Dave Petersonquot; wrote:

gt; =sum(c18 c32)
gt; could be replaced with
gt; =sum(c18,c32)
gt;
gt; But if =sum(c18 c32) returned an error, then I don't think c18 and c32 are
gt; really both empty.
gt;
gt;
gt; And I would expect G29 being empty as the real problem with the first formula.
gt;
gt;
gt;
gt; Serge wrote:
gt; gt;
gt; gt; Hello Dave,
gt; gt; In H27 the answer is #DIV/0! Because now the cells I10, I37 amp; G29 are empty.
gt; gt; H27=DEGREES(ATAN2((I10-(I37*0.0833))/2,G29))
gt; gt;
gt; gt; In C46 the answer is #VALUE! Because C18 amp; C32 are empty.
gt; gt; C46=SUM(C18 C32)
gt; gt;
gt; gt; Other similar cells have the same situation.
gt; gt; Eventually those cell will get data input.
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; Just a guess, but if you had quot;deletedquot; the data by hitting the spacebar, then
gt; gt; gt; you may see #Value! errors for formulas that refer to that cell.
gt; gt; gt;
gt; gt; gt; And if you empty a cell or make it equal 0 and then try to divide by that cell,
gt; gt; gt; you'll get the #Div/0! error.
gt; gt; gt;
gt; gt; gt; I think I'd be more careful quot;deletingquot; the data and use a different formula:
gt; gt; gt;
gt; gt; gt; =if(a1=0,quot;quot;,b1/a1)
gt; gt; gt; or
gt; gt; gt; =if(iserror(b1/a1),quot;quot;,b1/a1)
gt; gt; gt;
gt; gt; gt; If you really want to cheat (I wouldn't use this technique), you could apply
gt; gt; gt; format|conditional formatting to make it look like the cells that evaluate to
gt; gt; gt; errors are empty.
gt; gt; gt;
gt; gt; gt; With B1 the activecell in the selected cells
gt; gt; gt; Format|conditional formatting
gt; gt; gt; formula is: =ISERROR(B1)
gt; gt; gt; make it a white font on a white fill (or something like that)
gt; gt; gt;
gt; gt; gt; And you can select any cells you want, just write the formula using the
gt; gt; gt; activecell.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Serge wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; I just completed a workbook (two worksheet) with help from some very nice
gt; gt; gt; gt; group members.
gt; gt; gt; gt; Now I want to create a master template. In the user input cells,I deleted
gt; gt; gt; gt; the data used to create the workbook in the first place. Now where the
gt; gt; gt; gt; answers should be I get this quot;#VALUE!quot; and one of the cell has a quot;#DIV/0quot; and
gt; gt; gt; gt; other cells that has the same formula, shows a quot;0quot; which I don't mind. Why
gt; gt; gt; gt; would I get this answer in some cells and not in other cells.
gt; gt; gt; gt; Also I would like to lock this workbook to prevent changes of the formulas
gt; gt; gt; gt; by other staff members and still allowing them to use the workbook.
gt; gt; gt; gt; Can someone help with my request?
gt; gt; gt; gt;
gt; gt; gt; gt; Many thanks in advance.
gt; gt; gt; gt; Serge
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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

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

But if c18 and c32 both evaluated to quot;quot;, then
=sum(c18,c32)
returned 0 for me.
Serge wrote:
gt;
gt; Hello again Dave,
gt; I tried the comma, no luck.
gt;
gt; In C18=C17*0.90718
gt; In C17=IF(ISNA(VLOOKUP(B7,R9:S16,2,0)),quot;quot;,VLOOKUP(B7, R9:S16,2,0)*C13/2000)
gt; In C32=C31*0.90718
gt; In C31=IF(ISNA(VLOOKUP(B7,R9:S16,2,0)),quot;quot;,VLOOKUP(B7, R9:S16,2,0)*C27/2000)
gt; Everything works well as long there data in the correct cells or changing
gt; data in the user input cell.
gt; But to start with a quot;blankquot; sheet ready for user input I needed to empty
gt; those particular cells.
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; =sum(c18 c32)
gt; gt; could be replaced with
gt; gt; =sum(c18,c32)
gt; gt;
gt; gt; But if =sum(c18 c32) returned an error, then I don't think c18 and c32 are
gt; gt; really both empty.
gt; gt;
gt; gt;
gt; gt; And I would expect G29 being empty as the real problem with the first formula.
gt; gt;
gt; gt;
gt; gt;
gt; gt; Serge wrote:
gt; gt; gt;
gt; gt; gt; Hello Dave,
gt; gt; gt; In H27 the answer is #DIV/0! Because now the cells I10, I37 amp; G29 are empty.
gt; gt; gt; H27=DEGREES(ATAN2((I10-(I37*0.0833))/2,G29))
gt; gt; gt;
gt; gt; gt; In C46 the answer is #VALUE! Because C18 amp; C32 are empty.
gt; gt; gt; C46=SUM(C18 C32)
gt; gt; gt;
gt; gt; gt; Other similar cells have the same situation.
gt; gt; gt; Eventually those cell will get data input.
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Just a guess, but if you had quot;deletedquot; the data by hitting the spacebar, then
gt; gt; gt; gt; you may see #Value! errors for formulas that refer to that cell.
gt; gt; gt; gt;
gt; gt; gt; gt; And if you empty a cell or make it equal 0 and then try to divide by that cell,
gt; gt; gt; gt; you'll get the #Div/0! error.
gt; gt; gt; gt;
gt; gt; gt; gt; I think I'd be more careful quot;deletingquot; the data and use a different formula:
gt; gt; gt; gt;
gt; gt; gt; gt; =if(a1=0,quot;quot;,b1/a1)
gt; gt; gt; gt; or
gt; gt; gt; gt; =if(iserror(b1/a1),quot;quot;,b1/a1)
gt; gt; gt; gt;
gt; gt; gt; gt; If you really want to cheat (I wouldn't use this technique), you could apply
gt; gt; gt; gt; format|conditional formatting to make it look like the cells that evaluate to
gt; gt; gt; gt; errors are empty.
gt; gt; gt; gt;
gt; gt; gt; gt; With B1 the activecell in the selected cells
gt; gt; gt; gt; Format|conditional formatting
gt; gt; gt; gt; formula is: =ISERROR(B1)
gt; gt; gt; gt; make it a white font on a white fill (or something like that)
gt; gt; gt; gt;
gt; gt; gt; gt; And you can select any cells you want, just write the formula using the
gt; gt; gt; gt; activecell.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Serge wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I just completed a workbook (two worksheet) with help from some very nice
gt; gt; gt; gt; gt; group members.
gt; gt; gt; gt; gt; Now I want to create a master template. In the user input cells,I deleted
gt; gt; gt; gt; gt; the data used to create the workbook in the first place. Now where the
gt; gt; gt; gt; gt; answers should be I get this quot;#VALUE!quot; and one of the cell has a quot;#DIV/0quot; and
gt; gt; gt; gt; gt; other cells that has the same formula, shows a quot;0quot; which I don't mind. Why
gt; gt; gt; gt; gt; would I get this answer in some cells and not in other cells.
gt; gt; gt; gt; gt; Also I would like to lock this workbook to prevent changes of the formulas
gt; gt; gt; gt; gt; by other staff members and still allowing them to use the workbook.
gt; gt; gt; gt; gt; Can someone help with my request?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Many thanks in advance.
gt; gt; gt; gt; gt; Serge
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

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 amp; C32.

quot;Dave Petersonquot; wrote:

gt; C18 contains a formula that looks at C17. If c17 = quot;quot;, then c18 will be the
gt; value error.
gt;
gt; I'd use:
gt; =if(iserror(yourformula),quot;quot;,yourformula)
gt;
gt; But if c18 and c32 both evaluated to quot;quot;, then
gt; =sum(c18,c32)
gt; returned 0 for me.
gt;
gt;
gt;
gt; Serge wrote:
gt; gt;
gt; gt; Hello again Dave,
gt; gt; I tried the comma, no luck.
gt; gt;
gt; gt; In C18=C17*0.90718
gt; gt; In C17=IF(ISNA(VLOOKUP(B7,R9:S16,2,0)),quot;quot;,VLOOKUP(B7, R9:S16,2,0)*C13/2000)
gt; gt; In C32=C31*0.90718
gt; gt; In C31=IF(ISNA(VLOOKUP(B7,R9:S16,2,0)),quot;quot;,VLOOKUP(B7, R9:S16,2,0)*C27/2000)
gt; gt; Everything works well as long there data in the correct cells or changing
gt; gt; data in the user input cell.
gt; gt; But to start with a quot;blankquot; sheet ready for user input I needed to empty
gt; gt; those particular cells.
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; =sum(c18 c32)
gt; gt; gt; could be replaced with
gt; gt; gt; =sum(c18,c32)
gt; gt; gt;
gt; gt; gt; But if =sum(c18 c32) returned an error, then I don't think c18 and c32 are
gt; gt; gt; really both empty.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; And I would expect G29 being empty as the real problem with the first formula.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Serge wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; Hello Dave,
gt; gt; gt; gt; In H27 the answer is #DIV/0! Because now the cells I10, I37 amp; G29 are empty.
gt; gt; gt; gt; H27=DEGREES(ATAN2((I10-(I37*0.0833))/2,G29))
gt; gt; gt; gt;
gt; gt; gt; gt; In C46 the answer is #VALUE! Because C18 amp; C32 are empty.
gt; gt; gt; gt; C46=SUM(C18 C32)
gt; gt; gt; gt;
gt; gt; gt; gt; Other similar cells have the same situation.
gt; gt; gt; gt; Eventually those cell will get data input.
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Just a guess, but if you had quot;deletedquot; the data by hitting the spacebar, then
gt; gt; gt; gt; gt; you may see #Value! errors for formulas that refer to that cell.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; And if you empty a cell or make it equal 0 and then try to divide by that cell,
gt; gt; gt; gt; gt; you'll get the #Div/0! error.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I think I'd be more careful quot;deletingquot; the data and use a different formula:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; =if(a1=0,quot;quot;,b1/a1)
gt; gt; gt; gt; gt; or
gt; gt; gt; gt; gt; =if(iserror(b1/a1),quot;quot;,b1/a1)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; If you really want to cheat (I wouldn't use this technique), you could apply
gt; gt; gt; gt; gt; format|conditional formatting to make it look like the cells that evaluate to
gt; gt; gt; gt; gt; errors are empty.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; With B1 the activecell in the selected cells
gt; gt; gt; gt; gt; Format|conditional formatting
gt; gt; gt; gt; gt; formula is: =ISERROR(B1)
gt; gt; gt; gt; gt; make it a white font on a white fill (or something like that)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; And you can select any cells you want, just write the formula using the
gt; gt; gt; gt; gt; activecell.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Serge wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I just completed a workbook (two worksheet) with help from some very nice
gt; gt; gt; gt; gt; gt; group members.
gt; gt; gt; gt; gt; gt; Now I want to create a master template. In the user input cells,I deleted
gt; gt; gt; gt; gt; gt; the data used to create the workbook in the first place. Now where the
gt; gt; gt; gt; gt; gt; answers should be I get this quot;#VALUE!quot; and one of the cell has a quot;#DIV/0quot; and
gt; gt; gt; gt; gt; gt; other cells that has the same formula, shows a quot;0quot; which I don't mind. Why
gt; gt; gt; gt; gt; gt; would I get this answer in some cells and not in other cells.
gt; gt; gt; gt; gt; gt; Also I would like to lock this workbook to prevent changes of the formulas
gt; gt; gt; gt; gt; gt; by other staff members and still allowing them to use the workbook.
gt; gt; gt; gt; gt; gt; Can someone help with my request?
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Many thanks in advance.
gt; gt; gt; gt; gt; gt; Serge
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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

Serge wrote:
gt;
gt; Hello Dave,
gt; I'm very confused at the moment, I have been busy at this since your last
gt; reply.
gt; I'm having difficulty with resolving C18 amp; C32.
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; C18 contains a formula that looks at C17. If c17 = quot;quot;, then c18 will be the
gt; gt; value error.
gt; gt;
gt; gt; I'd use:
gt; gt; =if(iserror(yourformula),quot;quot;,yourformula)
gt; gt;
gt; gt; But if c18 and c32 both evaluated to quot;quot;, then
gt; gt; =sum(c18,c32)
gt; gt; returned 0 for me.
gt; gt;
gt; gt;
gt; gt;
gt; gt; Serge wrote:
gt; gt; gt;
gt; gt; gt; Hello again Dave,
gt; gt; gt; I tried the comma, no luck.
gt; gt; gt;
gt; gt; gt; In C18=C17*0.90718
gt; gt; gt; In C17=IF(ISNA(VLOOKUP(B7,R9:S16,2,0)),quot;quot;,VLOOKUP(B7, R9:S16,2,0)*C13/2000)
gt; gt; gt; In C32=C31*0.90718
gt; gt; gt; In C31=IF(ISNA(VLOOKUP(B7,R9:S16,2,0)),quot;quot;,VLOOKUP(B7, R9:S16,2,0)*C27/2000)
gt; gt; gt; Everything works well as long there data in the correct cells or changing
gt; gt; gt; data in the user input cell.
gt; gt; gt; But to start with a quot;blankquot; sheet ready for user input I needed to empty
gt; gt; gt; those particular cells.
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; =sum(c18 c32)
gt; gt; gt; gt; could be replaced with
gt; gt; gt; gt; =sum(c18,c32)
gt; gt; gt; gt;
gt; gt; gt; gt; But if =sum(c18 c32) returned an error, then I don't think c18 and c32 are
gt; gt; gt; gt; really both empty.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; And I would expect G29 being empty as the real problem with the first formula.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Serge wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Hello Dave,
gt; gt; gt; gt; gt; In H27 the answer is #DIV/0! Because now the cells I10, I37 amp; G29 are empty.
gt; gt; gt; gt; gt; H27=DEGREES(ATAN2((I10-(I37*0.0833))/2,G29))
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; In C46 the answer is #VALUE! Because C18 amp; C32 are empty.
gt; gt; gt; gt; gt; C46=SUM(C18 C32)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Other similar cells have the same situation.
gt; gt; gt; gt; gt; Eventually those cell will get data input.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Just a guess, but if you had quot;deletedquot; the data by hitting the spacebar, then
gt; gt; gt; gt; gt; gt; you may see #Value! errors for formulas that refer to that cell.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; And if you empty a cell or make it equal 0 and then try to divide by that cell,
gt; gt; gt; gt; gt; gt; you'll get the #Div/0! error.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I think I'd be more careful quot;deletingquot; the data and use a different formula:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; =if(a1=0,quot;quot;,b1/a1)
gt; gt; gt; gt; gt; gt; or
gt; gt; gt; gt; gt; gt; =if(iserror(b1/a1),quot;quot;,b1/a1)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; If you really want to cheat (I wouldn't use this technique), you could apply
gt; gt; gt; gt; gt; gt; format|conditional formatting to make it look like the cells that evaluate to
gt; gt; gt; gt; gt; gt; errors are empty.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; With B1 the activecell in the selected cells
gt; gt; gt; gt; gt; gt; Format|conditional formatting
gt; gt; gt; gt; gt; gt; formula is: =ISERROR(B1)
gt; gt; gt; gt; gt; gt; make it a white font on a white fill (or something like that)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; And you can select any cells you want, just write the formula using the
gt; gt; gt; gt; gt; gt; activecell.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Serge wrote:
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; I just completed a workbook (two worksheet) with help from some very nice
gt; gt; gt; gt; gt; gt; gt; group members.
gt; gt; gt; gt; gt; gt; gt; Now I want to create a master template. In the user input cells,I deleted
gt; gt; gt; gt; gt; gt; gt; the data used to create the workbook in the first place. Now where the
gt; gt; gt; gt; gt; gt; gt; answers should be I get this quot;#VALUE!quot; and one of the cell has a quot;#DIV/0quot; and
gt; gt; gt; gt; gt; gt; gt; other cells that has the same formula, shows a quot;0quot; which I don't mind. Why
gt; gt; gt; gt; gt; gt; gt; would I get this answer in some cells and not in other cells.
gt; gt; gt; gt; gt; gt; gt; Also I would like to lock this workbook to prevent changes of the formulas
gt; gt; gt; gt; gt; gt; gt; by other staff members and still allowing them to use the workbook.
gt; gt; gt; gt; gt; gt; gt; Can someone help with my request?
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Many thanks in advance.
gt; gt; gt; gt; gt; gt; gt; Serge
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson