Excel: Change cells number format off an other cells value


Hi, I am using an if statement that depending on the value in B1 is a 1 or a 2 will ether return a number or a Percentage. I am trying to get excel to change the format if the value in b2 is 1 to a % format and if it is 2 to a number format. I have tried using these two rules in conditional formatting but it is not working and getting "stuck" in one of the formats =$B$1=2 format as Number =$B$1=1 Format as Percentage Here is an example that should be formatted as a percentage Excel 2007

ABCD
1 1 % Used
2 Net Seats
328-Oct-134-Nov-13
4Seat Utilization% for Actual Prod Staff11

Summray



Worksheet Formulas
CellFormula
C3=Springfield!B8
D3=C3+7
C4=IF($B$1=1,VLOOKUP(C$3,Greeley!$B$8:$BI$33,58,FALSE),VLOOKUP(C$3,Greeley!$B$8:$BI$33,12,FALSE)-(VLOOKUP(C$3,Greeley!$B$8:$BI$33,9,FALSE)))
D4=IF($B$1=1,VLOOKUP(D$3,Greeley!$B$8:$BI$33,58,FALSE),VLOOKUP(D$3,Greeley!$B$8:$BI$33,12,FALSE)-(VLOOKUP(D$3,Greeley!$B$8:$BI$33,9,FALSE)))

Here is an example as a number Excel 2007
ABCD
1 2 % Used
2 Net Seats
328-Oct-134-Nov-13
4Seat Utilization% for Actual Prod Staff9196

Summray



Worksheet Formulas
CellFormula
C3=Springfield!B8
D3=C3+7
C4=IF($B$1=1,VLOOKUP(C$3,Greeley!$B$8:$BI$33,58,FALSE),VLOOKUP(C$3,Greeley!$B$8:$BI$33,12,FALSE)-(VLOOKUP(C$3,Greeley!$B$8:$BI$33,9,FALSE)))
D4=IF($B$1=1,VLOOKUP(D$3,Greeley!$B$8:$BI$33,58,FALSE),VLOOKUP(D$3,Greeley!$B$8:$BI$33,12,FALSE)-(VLOOKUP(D$3,Greeley!$B$8:$BI$33,9,FALSE)))

Any Ideas I am willing to use VBA I was thinking of a change event but I could not figure out how to get the code to work :(


This question generated 23 answers. To proceed to the answers, click here.

This thread is current as of November 01, 2013.


For more resources for Microsoft Excel