I have an Invoice sheet that has a formula in the price section.If I want to
type a price in instead of using the price the formula uses how do I do that
with out damaging the formula I have set?

You don't. Or, if you do, don't save the file.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"cjbarron5" wrote in message
...
I have an Invoice sheet that has a formula in the price section.If I want
to
type a price in instead of using the price the formula uses how do I do
that
with out damaging the formula I have set?

thats what I thought but I was hopeing for someting diffrent

"cjbarron5" wrote:

I have an Invoice sheet that has a formula in the price section.If I want to
type a price in instead of using the price the formula uses how do I do that
with out damaging the formula I have set?

See if the following will work for you.

-- set up a "price override" cell.
-- change your price formula to check this cell first. If there is use it,
otherwise use your formula.

This way the formula stays intact, but you can override it.

Regards,
Fred.

"cjbarron5" wrote in message
...
I have an Invoice sheet that has a formula in the price section.If I want
to
type a price in instead of using the price the formula uses how do I do
that
with out damaging the formula I have set?

here is the formula I have
set............=IF(ISNA(VLOOKUP(C18,TABLE1A,3,FALS E)),"",VLOOKUP(C18,TABLE1A,3,FALSE))
so to change it to check my "price override cell" first what do I need to do?
any ideas
"Fred Smith" wrote:

See if the following will work for you.

-- set up a "price override" cell.
-- change your price formula to check this cell first. If there is use it,
otherwise use your formula.

This way the formula stays intact, but you can override it.

Regards,
Fred.

"cjbarron5" wrote in message
...
I have an Invoice sheet that has a formula in the price section.If I want
to
type a price in instead of using the price the formula uses how do I do
that
with out damaging the formula I have set?

Let's say your override cell is in D5. Then use:

if(d5<0,d5,ISNA(VLOOKUP(C18,TABLE1A,3,FALSE)),"", VLOOKUP(C18,TABLE1A,3,FALSE))

Regards,
Fred.

"cjbarron5" wrote in message
...
here is the formula I have
set............=IF(ISNA(VLOOKUP(C18,TABLE1A,3,FALS E)),"",VLOOKUP(C18,TABLE1A,3,FALSE))
so to change it to check my "price override cell" first what do I need to
do?
any ideas
"Fred Smith" wrote:

See if the following will work for you.

-- set up a "price override" cell.
-- change your price formula to check this cell first. If there is use
it,
otherwise use your formula.

This way the formula stays intact, but you can override it.

Regards,
Fred.

"cjbarron5" wrote in message
...
I have an Invoice sheet that has a formula in the price section.If I
want
to
type a price in instead of using the price the formula uses how do I do
that
with out damaging the formula I have set?

Correction:

if(d5<0,d5,if(ISNA(VLOOKUP(C18,TABLE1A,3,FALSE)), "",VLOOKUP(C18,TABLE1A,3,FALSE)))

"Fred Smith" wrote in message
...
Let's say your override cell is in D5. Then use:

if(d5<0,d5,ISNA(VLOOKUP(C18,TABLE1A,3,FALSE)),"", VLOOKUP(C18,TABLE1A,3,FALSE))

Regards,
Fred.

"cjbarron5" wrote in message
...
here is the formula I have
set............=IF(ISNA(VLOOKUP(C18,TABLE1A,3,FALS E)),"",VLOOKUP(C18,TABLE1A,3,FALSE))
so to change it to check my "price override cell" first what do I need to
do?
any ideas
"Fred Smith" wrote:

See if the following will work for you.

-- set up a "price override" cell.
-- change your price formula to check this cell first. If there is use
it,
otherwise use your formula.

This way the formula stays intact, but you can override it.

Regards,
Fred.

"cjbarron5" wrote in message
...
I have an Invoice sheet that has a formula in the price section.If I
want
to
type a price in instead of using the price the formula uses how do I
do
that
with out damaging the formula I have set?

ok so now I have this formula
=IF(PRICEOVERRIDE!B20,PRICEOVERRIDE!B2,IF(ISNA(VL OOKUP(E18,TABLE1A,3,FALSE)),"",VLOOKUP(E18,TABLE1A ,3,FALSE)))
but my invoice has 27 lines in it when I use the above formula in all 27
price spaces and enter only one item it shows up in all 27 spaces
QTY ITEM# Description Price
Shipping Extension

1 USEDCOMP USED COMPUTER $1.00 $1.00
$1.00
$-
$1.00 $-

extension contains the formula: =(N(F18)*B18)+G18
description contains the formula:
=IF(ISNA(VLOOKUP(C18,TABLE1A,3,FALSE)),"",VLOOKUP( C18,TABLE1A,3,FALSE))

any ideas?
thanks chris

"cjbarron5" wrote:

