Change cells number format off an other cells value

B

bschwartz

Well-known Member

Joined

Jan 5, 2013

Messages

1,367

• Nov 1, 2013

• #2

Strange, it works just fine for me using your same formulas in conditional formatting.

Cell B1 = 2:

Excel 2010
	A	B	C	D
1		2	% Used
2			Net Seats
3			28-Oct-13	4-Nov-13
4	Seat Utilization% for Actual Prod Staff		1.00	1.00
Conditional Conditional Formatt

Cell B1 = 1:

Excel 2010
	A	B	C	D
1		1	% Used
2			Net Seats
3			28-Oct-13	4-Nov-13
4	Seat Utilization% for Actual Prod Staff		100.00%	100.00%
Conditional Conditional Formatt

My conditional formatting rules are:

Rule 1:
1) Applies to: =$C$4:$D$4
2) Use formula: =$B$1=2
3) Format --> Number --> Number

Rule 2:
1) Applies to: =$C$4:$D$4
2) Use formula: =$B$1=1
3) Format --> Number --> Percentage

 

Upvote 0

M

MDuff

Well-known Member

Joined

Dec 29, 2002

Messages

529

Office Version

1. 365

Platform

1. Windows

• Nov 1, 2013

• #3

Not sure that the same logic i used on thing to note I noticed is I am in Excel 2007 and your on 2010 not sure if that makes a difference

 

Upvote 0

B

bschwartz

Well-known Member

Joined

Jan 5, 2013

Messages

1,367

• Nov 2, 2013

• #4

MDuff said:

Not sure that the same logic i used on thing to note I noticed is I am in Excel 2007 and your on 2010 not sure if that makes a difference

Click to expand...

You don't know if it's the same logic used? I don't really understand that sentence, but if you didn't use those formulas before, do you want to try it and post the result here? It is working on my version of your spreadsheet.

And I don't believe the version should make a difference. This should work the same on 2007.

 

Upvote 0

M

MDuff

Well-known Member

Joined

Dec 29, 2002

Messages

529

Office Version

1. 365

Platform

1. Windows

• Nov 3, 2013

• #5

Ok I tested this set up on a new worksheet and it works so now I am not sure why it will not work on the book I originally Posted


I have:

Rule 1:
1) Applies to: =$C$4:$D$4
2) Use formula: =$B$1=2
3) Format --> Number --> Number


Rule 2:
1) Applies to: =$C$4:$D$4
2) Use formula: =$B$1=1
3) Format --> Number --> Percentage

No Stop if true Selected

and this is what I get if 1 or 2 is selected...on the workbook originally posted

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)))

this is on the new workbook that works

Excel 2007
	A	B	C	D	E	F
1		2	% Used
2			Net Seats
3			28-Oct-13	4-Nov-13
4	Seat Utilization% for Actual Prod Staff		300	1
5
6
7
8
9					1	0.3
10					2	300
Sheet1

Cell Formulas
Range	Formula
C4	=VLOOKUP(B1,$E$9:$F$10,2,FALSE)

Any Ideas why it works in one sheet and not an other?

 

Upvote 0

B

bschwartz

Well-known Member

Joined

Jan 5, 2013

Messages

1,367

• Nov 3, 2013

• #6

Do you have any other rule on the first worksheet that might be overriding the new rules?

 

Upvote 0

B

bschwartz

Well-known Member

Joined

Jan 5, 2013

Messages

1,367

• Nov 3, 2013

• #7

Nevermind, this was wrong.

 

Last edited: Nov 3, 2013

Upvote 0

M

MDuff

Well-known Member

Joined

Dec 29, 2002

Messages

529

Office Version

1. 365

Platform

1. Windows

• Nov 3, 2013

• #8

I think I found something out and I am sorry i left it it out I am using a Combo Box to Populate the 1 and 2 in B2 If I use the Combo Box it will not change the format BUT if I manly Type the number if and hit enter it will change it so I am thinking it must be something with Combo Box and excel not "refreshing" to trigger the conditional formatting.

I hope this additional information helps and any ideas on how I can still use the Combo Box and the Conditional Formatting?

thanks

 

Upvote 0

B

bschwartz

Well-known Member

Joined

Jan 5, 2013

Messages

1,367

• Nov 3, 2013

• #9

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

 

Upvote 0

RoryA

MrExcel MVP, Moderator

Joined

May 2, 2008

Messages

42,255

Office Version

1. 365
2. 2016

Platform

1. Windows
2. MacOS

• Nov 3, 2013

• #10

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.

 

Upvote 0