formula to copy formatting

[Kev Utton]

I have a formula but need to add to the formula a syntax that also copies the format as well as the contents, is this possible ?

This is my current formula:

=IF(BQ403="","",IF(BQ403<10,CEILING((BQ403*$D403),0.01),(IF(((BQ403*1.05)>50),CEILING(SUM(BQ403*$D403),0.1),CEILING(SUM(BQ403*$D403),0.05)))))

Reason, I have a list of data working off another list, however some are specials which don't work off this list and are therefore formatted in blue to stand out (there is no rhyme or reason to them being special so conditional formatting won't work)
The formula is basically taking the number and adding 5% and rounding up accordingly, but I still need the specials to stand out.

Look forward to any help available

[tuph]

If there's no rhyme or reason to the specials, how does the formula know that it's result is special?

[Kev Utton]

Thanks for the reply.

The formula does not know that it is a special and dosn't need to, because the calculations is for all.

However I need to know becuase it has relations to discissoin re margins etc which is why I highlight them.

Basically i have a trade price list of say 50 items, a customers list may be based on the trade list but there are certian products where we need to be competitive so I give them a price accordingly, I highlight these prices so that I know they special.

So I currently have a 2007 list I then use a calc to work out 2008, this information is then transfered into a main price list template using a Vlookup, this template needs to show the highlighted items just like my list does.

obviously I can do manually but the list has over 500 products and I have over 30 of lists which all have different special items.


Hope this makes sense ??

[tuph]

If the only indicator of a special price is in your knowledge, I can't see any way of telling Excel to change the format of the cell. Someone with more expertise may be able to help.

[daddylonglegs]

Hello Kev,

As tuph says, you can't do this with formulas - you'd need VBA - try the Excel programming forum - or, if you don't want to go down that route perhaps you could have an "x" or similar, in the column next to your specials, then you can use conditional formatting based on that.

BTW, you could simplify your format a little like this

=IF(BQ403="","",CEILING(BQ403*$D403,IF( BQ403<10,0.01,IF(BQ403*1.05>50,0.1,0.05))))