I have an Invoice sheet that has a formula in the price section.If I want to
type a price in instead of using the price the formula uses how do I do that
with out damaging the formula I have set?

well the formula
=IF(PRICEOVERRIDE!B20,PRICEOVERRIDE!B2,IF(ISNA(VL OOKUP(E19,TABLE1A,3,FALSE)),"",VLOOKUP(E19,TABLE1A ,3,FALSE)))
works great for price override but my normal function does not the original
formula was
=IF(ISNA(VLOOKUP(E19,TABLE1A,3,FALSE)),"",VLOOKUP( E19,TABLE1A,3,FALSE)) and
worked great but my priceoverride has made it not work!!!!!!!!HELP!!!!!!!!!!

ok i found part of the problem but it still shows the price override amount
in all 27 lines even if there is no product listed

OK, you want to check for the existance of a product first, before you check
for a price override. So change the If statement to:

=IF(ISNA(VLOOKUP(E19,TABLE1A,3,FALSE)),"",IF(PRICE OVERRIDE!B20,PRICEOVERRIDE!B2,VLOOKUP(E19,TABLE1A ,3,FALSE))

Regards,
Fred

"cjbarron5" wrote in message
...
well the formula
=IF(PRICEOVERRIDE!B20,PRICEOVERRIDE!B2,IF(ISNA(VL OOKUP(E19,TABLE1A,3,FALSE)),"",VLOOKUP(E19,TABLE1A ,3,FALSE)))
works great for price override but my normal function does not the
original
formula was
=IF(ISNA(VLOOKUP(E19,TABLE1A,3,FALSE)),"",VLOOKUP( E19,TABLE1A,3,FALSE))
and
worked great but my priceoverride has made it not
work!!!!!!!!HELP!!!!!!!!!!

ok I tryed the new formula and it doesnt seem to work its still showing in
all the other cells any more ideas????
thanks

What does "doesnt seem to work" mean? What happens? What results do you get?

What does "still showing in all the other cells" mean?

Regards,
Fred.

"cjbarron5" wrote in message
...
ok I tryed the new formula and it doesnt seem to work its still showing in
all the other cells any more ideas????
thanks

doesnt seem to work means im not getting a blank cell when there is no other
info in the refered cell
my invoice has 27 lines for items so when I try to override one price it
over rides all the prices
hope this clears things up
I can send you a copy of the workbook if you like
thanks
"Fred Smith" wrote:

What does "doesnt seem to work" mean? What happens? What results do you get?

What does "still showing in all the other cells" mean?

Regards,
Fred.

"cjbarron5" wrote in message
...
ok I tryed the new formula and it doesnt seem to work its still showing in
all the other cells any more ideas????
thanks

1. What formula are you using? Copy it directly from the formula bar so we
can see exactly what it is.
2. If your invoice has 27 lines, you need a range of 27 cells for your price
overrides. In your Priceoverride sheet, you need to set up a range (say B2
to B28) to support overriding each line separately.
3. You say "not getting a blank cell when there is no other info in the
refered cell" Well, what are you getting? What do you mean by "refered
cell"?

Rather than send a copy of the workbook, I'd rather you explain things
clearly and keep the whole conversation in the message so I don't have to go
back and forth to other messages. That way I don't have to worry about
viruses, and you get your answer much more quickly.

Regards,
Fred.

"cjbarron5" wrote in message
...

doesnt seem to work means im not getting a blank cell when there is no
other
info in the refered cell
my invoice has 27 lines for items so when I try to override one price it
over rides all the prices
hope this clears things up
I can send you a copy of the workbook if you like
thanks
"Fred Smith" wrote:

What does "doesnt seem to work" mean? What happens? What results do you
get?

What does "still showing in all the other cells" mean?

Regards,
Fred.

"cjbarron5" wrote in message
...
ok I tryed the new formula and it doesnt seem to work its still showing
in
all the other cells any more ideas????
thanks

I was playing around with it most of the night and I have got it done the
formula I came up with is
=IF(PRICEOVERRIDE!B20,PRICEOVERRIDE,VLOOKUP(D18,T ABLE1A,2,FALSE))
along with the the 27 cell range suggested by Fred Smith the whole problem I
think was I wanted to use one override cell for all cells

"Fred Smith" wrote:

1. What formula are you using? Copy it directly from the formula bar so we
can see exactly what it is.
2. If your invoice has 27 lines, you need a range of 27 cells for your price
overrides. In your Priceoverride sheet, you need to set up a range (say B2
to B28) to support overriding each line separately.
3. You say "not getting a blank cell when there is no other info in the
refered cell" Well, what are you getting? What do you mean by "refered
cell"?

Rather than send a copy of the workbook, I'd rather you explain things
clearly and keep the whole conversation in the message so I don't have to go
back and forth to other messages. That way I don't have to worry about
viruses, and you get your answer much more quickly.

Regards,
Fred.

"cjbarron5" wrote in message
...

doesnt seem to work means im not getting a blank cell when there is no
other
info in the refered cell
my invoice has 27 lines for items so when I try to override one price it
over rides all the prices
hope this clears things up
I can send you a copy of the workbook if you like
thanks
"Fred Smith" wrote:

What does "doesnt seem to work" mean? What happens? What results do you
get?

What does "still showing in all the other cells" mean?

Regards,
Fred.

"cjbarron5" wrote in message
...
ok I tryed the new formula and it doesnt seem to work its still showing
in
all the other cells any more ideas????
thanks

I still see problems with your formula, but if you're happy, so are we.

First, I'm sure you need to add !B2 to the sheet name in the true part of
your If statement.
Second, what happens if D18 is not in your table? Are you happy with #N/A as
the result?

Regards,
Fred.

"cjbarron5" wrote in message
...
I was playing around with it most of the night and I have got it done the
formula I came up with is
=IF(PRICEOVERRIDE!B20,PRICEOVERRIDE,VLOOKUP(D18,T ABLE1A,2,FALSE))
along with the the 27 cell range suggested by Fred Smith the whole problem
I
think was I wanted to use one override cell for all cells

"Fred Smith" wrote:

1. What formula are you using? Copy it directly from the formula bar so
we
can see exactly what it is.
2. If your invoice has 27 lines, you need a range of 27 cells for your
price
overrides. In your Priceoverride sheet, you need to set up a range (say
B2
to B28) to support overriding each line separately.
3. You say "not getting a blank cell when there is no other info in the
refered cell" Well, what are you getting? What do you mean by "refered
cell"?

Rather than send a copy of the workbook, I'd rather you explain things
clearly and keep the whole conversation in the message so I don't have to
go
back and forth to other messages. That way I don't have to worry about
viruses, and you get your answer much more quickly.

Regards,
Fred.

"cjbarron5" wrote in message
...

doesnt seem to work means im not getting a blank cell when there is no
other
info in the refered cell
my invoice has 27 lines for items so when I try to override one price
it
over rides all the prices
hope this clears things up
I can send you a copy of the workbook if you like
thanks
"Fred Smith" wrote:

What does "doesnt seem to work" mean? What happens? What results do
you
get?

What does "still showing in all the other cells" mean?

Regards,
Fred.

"cjbarron5" wrote in message
...
ok I tryed the new formula and it doesnt seem to work its still
showing
in
all the other cells any more ideas????
thanks

yep I forgot to type !b2 when I typed it in the message, and d18 is in the
range now after some minor revissions to the sheet
Thanks alot for ALL your help and patience
Chris Barron
"Fred Smith" wrote:

I still see problems with your formula, but if you're happy, so are we.

First, I'm sure you need to add !B2 to the sheet name in the true part of
your If statement.
Second, what happens if D18 is not in your table? Are you happy with #N/A as
the result?

Regards,
Fred.

"cjbarron5" wrote in message
...
I was playing around with it most of the night and I have got it done the
formula I came up with is
=IF(PRICEOVERRIDE!B20,PRICEOVERRIDE,VLOOKUP(D18,T ABLE1A,2,FALSE))
along with the the 27 cell range suggested by Fred Smith the whole problem
I
think was I wanted to use one override cell for all cells

"Fred Smith" wrote:

1. What formula are you using? Copy it directly from the formula bar so
we
can see exactly what it is.
2. If your invoice has 27 lines, you need a range of 27 cells for your
price
overrides. In your Priceoverride sheet, you need to set up a range (say
B2
to B28) to support overriding each line separately.
3. You say "not getting a blank cell when there is no other info in the
refered cell" Well, what are you getting? What do you mean by "refered
cell"?

Rather than send a copy of the workbook, I'd rather you explain things
clearly and keep the whole conversation in the message so I don't have to
go
back and forth to other messages. That way I don't have to worry about
viruses, and you get your answer much more quickly.

Regards,
Fred.

"cjbarron5" wrote in message
...

doesnt seem to work means im not getting a blank cell when there is no
other
info in the refered cell
my invoice has 27 lines for items so when I try to override one price
it
over rides all the prices
hope this clears things up
I can send you a copy of the workbook if you like
thanks
"Fred Smith" wrote:

What does "doesnt seem to work" mean? What happens? What results do
you
get?

What does "still showing in all the other cells" mean?

Regards,
Fred.

"cjbarron5" wrote in message
...
ok I tryed the new formula and it doesnt seem to work its still
showing
in
all the other cells any more ideas????
thanks