Afternoon all…

Don’t know if anyone can help with this…

We use excel 2010 and have quite a few ‘costing sheets’, first/front tab it shows the labour hours final prices and percentage overheads etc…

We have to manually set this percentage at 5% or higher to get a good cost, depending on the items quoted in the 2nd tab.

Is there a way in excel to set it up so if the cell does not have 5% or higher it will warn your pior to closing and saving the document? basically a memory jogger!

i hope that makes sense.

cheers for nay help

Sounds like a VBA/macro would suit you, are you skilled in that area?

You might also be able to do it with conditional formatting applied to change the background colour for the entire file if any of those cells are less than 5% – not exactly a warning pre-save but it would highlight the issue while you’re working and wouldn’t need you to get into macros/VBA.

Validation? Have a message box pop up and warn you? Might happen a lot during normal working…

Like Portlyone says.
A macros that kicks in when the page is closed would work.

Without getting to technical, a loop that checks every cell is more than 5% off, then lists the ones that aren’t.
Is that the sort of thing?

Josh..

Something like this?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Range(“A1”) < 5 Then
MSG1 = MsgBox(“Percentage lower than 5! Do you still want to close?”, vbYesNo, “Warning!”)

If MSG1 = vbYes Then
Application.Quit
End If
End If
End Sub

The Purist’s is the simplest approach, and quite effective.
Esp if you set the format to grey cell fill and grey text 😉

I’d be tempted to stick with conditional formatting as described above. Not sure you can apply formatting from one cell value across the whole worksheet, but it’s a simpler concept than the VBA solutions.

you can apply formatting from one cell value across the whole worksheet

you can.

Just lock the conditional reference.

Select all, before going into conditional formatting, then set formula to:
=$A$1<5%

jaysus thanks for all the replies..

i understand what you are saying but no idea how to implement it!

the simplest solution i think is what has been said above, to colour the cell you need to change if the cell with the percentage in has not hit 5% or above.

You might also be able to do it with conditional formatting applied to change the background colour for the entire file if any of those cells are less than 5% – not exactly a warning pre-save but it would highlight the issue while you’re working and wouldn’t need you to get into macros/VBA.

how do i do that?

Just out of interest, why do you need to check that limit before closing the document? (Most closed documents are just that: closed. And therefore the contents make little difference…)

I only ask as the limit will be readable to anyone else who receives the file, which means you may be inadvertently sending information to 3rd parties…

mogrim..

its all in house costing, we use the pricing excel sheet and then put the costing into a word doc, PDF then send to the client.

Fair enough, I wondered if there was a risk you were going to send it directly to a client: letting them know that your lower limit was 5% wouldn’t do you much good if you need to negotiate prices with them!

here is a screen grab of the sheet, hope you can see it.

The yellow cell V10 shows the percentage based on the second sheet behind, this needs to be 5% OR above..

If it is lower than 5% you have to manually increase the green cell D27 which is linked to V10 and it increases.

Can’t you just code the green cell increase and make it automatic? Maybe with some kind of colour change to make it clear it’s an auto-generated value?

cheers,

that would be great and i dont know why it wasnt set up when th eguru (whoever that was) deisgne dit years ago.

i wouldnt even know where to begin, excel for dummies maybe!

I’d go with the simplest (even though I’m fluent in VBA) and conditional format the cell..(s)

Use an IF statement somewhere else on the sheet with a merged cell over a group of cells say five x five and conditional format that, complete with a warning message like “Oi fat fingers, watch those margins sonny” You know so it’s mahooosive, but not intrusive and doesn’t show if it’s within tollerance.

VBA’s a good tool, but sounds like you are not into that just yet.

cheers all, bikebouy ive finally got my head round it and have it so the cell stays red until you manually mess with cell D27.

Good shout though about the cell with warning message, i’ll give that a crack.

cheers everyone

that would be great and i dont know why it wasnt set up when th eguru (whoever that was) deisgne dit years ago.

If you want to set it automatically check out the “=if(condition; what to do when true; what to do when false)” function, it’s pretty easy to use. It does mean you’re generating automatic prices, which may or may not be a problem – do you need to formally sign-off any manual price change?

bikebouy’s suggestion is a good one, too – probably the best way to make it non-automatic. It’d be clear to any other user of the spreadsheet as well so should reduce the possibility of error.

cheers mogrim, just reading up on the IF= function now, my god i should have done this years ago!!

we dont need to formally sign off, which in essence is the problem because it would get checked!!

thanks everyone again, great help!

You could use Goal Seek to automagically change D27 to make V10 greater than 5%.

Something simple like:

Cell A1 = 10
Cell A2 = 9.5 (though this cell is the variable)
Cell A3 = formula result

Stick the IF somewhere like B1, then Merge it over range B1 to C5 then center it, use the following as the formula:
=IF(A3>=105%,”I’m a dope”,”No you’re not”)
Then conditional format the IF result Cell (B1) on “I’m a dope” with a border highlight and Format it Red or something

You get the drift now.