Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excell programming
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? -- |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excell programming
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$40,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. "Muddled" wrote: 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? -- |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excell programming
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. Muddled wrote: 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? -- -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excell programming
Thank you JLatham.
Your "However" Instructions were exactly what I needed.-- "JLatham" wrote: 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$40,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. "Muddled" wrote: 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? -- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excell programming
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. "Dave Peterson" wrote: 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. Muddled wrote: 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? -- -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
programming help | Excel Discussion (Misc queries) | |||
Programming or Function? | Excel Discussion (Misc queries) | |||
CD Programming | Excel Discussion (Misc queries) | |||
programming | Excel Discussion (Misc queries) | |||
Programming help | Excel Discussion (Misc queries) |