I'm formatting a calendar on multiple sheets in excel, with the weekends highlighted. I'd like to format all the sheets from a table of 1s & 0s I generated to match different years. Unfortunately, I've failed to properly use named ranges or offset to achieve the effect I desire.
The left formatting uses a relative reference, =O3=1, which would be cumbersome and introduce the possibility of error as others copy in the 'data' onto 13 sheets in years hence. So, I noticed people suggesting using a named range. Here, I could not reproduce their results.
The image above uses the O3:O14
named bhbh
(since I'm just practicing on an empty sheet) and attempts to format the Dec column where =bhbh>0
. As you can see, the entire area is colored rather than by cell within the range.
The same happens when the grid is a named range. Using =offset("sheet3!O3:O10")=1
does not apply the formatting to the selected cells. I'm at a loss besides pasting the data on each sheet for relative formatting, but that seems dangerous over time.