How to use OFFSET Function with Conditional Formatting

M

Marcie Be

Board Regular
Joined
Jun 25, 2020
Messages
124
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
What is the best way to use the OFFSET Function with CF

I have a number of CF Formula that only apply to alternate rows. I have applied the formula long hand on every other row over the first 40 rows of my document. I now need to expand to cover 100+ rows. This manual method is not efficicient.

I do have a few OFFSET formula example =OFFSET($C5,-MOD(ROW(S4),2),0)="Record Change (RC)"

How would I apply the OFFSET Function to the following CF =NOT(ISBLANK($S5))

Other CF Formual that I need to OFFSET include

Cell Value =0

=IF($AO5="","",IF($AP5="","",IF(DAYS($AO5,$AP5),IF($AO6<=1,"True","False"))))

Formulae to be OFFSET are applied from Row 5 downwards

Thak you in advance

Best Regards

Marcie Be
 
Sort by date Sort by votes
Yes I will take a localised approach to hopefully resolve the issue.
Thanks again
Best Regards
Marcie Be
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
You are welcome.

Note that if you have multiple rules on the same range, you need to play special attention to the order you place them in to process, and choose whether it should stop processing or go on to the other rules. This really applies to situations when you have multiple CF rules on a single range that might be true at the same time.
 
Upvote 0
Joe4 said:
You are welcome.

Note that if you have multiple rules on the same range, you need to play special attention to the order you place them in to process, and choose whether it should stop processing or go on to the other rules. This really applies to situations when you have multiple CF rules on a single range that might be true at the same time.
Click to expand...
@Joe4 I have stripped out and re-entered the CFs that are not working properly and I still have the same issue.
Here is the XL2BB can you take a look please
The two areas that I have been trying to fix are Columns S & U, and AJ & AN
The CFs work for rows 5 to 10, after that they do not.
I have applied a continuous range from S5:S116, U5:U116,AJ5:AJ116 and AN5:AN116

Tracker_BETA Test Version V5_MBW_14-09-20_New Layout2_ DO NOT USE.xlsm
S
729/06/2020
PLM2 Tracker
Cells with Conditional Formatting
CellConditionCell FormatStop If True
R5:T116Expression=$C5="Drawing Update Only"textNO
S5:S16,S18:S116Expression=OFFSET($C5,-MOD(ROW(S4),2),0)="Drawing Update Only"textNO
R5:T116Expression=$C5="Cost Only"textNO
S5:S16,S18:S116Expression=OFFSET($C5,-MOD(ROW(S4),2),0)="Cost Only"textNO
R5:T116Expression=$C5="Record Change (RC)"textNO
S5:S16,S18:S116Expression=OFFSET($C5,-MOD(ROW(S4),2),0)="Record Change (RC)"textNO
R5:T116Expression=$C5="Data Transfer"textNO
S5:S16,S18:S116Expression=OFFSET($C5,-MOD(ROW(S4),2),0)="Data Transfer"textNO
S5:S116Expression=AND(ISEVEN(ROW()),$S5="")textNO
S5:S116Expression=AND(ISEVEN(ROW()),$S6<$U6)textNO
S5:S116Expression=AND(ISEVEN(ROW()),$S6>=$U6)textNO
S5:S16,S18:S116Expression=AND(ISODD(ROW()),NOT(ISBLANK($S5)))textNO
 
Upvote 0
Can you also post the data from rows 5-16, so I can try to recreate your data sheet on my side?
 
Upvote 0
Joe4 said:
Can you also post the data from rows 5-16, so I can try to recreate your data sheet on my side?
Click to expand...

I hope this is correct

