Format Cells based on specific text

[Sammy]

Hello!


I have a question that I couldn't find by searching through other posts and have been stuck on this problem for hours.


I have a row in a worksheet in which the value in cell A12 (and A13, A14, A15 till A61) contains either "P", "D" or "". Based on the value under column "A" i would like to change the number format in corresponding rows from column "F to Q"


For example, if cell "A12" contains "P" I'd like to format the cells from column "F12:Q12" as a percentage. If the cell "A12" contains "D" I'd like to format the cells from column "F12:Q12" as regular whole number with 2 decimal places. And if the cell "A12" contains "" I'd like to format the cells from column "F12:Q12" as regular whole number without any decimal places.


I didn't want to use macro; hence tried doing this using conditional formatting but when i change the parameters/locations to view other sites performance the number formatting changes after some few selections in my List Box.


Is there anyway that without using macro's I can change the number formats accordingly?


Any help would be much appreciated!


Thanks,

Sam

 

[Luke M]

If you have XL 2007+, then yes. Select your range F12:Q12. Conditional Format, new rule. Base rule on formula. Formula will be:

=$A2="P"

Click on the format button, then Number tab. Select percentage (and number of decimals). Ok out.

Repeat as needed for other settings.


If this is what you already did, could you elaborate on what this meant?

but when i change the parameters/locations to view other sites performance the number formatting changes after some few selections in my List Box.

 

[Sammy]

Hi Luke,


Yes, I am using 2007 and already tried the steps mentioned. It works fine, but not for long!


Basically I have a dashboard which displays data for different locations in percentages and numbers for different parameters, I am using a list box to change locations to view that particular locations performance, which means the format for parameter "1" under location "A" might not be the same as parameter "1" under location "B". So when i change from location "A" to location "B" etc... the formatting stays for only 3/4 locations then the formatting stops working.


Let me know in case you need more info.


Thanks,

Sam

 

[Luke M]

Hmm. Well, I first missed that you were moving by column, not by row, so to correct my earlier formula it should be an absolute row, not column:

=A$2="P"

When you say a list box, what kind are you using?

a) Data Validation drop down

b) Forms Menu List Box

c) ActiveX Menu List Box


The last part of your sentence sounds more like a bug issue, if things work for a little bit, but then stop. Have you tried creating the basic setup in a new workbook to test for corruption maybe?

 

[Sammy]

I'm using a Form Menu List Box wherein I have a list of the locations.


I did try using the file under different computers but with the same results.

 

[Luke M]

So, the list box is connected to a cell and gives a numerical value, and the cells in col 1 contain some sort of lookup formula, I presume?

Rather than just trying on a different computer, trying on a new workbook would tesst if the theory is correct and/or there's something else goofy going on in workbook.

Trying to debug the problem, can we start small and just try to get 1 cell to change formatting based on another. Then, try having multiple cells change. Then, copy/extend the rule to other cells in other columns. Trying ot figure out at what point does the problem emerge. =/

 

[Sammy]

Yes, the list box is connected to a cell and gives a numerical value, and the cells in col A contain a vlookup formula


Well, I used the below formula on top of my actual formula and it works fine when I change the locations, even though I ended up with a messy and LENGTHY formula Parts of the trade for not wanting to use a macro!


=if(a12=1, text(formula, "0.00%"), if(a12=2, text(formula, "0.00"), text(formula,"0")))


Thanks a lot for your time and help though. Much appreciated!


Thanks,

Sam