Change cells number format off an other cells value

[MDuff]

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
	A	B	C	D
1		1	% Used
2			Net Seats
3			28-Oct-13	4-Nov-13
4	Seat Utilization% for Actual Prod Staff		1	1
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)))

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

 

[bschwartz]

Is the value in B1 manually entered, the result of a formula or selected from a data validation list? I seem to recall that Excel 2007 was buggy in this regard, especially if using a DV list.

RoryA, you probably missed his most recent post, in which he said that he is using a combo box.

Regards.

 

[MDuff]

Right click on the Combo Box, and hit Format Control. Copy/paste to this forum what is in the input box for Cell Link.

From the Combo Box:

Input Range: $C$1:$C$2
Cell Link: $B$1
Drop Down Lines: 2

 

[MDuff]

Is the value in B1 manually entered, the result of a formula or selected from a data validation list? I seem to recall that Excel 2007 was buggy in this regard, especially if using a DV list.

yes it's the result of a combo box entry if I many enter in b1 and hit enter the Conditional formatting will work but when I change with the Combo Box it will not

 

[RoryA]

I suspect it's the same bug though (I think the two controls work similarly).

 

[bschwartz]

I'm confused... How does C1:C2 give you values 1 and 2? Are you using custom number formatting to hide the actual values?

Otherwise, I don't know what the issue is. It works just fine for me, using a similar combo box.

If it works better on another worksheet, maybe move all the data to a new worksheet and try it again?

 

[bschwartz]

I suspect it's the same bug though (I think the two controls work similarly).

Are you saying the reason it works fine for me, but not for MDuff is because I am on Excel 2010?

 

[MDuff]

I'm confused... How does C1:C2 give you values 1 and 2? Are you using custom number formatting to hide the actual values?

Otherwise, I don't know what the issue is. It works just fine for me, using a similar combo box.

If it works better on another worksheet, maybe move all the data to a new worksheet and try it again?

What gives me the 1 and 2 is the cell Link to b1 whats in c1;c2 are the names to select in the combo box

 

[bschwartz]

What gives me the 1 and 2 is the cell Link to b1 whats in c1;c2 are the names to select in the combo box

Oh, I see. Well it still works on my worksheet. Might be that bug that RoryA mentioned...

 

[RoryA]

Are you saying the reason it works fine for me, but not for MDuff is because I am on Excel 2010?

Yep. I am so glad we skipped 2007 at work. If it works with manual entry, I am 99% certain it's that bug.

 

[MDuff]

thanks but on the BUG any ideas on VBA that may help