Hello,





I have a spreadsheet that is set up as an invoice.

When I run a macro to create a new invoice it clears the details that were
entered in the previous invoice and when the new invoice opens before I
enter any details I am getting the following error #VALUE! in G:48
(Discount) and G:49 (Total).

The formula I have in G:48 is =-G46*DISCOUNT (Discount is a named range in
H:9)

The formula I have in G:49 is =SUM(G46:G48)

Could somebody please help me with rewriting the formulas in G:48 and G:49
so as not to get the error #VALUE! when a new invoice is opened. Ideally I
would like these cells to be blank when the new invoice is created.



Any help or suggestions would be most welcome.





Eamon

is there an underscore before <g46 in the formula for g48?


Eamon wrote in message
...
Hello,





I have a spreadsheet that is set up as an invoice.

When I run a macro to create a new invoice it clears the details that were
entered in the previous invoice and when the new invoice opens before I
enter any details I am getting the following error #VALUE! in G:48
(Discount) and G:49 (Total).

The formula I have in G:48 is =-G46*DISCOUNT (Discount is a named range in
H:9)

The formula I have in G:49 is =SUM(G46:G48)

Could somebody please help me with rewriting the formulas in G:48 and G:49
so as not to get the error #VALUE! when a new invoice is opened. Ideally I
would like these cells to be blank when the new invoice is created.



Any help or suggestions would be most welcome.





Eamon

=IF(ISERR(DISCOUNT),"",-G46*DISCOUNT)
and
=IF(ISERR(DISCOUNT),"",SUM(G46:G48))

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=376241

Good morning Eamon

I was just going to answer this, but notice you've just got a reply.

An alternative would be to got to Tools Options, View, and then
uncheck zero values. This would suppress zeros across your whole
workbook.

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=376241

Dominic,

I had zero values unchecked, but I am still getting the #VALUE! error when a
new invoice is opened.
Anything else I could try?

Eamon



"dominicb" wrote in
message ...

Good morning Eamon

I was just going to answer this, but notice you've just got a reply.

An alternative would be to got to Tools Options, View, and then
uncheck zero values. This would suppress zeros across your whole
workbook.

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile:
http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=376241

"mangesh_yadav"
wrote in message
news:mangesh_yadav.1q1rug_1117796712.4913@excelfor um-nospam.com...

=IF(ISERR(DISCOUNT),"",-G46*DISCOUNT)
and
=IF(ISERR(DISCOUNT),"",SUM(G46:G48))



Mangesh

Thanks for your help.

I have entered your formulas as shown, but i am still getting the #Value!
error when a new invoice is created

Eamon

Snip

"R.VENKATARAMAN" wrote in message
...
is there an underscore before <g46 in the formula for g48?

No. it is a minus sign -.


Eamon wrote in message
...
Hello,





I have a spreadsheet that is set up as an invoice.

When I run a macro to create a new invoice it clears the details that
were
entered in the previous invoice and when the new invoice opens before I
enter any details I am getting the following error #VALUE! in G:48
(Discount) and G:49 (Total).

The formula I have in G:48 is =-G46*DISCOUNT (Discount is a named range
in
H:9)

The formula I have in G:49 is =SUM(G46:G48)

Could somebody please help me with rewriting the formulas in G:48 and
G:49
so as not to get the error #VALUE! when a new invoice is opened. Ideally
I
would like these cells to be blank when the new invoice is created.



Any help or suggestions would be most welcome.





Eamon

Try:

=IF(ISNUMBER(-G46*DISCOUNT),-G46*DISCOUNT,"")
and
=IF(ISNUMBER(SUM(G46:G48)),SUM(G46:G48),"")


Mangesh




"Eamon" wrote in message
...

"mangesh_yadav"

wrote in message
news:mangesh_yadav.1q1rug_1117796712.4913@excelfor um-nospam.com...

=IF(ISERR(DISCOUNT),"",-G46*DISCOUNT)
and
=IF(ISERR(DISCOUNT),"",SUM(G46:G48))



Mangesh

Thanks for your help.

I have entered your formulas as shown, but i am still getting the #Value!
error when a new invoice is created

Eamon

Snip

Mangesh,

Your formula gets rid of the #VALUE! Error. Thank you.
See below for detail of how the spreadsheet is laid out.

Potential problem now.
1) If someone just purchases a part from the garage, I am getting #VALUE for
TAX G47
Or
2) If someone for example had a minor repair carried out that did not
require any parts, just labour I am getting #VALUE for TAX G47

When parts and labour are used it works fine.

Any suggestions to correct this please.



In cells B17:G43 I have parts that the garage may sell or are used in
repairs.
Columns:
B Code
C Product Description
D Format
E Price
F Quantity
G Total


In cells A44:D48 I have details of Labor
Columns:
A Service Person
B Hours
C Rate
D Amount

D49 (TOTAL) =IF(SUM(E45:E48),SUM(E45:E48),"")


G44 (PARTS) =IF(SUM(G17:G43),SUM(G17:G43),"")
G45 (LABOR) =IF(SUM(E49),E49,"")
G46 (SUB TOTAL) =IF(SUM(G43:G45),SUM(G43:G45),"")
G47 (TAX) =IF(OR(SUM(G44)0,G45),(PARTS_TAX*G44)+(LABOUR_TAX *G45),"")

(PARTS_TAX is in D14) and (LABOUR_TAX is in G14)

G48 (DISCOUNT) =IF(ISNUMBER(-G46*DISCOUNT),-G46*DISCOUNT,"")

(DISCOUNT is in H14)

G49 (TOTAL) =IF(ISNUMBER(SUM(G46:G48)),SUM(G46:G48),"")

Eamon


"Mangesh Yadav" wrote in message
...
Try:

