Conditional Formatting with deleting rows

[JMWh1t3]

Hiya everyone,

I have a sheet on teams that multiple people use from multiple locations. I have conditional formatting on the sheet using an xlookup to highlight cells and rows that meet certain requirements. At the minute I am having to do the lookups via entire columns (e.g. =xlookup($A1,'Data'E:E,'Data'F:F) so it looks for the value in the first column and highlights the cells where the value in A:A is found in E:E and there is and entry in F:F. This works, but due to doing entire column calculations it is sometimes incredibly slow. I have had to format it this way because at the end of each shift we delete complete rows and if I did the lookup on E1:E500 then when the rows are deleted I end up with #Ref errors or gaps in the formatting.

Is there a way to make a range dynamic, so if i have E1:E500 and rows were deleted it would still stay as E1:E500 and not break. I tried with a Named Range also but I couldn't make it work.

I would like this to work for conditional formatting and formulas if possible. I have Countifs and Sumifs and these are calculated on full rows too as there is always different amounts of data in the sheet, between 50 to 350 rows.

 

[6StringJazzer]

Normally referencing an entire column isn't a problem unless the formula is an array formula. I don't think yours is, so I'm not sure what's slowing you down. Hard to tell without having the whole picture.

When putting formulas (or VBA code) into a post, it is a best practice to copy it directly from Excel and put it into your post to ensure that what we are looking at is what's really in your file. The formula you showed here is invalid.

The only option I can think of that does not use a full column reference is to use OFFSET. However, this has a downside that it is a volatile function, and so will be recalculated every time anything your sheet is recalculated. This could also cause your sheet calculations to slow, depending on how you're using it.

=XLOOKUP($A1,OFFSET('Data'!$E$1,0,0,500),OFFSET('Data'!$F$1,0,0,500))