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
Here is an example as a number
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
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
Cell Formulas | ||
---|---|---|
Range | Formula | |
C3 | =Springfield!B8 | |
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))) | |
D3 | =C3+7 | |
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 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | 2 | % Used | ||||
2 | Net Seats | |||||
3 | 28-Oct-13 | 4-Nov-13 | ||||
4 | Seat Utilization% for Actual Prod Staff | 91 | 96 | |||
Summray |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C3 | =Springfield!B8 | |
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))) | |
D3 | =C3+7 | |
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