Placeholder / Conditional Formatting Solution Needed

[ollyhughes1982]

Hi. I have the worksheet in the screenshot attached. I want the purple-themed squares to appear with a white background and purple coloured text when the smaller bottom cell is blank (i.e. the relevant cell in the first worksheet is blank). When the bottom cell is equal to the bigger cell above, I want the bottom cell to have a purple fill and purple text and the top cell to have a purple fill and white text. This is to highlight when the condition has been met and fills the the square to show it is completed. I have come up with conditional formatting to do this cell-by-cell, but there are 1,000 boxes and this will be unfeasible to repeat 1,000 times.

I only added the smaller cells (below the larger main ones) originally, so that I could do a calculation upon which the bigger cell could conditional format - if there is a why that a placeholder / watermark could be in each main larger cell, rather than having to have a smaller cell below, this would be ideal. I have manually changed the colours for the first 4 boxes as an example of what I'm trying to achieve.

As i said earlier, I want to repeat the process for all 1,000 boxes and their relevant colours.

Link to file (small in size) below:

Analogue Tracker WORKING.xlsx

Thanks in advance!

Olly.

 

[Skiff]

So you want to fill cell B3 with color white?

You don't need helper cell/row. Put new rule of conditional formatting for this cell, where formula is:
=VLOOKUP(B3;'All Completed Runs'!$A:$A,1,0)=B3

Then copy formatting of this cell to rest of the cells you want to color.
Additional (helper) rows are not necessary.

 

[ollyhughes1982]

So you want to fill cell B3 with color white?

You don't need helper cell/row. Put new rule of conditional formatting for this cell, where formula is:
=VLOOKUP(B3;'All Completed Runs'!$A:$A,1,0)=B3

Then copy formatting of this cell to rest of the cells you want to color.
Additional (helper) rows are not necessary.

Thanks. If the number hasn't been achieved in the first worksheet, I want the cell to appear white with purple text and if it is not in the first worksheet then I want it to appear with a purple fill and white text. This is so that there is always a number showing, but white background shows incomplete and purple background is complete.

 

[ollyhughes1982]

So you want to fill cell B3 with color white?

You don't need helper cell/row. Put new rule of conditional formatting for this cell, where formula is:
=VLOOKUP(B3;'All Completed Runs'!$A:$A,1,0)=B3

Then copy formatting of this cell to rest of the cells you want to color.
Additional (helper) rows are not necessary.

Could you possibly do this in the file, please? I can't get it to work, I get errors. Referencing B3 gives a circular reference, as B3 is where the formula is being entered.

 

[Skiff]

My bad, after first B3 I left ";" instead of ",".
Try now, and choose correct format in conditional formatting for this cell

=VLOOKUP(B3,'All Completed Runs'!$A:$A,1,0)=B3

BTW: as I understand you search for same number in column A of first sheet as in B3 in the second sheet. I understood that this is the condition.

 

[ollyhughes1982]

That works perfectly now. Thanks so much!

 

[ollyhughes1982]

Hi. I have hit a snag when I try and do the same going downwards on the right-hand side. Could you maybe have a look, please? I have attached a link below:

Analogue Tracker WORKING 2.xlsx

Microsoft Excel Workbook

1drv.ms

Thanks again!

 

[Fluff]

How about

=MATCH(BI4,'All Completed Runs'!$A:$A,0)

 

[ollyhughes1982]

How about

=MATCH(BI4,'All Completed Runs'!$A:$A,0)

Yes, that works. Thanks!

 

[Fluff]

Glad we could help & thanks for the feedback.