Tracker_BETA Test Version V5_MBW_14-09-20_New Layout2_ DO NOT USE.xlsm
ABCDEFGHIJKLMNOPQRSTUAIAJAKALAMANASATAUAVBBBCBDBE
5EXAMPLESEng ReleaseThis is a Eng Release Change RequestYDR-123456 (3D) CR-789101 CN-111213 DR-141516 (2D) TF-123456 19/06/202019/07/202030MBWA N EngineerCompleteDR-12345CR-789101CN-111213DR-141516TF-123456DR-1234501/08/202015CompleteCN-11121315/07/2020YN15CompleteDR-14151602/07/202021CompleteTF-12345610/07/20204Complete
614/08/202016/08/202027/07/202030/07/202026/07/202023/07/202014/07/2020
7Fast Track (CR)This is a Fast Track Change RequestNDR-891011 CR-372187 CN-089800 03/07/202009/07/20206MBWA N EngineerCompleteDR-891011CR-372187CN-089800N/AN/ADR-89101129/06/20203CompleteCN-08980006/07/2020YN3Complete  
802/07/202002/07/202003/07/202009/07/2020
9Cost OnlyThis is a Cost Only Change RequestYCN-192021 TF-20191831/08/2020 MBWA N EngineerAwaiting Audit ApprovalN/AN/ACN-192021N/ATF-201918 CN-19202106/07/20203Complete TF-20191810/07/20201Complete
1011/07/202009/07/202011/07/2020
11Data TransferThis is a Data Transfer RequestYTF-10987611/09/202011/09/20200MBWA N EngineerCompleteN/AN/AN/AN/ATF-109876   TF-10987601/08/20200Complete
12 01/08/2020
13Record Change (RC)This is a Record Change RequestNCN-293031 DR-00123405/08/202005/08/20200MBWA N EngineerCompleteN/AN/ACN-293031DR-001234N/A CN-29303103/08/2020NY3CompleteDR-00123405/08/20201Complete 
1409/08/202006/08/202008/08/202006/08/2020
15Fast Track (CN)This is a Fast Track Change NoticeYDR-98765 CN-201918 DR785791 TF-30987630/09/2020 MBWA N EngineerWork in ProgressDR-98765N/ACN-201918DR-785791TF-309876DR-9876528/08/20205CompleteCN-20191801/09/2020YN16OpenDR-78579102/09/20203CompleteTF-30987605/09/202010Complete
1601/09/202002/09/202006/09/202005/09/202005/09/202015/09/2020
PLM2 Tracker
Cell Formulas
RangeFormula
M5,M15,M13,M11,M9,M7M5=IF($R5="","N/A",$R5)
N5,N15,N13,N11,N9,N7N5=IF($AA5="","N/A",$AA5)
O5,O15,O13,O11,O9,O7O5=IF($AI5="","N/A",$AI5)
P5,P15,P13,P11,P9,P7P5=IF($AS5="","N/A",$AS5)
Q5,Q15,Q13,Q11,Q9,Q7Q5=IF($BB5="","N/A",$BB5)
AJ6,AJ16,AJ14,AJ12,AJ10,AJ8AJ6=IF($C5="COST ONLY",AJ5+5,IF($C5="Fast Track (CR)",S6+1,IF(OR($C5="DATA TRANSFER",AT6=""),"",AT6+1)))
I5,I15,I13,I11,I9,I7I5=IF(H5="","",DAYS(H5,G5))
T5,T15,T13,T11,T9,T7T5=IF($S5="","",DATEDIF($S5,MIN($U6,TODAY()),"D"))
AM5,AM15,AM13,AM11,AM9,AM7AM5=IF($AJ5="","",DATEDIF($AJ5,MIN($AN6,TODAY()),"D"))
AU5,AU15,AU13,AU11,AU9,AU7AU5=IF($AT5="","",DATEDIF($AT5,MIN($AV6,TODAY()),"D"))
BD5,BD15,BD13,BD11,BD9,BD7BD5=IF($BC5="","",DATEDIF($BC5,MIN($BE6,TODAY()),"D"))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AN5:AN15,AN18:AN116Expression=OFFSET($C5,-MOD(ROW(AJ4),2),0)="Data Transfer"textNO
AN5:AN116Expression=AND(ISEVEN(ROW()),ISBLANK($AN5))textNO
AN5:AN116Expression=AND(ISODD(ROW()),$AN5="Complete")textNO
AN5:AN116Expression=AND(ISEVEN(ROW()),$AN6<=$AJ6)textNO
AJ5:AJ15,AJ18:AJ116Expression=OFFSET($C5,-MOD(ROW(AJ4),2),0)="Data Transfer"textNO
AI5:AM116Expression=$C5="Data Transfer"textNO
U5:U16,U18:U116Expression=OFFSET($C5,-MOD(ROW(U4),2),0)="Drawing Update Only"textNO
U5:U16,U18:U116Expression=OFFSET($C5,-MOD(ROW(U4),2),0)="Data Transfer"textNO
U5:U16,U18:U116Expression=OFFSET($C5,-MOD(ROW(U4),2),0)="Record Change (RC)"textNO
R5:T116Expression=$C5="Drawing Update Only"textNO
S5:S16,S18:S116Expression=OFFSET($C5,-MOD(ROW(S4),2),0)="Drawing Update Only"textNO
AV5:AV16,AV18:AV116Expression=OFFSET($C5,-MOD(ROW(AV4),2),0)="Fast Track (CR)"textNO
AV5:AV16,AV18:AV116Expression=OFFSET($C5,-MOD(ROW(AV4),2),0)="Cost Only"textNO
AV5:AV16,AV18:AV116Expression=OFFSET($C5,-MOD(ROW(AV4),2),0)="Data Transfer"textNO
BE5:BE16,BE18:BE116Expression=OFFSET($BE5,-MOD(ROW(BE4),2),0)="Complete"textNO
AV5:AV16,AV18:AV116Expression=AND(ISEVEN(ROW()),ISBLANK($AV4))textNO
AN5:AN116Expression=AND(ISEVEN(ROW()),$AN6>$AJ6)textNO
R5:T116Expression=$C5="Cost Only"textNO
U5:U16,U18:U116Expression=OFFSET($C5,-MOD(ROW(U4),2),0)="Cost Only"textNO
U5:U16,U18:U116Expression=AND(ISODD(ROW()),$U5="")textNO
U5:U16,U18:U116Expression=AND(ISEVEN(ROW()),ISBLANK($U5))textNO
AJ5:AJ15,AJ18:AJ116Expression=AND(ISEVEN(ROW()),$AJ5="")textNO
H5:H116Cell Value=""textNO
AJ5:AJ116Expression=AND(ISEVEN(ROW()),$AJ6<$AN6)textNO
AJ5:AJ116Expression=AND(ISEVEN(ROW()),$AJ6>=$AN6)textNO
AN16Expression=$AN16<>""textNO
BB5:BH5,BB7:BH116,BB6:BE6Expression=IF($E5="N",TRUE)textNO
M5:Q116Cell Value="N/A"textNO
P17:P116,P5:P15Expression=$AV6>$AT6textNO
P17:P116,P5:P15Expression=$AV6<=$AT6textNO
O5:O15,O17:O116Expression=$AN6>$AJ6textNO
O5:O15,O17:O116Expression=$AN6<=$AJ6textNO
M17:M116,M5:M15Expression=$U6<=$S6textNO
AS5:AU116Expression=$C5="Data Transfer"textNO
AS5:AU116Expression=$C5="Cost Only"textNO
AJ5:AJ15,AJ18:AJ116Expression=AND(ISODD(ROW()),NOT(ISBLANK($AJ5)))textNO
U5:U116Expression=AND(ISODD(ROW()),$U5="Complete")textNO
AN5:AN15,AN18:AN116Expression=AND(ISODD(ROW()),$AN5)textNO
U5:U116Expression=AND(ISEVEN(ROW()),$U6>$S6)textNO
U5:U116Expression=AND(ISEVEN(ROW()),$U6<=$S6)textNO
S5:S16,S18:S116Expression=OFFSET($C5,-MOD(ROW(S4),2),0)="Cost Only"textNO
AV5:AV16,AV18:AV116Expression=AND(ISEVEN(ROW()),$AV5<=$AT5)textNO
AS5:AU116Expression=$C5="Fast Track (CR)"textNO
AT5:AT15Expression=OFFSET($C5,-MOD(ROW(S4),2),0)="Fast Track (CR)"textNO
AT5:AT15Expression=OFFSET($C5,-MOD(ROW(S4),2),0)="Data Transfer"textNO
AV5:AV16,AV18:AV116Expression=AND(ISEVEN(ROW()),$AV5>$AT5)textNO
R5:T116Expression=$C5="Record Change (RC)"textNO
S5:S16,S18:S116Expression=OFFSET($C5,-MOD(ROW(S4),2),0)="Record Change (RC)"textNO
R5:T116Expression=$C5="Data Transfer"textNO
O5:O15,O17:O116Expression=AB$5=""textNO
M17:M116,M5:M15Expression=$U6>$S6textNO
O5:O15,O17:O116Cell Valuebetween 1 and 2textNO
O5:O15,O17:O116Expression=OR($AM5=3,$AM5=4)textNO
AT5:AT15Expression=OFFSET($C5,-MOD(ROW(S4),2),0)="Cost Only"textNO
S5:S16,S18:S116Expression=OFFSET($C5,-MOD(ROW(S4),2),0)="Data Transfer"textNO
S5:S116Expression=AND(ISEVEN(ROW()),$S5="")textNO
S5:S116Expression=AND(ISEVEN(ROW()),$S6<$U6)textNO
S5:S116Expression=AND(ISEVEN(ROW()),$S6>=$U6)textNO
AT5:AT15Expression=AND(ISEVEN(ROW()),ISBLANK($AT5))textNO
BE17:BH116,BE5:BH5,BE6,BE7:BH15Expression=OFFSET($E5,-MOD(ROW(BE6),2),0)="N"textNO
O5:O15,O17:O116Expression=$AM5>=5textNO
AT5:AT15Expression=AND(ISEVEN(ROW()),$AT6<$AV6)textNO
AT5:AT15Expression=AND(ISEVEN(ROW()),$AT6>=$AV6)textNO
AT5:AT15Expression=AND(ISODD(ROW()),NOT(ISBLANK($AT5)))textNO
S5:S16,S18:S116Expression=AND(ISODD(ROW()),NOT(ISBLANK($S5)))textNO
AV5:AV16,AV18:AV116Expression=OFFSET($AV5,-MOD(ROW(AV4),2),0)="Complete"textNO
I5:I116Expression=$H5=""textNO
AM5:AM116Expression=$AJ5=""textNO
AM5:AM116Cell Valuebetween 0 and 2textNO
AM5:AM116Cell Valuebetween 3 and 4textNO
AM5:AM116Cell Value>=5textNO
T5:T116Expression=$S5=""textNO
G5:G116Expression=IF(G5="",TODAY(),"")textNO
G5:G116Expression=AND(G5>=TODAY()-20,G5<=TODAY()-10)textNO
G5:G116Expression=AND(G5>=TODAY()-10,G5<=TODAY()-5)textNO
G5:G116Expression=AND(G5>=TODAY()-5,G5<=TODAY()-0)textNO
G5:G116Expression=G5<=TODAY()-20textNO
G5:G116Expression=G5<=TODAY()-10textNO
G5:G116Expression=G5<=TODAY()-5textNO
G5:G16Expression=IF($G5 -TODAY(),"",IF($G5-TODAY(),"DAYS>=20", "False"))textNO
I5:I116Cell Valuebetween 0 and 9textNO
I5:I116Cell Valuebetween 10 and 19textNO
I5:I116Cell Value>=20textNO
H5:H116Cell Value<=$G5textNO
H5:H116Expression=IF($I5>=10,OR($I5<20,"False"))textNO
H5:H116Cell Value>$G5textNO
BD5:BD116Expression=$BC5=""textNO
Q5:Q116Expression=$BC5=""textNO
P5,P7:P116Expression=$AT5=""textNO
N5:N116Expression=$AB5=""textNO
Q5:Q116Expression=$BD5<=2textNO
Q5:Q116Expression=OR($BD5=3,$BD5=4)textNO
Q5:Q116Expression=$BD5>=5textNO
P5,P7:P116Expression=$AU5<=2textNO
P5,P7:P116Expression=OR($AU5=3,$AU5=4)textNO
P5,P7:P116Expression=$AU5>=5textNO
N5:N116Expression=$AD5<=2textNO
N5:N116Expression=OR($AD5=3,$AD5=4)textNO
BD5:BD116Cell Valuebetween 0 and 2textNO
BD5:BD116Cell Valuebetween 3 and 4textNO
BD5:BD116Cell Value>=5textNO
AU5:AU116Expression=$AT5=""textNO
AU5:AU116Cell Valuebetween 0 and 2textNO
N5:N116Expression=$AD5>=5textNO
T5:T116Cell Valuebetween 1 and 2textNO
T5:T116Cell Valuebetween 3 and 4textNO
T5:T116Cell Value>=5textNO
AU5:AU116Cell Valuebetween 3 and 4textNO
M17:M116,M5:M15Expression=$S5=""textNO
M5:M116Expression=$T5<=2textNO
M5:M116Expression=OR($T5=3,$T5=4)textNO
M5:M116Expression=$T5>=5textNO
BE5:BE16,BE18:BE116Expression=AND(ISODD(ROW()),$BE5)textNO
AU5:AU116Cell Value>=5textNO
AV5:AV16,AV18:AV116Expression=AND(ISODD(ROW()),$AV5)textNO
BE16:BH16Expression=OFFSET($E16,-MOD(ROW(#REF!),2),0)="N"textNO
AJ16Expression=OFFSET($C16,-MOD(ROW(AJ15),2),0)="Data Transfer"textNO
AJ16Expression=AND(ISEVEN(ROW()),$AJ16="")textNO
AJ16Expression=AND(ISEVEN(ROW()),#REF!<#REF!)textNO
AJ16Expression=AND(ISEVEN(ROW()),#REF!>=#REF!)textNO
AJ16Expression=AND(ISODD(ROW()),NOT(ISBLANK($AJ16)))textNO
M16Expression=#REF!<=#REF!textNO
M16Expression=#REF!>#REF!textNO
M16Expression=$S16=""textNO
O16Expression=#REF!>#REF!textNO
O16Expression=#REF!<=#REF!textNO
O16Expression=AB$5=""textNO
O16Cell Valuebetween 1 and 2textNO
O16Expression=OR($AM16=3,$AM16=4)textNO
O16Expression=$AM16>=5textNO
P16Expression=#REF!>#REF!textNO
P16Expression=#REF!<=#REF!textNO
AN16Expression=OFFSET($C16,-MOD(ROW(AJ15),2),0)="Data Transfer"textNO
AN16Expression=AND(ISEVEN(ROW()),ISBLANK($AN16))textNO
AN16Expression=AND(ISEVEN(ROW()),#REF!<=$AJ$6)textNO
AN16Expression=AND(ISEVEN(ROW()),#REF!>#REF!)textNO
AN16Expression=OFFSET($AN16,-MOD(ROW(AN15),2),0)="Complete"textNO
AN16Expression=AND(ISODD(ROW()),$AN16)textNO
AT16Expression=OFFSET($C16,-MOD(ROW(S15),2),0)="Fast Track (CR)"textNO
AT16Expression=OFFSET($C16,-MOD(ROW(S15),2),0)="Data Transfer"textNO
AT16Expression=OFFSET($C16,-MOD(ROW(S15),2),0)="Cost Only"textNO
AT16Expression=AND(ISEVEN(ROW()),ISBLANK($AT16))textNO
AT16Expression=AND(ISEVEN(ROW()),#REF!<#REF!)textNO
AT16Expression=AND(ISEVEN(ROW()),#REF!>=#REF!)textNO
AT16Expression=AND(ISODD(ROW()),NOT(ISBLANK($AT16)))textNO
S16Expression=AND(ISEVEN(ROW()),#REF!>=#REF!)textNO
S16Expression=AND(ISEVEN(ROW()),#REF!<#REF!)textNO
 
Upvote 0
OK, one final question.
In this example, which cells are not working the way you expect (and what is your expected results for those particular cells)?
 
Upvote 0
Joe4 said:
OK, one final question.
In this example, which cells are not working the way you expect (and what is your expected results for those particular cells)?
Click to expand...
I am not in front of my computer now for a few hours but from memory
Column S & U Row 10 onwards
Columns AJ & AN Row 10 onwards too definitely rows from 16 onwards.
Thank you
Best Regards
Marcie Be
 
Upvote 0
Results expected are when S is greater than U S should be green
When S is less than U S should be red
Similarly when U is greater than S U should be red and when U is less than S U should be green.
Same goes for AJ & AN
 
Upvote 0
I just looked closer at your image. Are you using "merged cells"?
If you are, I highly recommend removing them! They are the devil! They cause all sorts of problems.
Most advanced programmers avoid them like the plague. They cause nothing but headaches.

Here are a few articles that discuss some of the issues:
theexcelclub.com

Stop! Do Not Merge Cells in Excel - Heres why with fixes - The Excel Club

Find out why you should not merge cells in excel, the problems merged cells can cause and how you can overcome this problem.
theexcelclub.com
.
edu.gcfglobal.org

Excel Tips: Why You Should Avoid Merging Cells

Use the Center Across Selection feature to easily merge cells in Excel.
edu.gcfglobal.org

In addition to the issues shown there, it also can cause problems wth VBA.
I usually have two rules when it comes to merged cells:
1. Don't use them
2. Don't try to work with files that use them

So I would recommend removing them, and see if that works. You can always increase the individual row heights, if you want them to be taller.
 
Upvote 0
Joe4 said:
I just looked closer at your image. Are you using "merged cells"?
If you are, I highly recommend removing them! They are the devil! They cause all sorts of problems.
Most advanced programmers avoid them like the plague. They cause nothing but headaches.

Here are a few articles that discuss some of the issues:
theexcelclub.com

Stop! Do Not Merge Cells in Excel - Heres why with fixes - The Excel Club

Find out why you should not merge cells in excel, the problems merged cells can cause and how you can overcome this problem.
theexcelclub.com
.
edu.gcfglobal.org

Excel Tips: Why You Should Avoid Merging Cells

Use the Center Across Selection feature to easily merge cells in Excel.
edu.gcfglobal.org

In addition to the issues shown there, it also can cause problems wth VBA.
I usually have two rules when it comes to merged cells:
1. Don't use them
2. Don't try to work with files that use them

So I would recommend removing them, and see if that works. You can always increase the individual row heights, if you want them to be taller.
Click to expand...
Thank you @Joe4 for all your help. Thanks for the articles.
How can I overcome the need for merged cells vertically?
I am ok with removing the hoizontal merged cells.
Thanks again
Marcie Be
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
Conditional Formatting (CF Rule) Using INDIRECT Function
Replies
5
Views
269
Fluff
Fluff
nosajnid89
  • Solved
Can 'Conditionally Formatted' cells be use as a logic in IF statements etc?
2
Replies
14
Views
2K
RoryA
RoryA
D
  • Solved
Combining two INDEX and MATCH functions
Replies
3
Views
259
felixstraube
F
M
  • Question
VBA copy data in range
Replies
0
Views
843
MYMunshi
M
N
  • Solved
offset column for the matched cell of column A in conditional formatting
Replies
5
Views
996
Fluff
Fluff

Forum statistics

Threads
1,214,782
Messages
6,121,532
Members
449,037
Latest member
tmmotairi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top