=IF(ISNUMBER(-G46*DISCOUNT),-G46*DISCOUNT,"")
and
=IF(ISNUMBER(SUM(G46:G48)),SUM(G46:G48),"")












Mangesh




"Eamon" wrote in message
...

"mangesh_yadav"

wrote in message
news:mangesh_yadav.1q1rug_1117796712.4913@excelfor um-nospam.com...

=IF(ISERR(DISCOUNT),"",-G46*DISCOUNT)
and
=IF(ISERR(DISCOUNT),"",SUM(G46:G48))



Mangesh

Thanks for your help.

I have entered your formulas as shown, but i am still getting the
#Value!
error when a new invoice is created

Eamon

Snip

Have it solved, thanks to everyone who replied, your help was much
appreciated, and a special thank you to Mangesh.

Best regards,

Eamon


"Eamon" wrote in message
...
Mangesh,

Your formula gets rid of the #VALUE! Error. Thank you.
See below for detail of how the spreadsheet is laid out.

Potential problem now.
1) If someone just purchases a part from the garage, I am getting #VALUE
for TAX G47
Or
2) If someone for example had a minor repair carried out that did not
require any parts, just labour I am getting #VALUE for TAX G47

When parts and labour are used it works fine.

Any suggestions to correct this please.



In cells B17:G43 I have parts that the garage may sell or are used in
repairs.
Columns:
B Code
C Product Description
D Format
E Price
F Quantity
G Total


In cells A44:D48 I have details of Labor
Columns:
A Service Person
B Hours
C Rate
D Amount

D49 (TOTAL) =IF(SUM(E45:E48),SUM(E45:E48),"")


G44 (PARTS) =IF(SUM(G17:G43),SUM(G17:G43),"")
G45 (LABOR) =IF(SUM(E49),E49,"")
G46 (SUB TOTAL) =IF(SUM(G43:G45),SUM(G43:G45),"")
G47 (TAX) =IF(OR(SUM(G44)0,G45),(PARTS_TAX*G44)+(LABOUR_TAX *G45),"")

(PARTS_TAX is in D14) and (LABOUR_TAX is in G14)

G48 (DISCOUNT) =IF(ISNUMBER(-G46*DISCOUNT),-G46*DISCOUNT,"")

(DISCOUNT is in H14)

G49 (TOTAL) =IF(ISNUMBER(SUM(G46:G48)),SUM(G46:G48),"")

Eamon


"Mangesh Yadav" wrote in message
...
Try:

=IF(ISNUMBER(-G46*DISCOUNT),-G46*DISCOUNT,"")
and
=IF(ISNUMBER(SUM(G46:G48)),SUM(G46:G48),"")












Mangesh




"Eamon" wrote in message
...

"mangesh_yadav"

wrote in message
news:mangesh_yadav.1q1rug_1117796712.4913@excelfor um-nospam.com...

=IF(ISERR(DISCOUNT),"",-G46*DISCOUNT)
and
=IF(ISERR(DISCOUNT),"",SUM(G46:G48))



Mangesh

Thanks for your help.

I have entered your formulas as shown, but i am still getting the
#Value!
error when a new invoice is created

Eamon

Snip

Hi Eamon,

Good you solved it. Thanks for the feedback.

Mangesh



"Eamon" wrote in message
...
Have it solved, thanks to everyone who replied, your help was much
appreciated, and a special thank you to Mangesh.

Best regards,

Eamon


"Eamon" wrote in message
...
Mangesh,

Your formula gets rid of the #VALUE! Error. Thank you.
See below for detail of how the spreadsheet is laid out.

Potential problem now.
1) If someone just purchases a part from the garage, I am getting #VALUE
for TAX G47
Or
2) If someone for example had a minor repair carried out that did not
require any parts, just labour I am getting #VALUE for TAX G47

When parts and labour are used it works fine.

Any suggestions to correct this please.



In cells B17:G43 I have parts that the garage may sell or are used in
repairs.
Columns:
B Code
C Product Description
D Format
E Price
F Quantity
G Total


In cells A44:D48 I have details of Labor
Columns:
A Service Person
B Hours
C Rate
D Amount

D49 (TOTAL) =IF(SUM(E45:E48),SUM(E45:E48),"")


G44 (PARTS) =IF(SUM(G17:G43),SUM(G17:G43),"")
G45 (LABOR) =IF(SUM(E49),E49,"")
G46 (SUB TOTAL) =IF(SUM(G43:G45),SUM(G43:G45),"")
G47 (TAX) =IF(OR(SUM(G44)0,G45),(PARTS_TAX*G44)+(LABOUR_TAX *G45),"")

(PARTS_TAX is in D14) and (LABOUR_TAX is in G14)

G48 (DISCOUNT) =IF(ISNUMBER(-G46*DISCOUNT),-G46*DISCOUNT,"")

(DISCOUNT is in H14)

G49 (TOTAL) =IF(ISNUMBER(SUM(G46:G48)),SUM(G46:G48),"")

Eamon


"Mangesh Yadav" wrote in message
...
Try:

=IF(ISNUMBER(-G46*DISCOUNT),-G46*DISCOUNT,"")
and
=IF(ISNUMBER(SUM(G46:G48)),SUM(G46:G48),"")












Mangesh




"Eamon" wrote in message
...

"mangesh_yadav"

wrote in message
news:mangesh_yadav.1q1rug_1117796712.4913@excelfor um-nospam.com...

=IF(ISERR(DISCOUNT),"",-G46*DISCOUNT)
and
=IF(ISERR(DISCOUNT),"",SUM(G46:G48))



Mangesh

Thanks for your help.

I have entered your formulas as shown, but i am still getting the
#Value!
error when a new invoice is created

Eamon

Snip