excel wizards...

[vinister]

Hey guys,

I'm working on a spreadsheet for invoicing basic parts and labor.

For parts, sometimes the price is based on the cost, with an added markup %. That is how I have the spreadsheet setup. You enter in all the quantities and costs, and I have a slider that allows us to drag the markup on all products up and down at will. The spreadsheet calculates the individual selling prices, and adds it all up.

My problem is that sometimes we want to base the pricing on the MSRP, minus a discount. Easy enough to calculate, but difficult to enter into the forms. To make matters worse, sometimes my boss just goes into the selling price, and types some price he decided, erasing the underlying formula. Everything still works, and the markup for that individual item is properly calculated, but I just don't like it, because now that formula is gone....

I guess my question is, is there a way to manually enter in a value in a cell that contains a formula, while still maintaining the ability to revert to the formula, like say if the manual value is erased.

It would be nice if we could manually enter in the MSRP, then have a box appear on the side labelled 'discount', where we can enter in whatever % to discount the price by. I guess I want to conditionally override the " price each" cell's formula based on existence of data in the "MSRP" cell, also based on the "discount" cell. Phew....

I realize I'm getting into quite complicated coding here so if anyone can help but needs the file to look at, I'd be happy to share it! Its actually a very nice spreadsheet including a chart that breaks down profit % by labor and parts, has provisions for modifying hourly bill and pay rates, has multiple labor rates (regular and IT), and total profit breakdown. It prints out really nice while leaving out all the cost/profit info thanks to the printable area feature.

[damha]

How about putting the formula in another cell far away and just reference it?

[ex2cib]

Can't exactly explain what I'm thinking of in my head easily, but couldn't you have two columns, one with your formula, and a column next to it being manual.

Then, in the formula using that number, basically make it work like this. if [manual column reference] ("", [true value, does calculation with your formula], [false value, does calculation with manual value] )

then, lock down the spreadsheet to only allow certain cells to be modified.

[vinister]

thanks for your input!

I actually solved this issue awhile back.

I'm going to post up the spreadsheet, I think it would be very useful for anyone billing for their parts & labor that want good instant feedback on profit margins.

[ex2cib]

guess thats what i get for not looking at post dates while bored at work