how do you?????????

[cjbarron5]

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?

 

[Don Guillett]

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

 

[cjbarron5]

thats what I thought but I was hopeing for someting diffrent

 

[Fred Smith]

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]

here is the formula I have
set............=IF(ISNA(VLOOKUP(C18,TABLE1A,3,FALSE)),"",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]

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.

 

[Fred Smith]

Correction:

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

 

[cjbarron5]

ok so now I have this formula
=IF(PRICEOVERRIDE!B2>0,PRICEOVERRIDE!B2,IF(ISNA(VLOOKUP(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]

well the formula
=IF(PRICEOVERRIDE!B2>0,PRICEOVERRIDE!B2,IF(ISNA(VLOOKUP(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!!!!!!!!!!

 

[cjbarron5]

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

 

[Fred Smith]

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(PRICEOVERRIDE!B2>0,PRICEOVERRIDE!B2,VLOOKUP(E19,TABLE1A,3,FALSE))

Regards,
Fred

 

[cjbarron5]

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

 

[Fred Smith]

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]

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]

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]

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!B2>0,PRICEOVERRIDE,VLOOKUP(D18,TABLE1A,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]

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]

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