Excell programming

[Guest]

In My excel I have multiple tabs in one workbook. On the destination tabs, In
the $$ column a price is sourced from the master sheet tab. Sometimes I want
to substitute a different price than the one computed. Can I make a new cell
on the master sheet overide the destination cell on the secondary tabbed
sheets to show a non computed total without disrupting the existing links
between master & Destination sheets?
--

 

[Guest]

If I am understanding your question correctly, the basic answer is no. A
cell either has a formula or a value in it. If you have formulas in a cell
referencing cells on other sheets (the master) then simply writing a value
into that cell would destroy the formula.

However, you may be able to do this with an IF formula in the cell on the
secondary sheet. Let's assume for the moment that you have a formula there
now something like:
=Master!$A$1+Master!$B$4
and lets assume that when you want another value in that cell instead of the
results of that calculation, you are going to put it into X4 in the master
sheet. You could leave X4 empty or put a zero in it and use a formula like
this in the secondary sheet:
=IF(Master!$X$4>0,Master!$X$4,Master$A$1+Master!$B$4)
that says: if the value in X4 on the Master sheet is greater than zero, put
the value from X4 on the Master sheet here, otherwise put the results of the
calculation here.

Hope that helps some.

 

[Dave Peterson]

Maybe...
(Just to disagree with Jerry, <vbg>)

Maybe you can use this kind of layout.

In the input sheet (destination tabs), you could use something like:

Column Use
A Part Number
B Qty
C Standard Price (from the lookup table)
D Manual override price
E Extended price
A calculation like:
=b2*if(d2<>"",d2,c2)

Alternatively, you could approach it this way:
On the price sheet:
Column Use
A Part number
B Override indicator (say an X to use the manual price)
C Standard Price
D Manual override price

Then to retrieve the price:
=vlookup(a2,prices!a:d,if(vlookup(a2,prices!A:b,2,false)="x",4,3),0)

I would think that you would want to keep most users out of the Prices tab. It
would scare me letting lots of people make changes to that sheet. Too much of a
chance that the part numbers and standard price could change when they're adding
the X and the override price.

I'd use the first technique and add a warning cell:
=if(d2="","","Warning: Manual Pricing in Effect!"
in big bold letters in column F.
Just to make it easier to see.

 

[Guest]

Thank you JLatham.
Your "However" Instructions were exactly what I needed.--

 

[Guest]

Alternatives are always good - sometimes the 'alternative' turns out to work
better in a given situation. Or at least just as well, and in those cases,
the one that the maintainer understands and can maintain better is the better
choice.