Return double amount in case cell is colored (w/o VBA)

[Mandy_84]

Hi all! is that possible to use formula and determine the color at the same time without VBA? For example, Column B has following formula "=IF(MONTH(A1)+1=MONTH(TODAY()),"50","")", but let's say that row 2 and 3 is yellow, so I need the formula to show double of 50 (100), but still based on the formula that catches only last month entries.


[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR="class: outer_border"]
[TD="width: 126, align: right"]8/31/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR="class: outer_border"]
[TD="width: 126, align: right"]8/3/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR="class: outer_border"]
[TD="width: 126, align: right"]8/31/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR="class: outer_border"]
[TD="width: 126, align: right"]8/31/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR="class: outer_border"]
[TD="width: 126, align: right"]7/31/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks

 

[etaf]

are these cells coloured based on any conditional format rules

 

[Mandy_84]

are these cells coloured based on any conditional format rules

Only the rows 2 & 3 are colored (manually, not on any conditional format rules), the rest is white as normal

 

[etaf]

are they always row 2 and 3
or can it be any row

note "50" is entered as text and NOT a number , you just need 50

=IF(AND(MONTH(A1)+1=MONTH(TODAY()),OR(ROW()=2,ROW()=3)),100,IF(MONTH(A1)+1=MONTH(TODAY()),50,0))

 

[Rick Rothstein]

Call up the Name Manager dialog box (Formulas tab, Defined Names panel, Name Manager button). Click the New button when the dialog box appears... put IsYellow in the Name field and then put this formula in the "Refers to" field...

=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-1))

and finish off by clicking the OK button followed by click the Close button. Now, but this formula in cell B1 and copy it down...

=IF(MONTH(A1)+1=MONTH(TODAY()),IF(IsYellow,100,50),"")

 

[Mandy_84]

are they always row 2 and 3
or can it be any row

note "50" is entered as text and NOT a number , you just need 50

=IF(AND(MONTH(A1)+1=MONTH(TODAY()),OR(ROW()=2,ROW()=3)),100,IF(MONTH(A1)+1=MONTH(TODAY()),50,0))

It can be any row, not only 2,3. Hence any row can be colored, meaning it should return 100, instead of 50

 

[Rick Rothstein]

Call up the Name Manager dialog box (Formulas tab, Defined Names panel, Name Manager button). Click the New button when the dialog box appears... put IsYellow in the Name field and then put this formula in the "Refers to" field...

=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-1))

and finish off by clicking the OK button followed by click the Close button. Now, but this formula in cell B1 and copy it down...

=IF(MONTH(A1)+1=MONTH(TODAY()),IF(IsYellow,100,50),"")

Sorry, I gave you the wrong formula for the "Refers to" field, use this instead...

=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-1))=6

 

[Mandy_84]

Call up the Name Manager dialog box (Formulas tab, Defined Names panel, Name Manager button). Click the New button when the dialog box appears... put IsYellow in the Name field and then put this formula in the "Refers to" field...

=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-1))

and finish off by clicking the OK button followed by click the Close button. Now, but this formula in cell B1 and copy it down...


=IF(MONTH(A1)+1=MONTH(TODAY()),IF(IsYellow,100,50),"")

U r a monster of Excel! thanks a ton! works!

 

[Mandy_84]

Sorry, I gave you the wrong formula for the "Refers to" field, use this instead...

=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-1))=6

gives me 50 for yellows as well, not 100 ((((

 

[Mandy_84]

Sorry, I gave you the wrong formula for the "Refers to" field, use this instead...

=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),0,-1))=6

Hi Rick.
Any idea why it doesn't show 100 for yellow background cell? It gives 50 for all (((
Thanks