How do I show in a cell where counted values in a table has stopped using named referencing horisontally and vertically

[Tosca White]

I have a table that is referenced A-V at the top and 1-24 on the side (My own referencing). I want to show in a cell where the counted colour cells stop e.g. A15 or P10 or wherever it stops. Which formula can I use to show the horizontal and vertical reference where the count in the cell stops.

 

[DonkeyOte]

Perhaps you could elaborate regards "where the countered colour cells stop". Judging by the lack of response thus far I suspect people are struggling to visualise.

 

[Tosca White]

I conditionally formatted the table's cells so that if (using countif function)
you enter 1 in each cell it turns pink. If you enter 2 it turns green.
I would like a formula to tell me the reference where my last entry of 1 is in the table. Top column cells I referenced as A-V, Side rows 1-24. I want the formula to return P2 if my last pink (1) entry stops at P2 for example.

 

[DonkeyOte]

If we assume A:V appears in B1:W1 and 1 to 24 appears in A2:A25 with values in resulting matrix...

Address of Last 1:
=IF(COUNTIF($B$2:$W$25,1)=0,"n/a",CELL("address",INDIRECT("R"&SUBSTITUTE(MAX(IF($B$2:$W$25=1,ROW($B$2:$B$25)+COLUMN($B$2:$W$2)/1000)),".","C"),FALSE)))
confirmed with CTRL + SHIFT + ENTER

Address of Last 2 (as above except for modified value):
=IF(COUNTIF($B$2:$W$25,2)=0,"n/a",CELL("address",INDIRECT("R"&SUBSTITUTE(MAX(IF($B$2:$W$25=2,ROW($B$2:$B$25)+COLUMN($B$2:$W$2)/1000)),".","C"),FALSE)))
confirmed with CTRL + SHIFT + ENTER

Note the Array entry requirement.

The abvoe gives greater weight to row than column, ie a 2 in B24 is considered "later" than a 2 in W10 (row trumps column so to speak)

 

[Tosca White]

Hello again... Because I have a couple of headings and current date and time at the top, A starts at B8 and V ends at W8 then 1 starts at A9 and ends with 24 at A32. I suppose I can just use your formula and change where my cells start and end. You must be an Excel genius from what I can see! I will try this and let you know. Thanks Tosca

 

[Tosca White]

Just a note, my cells where I enter the 1's and 2's are not formatted directly. I have =COUNTIF(B9:W32,1) in column AB row 34 and
=COUNTIF(B9:W32,2) in column AB row 35 and formatted the text in this cell as white so nobody can see it. Would this make a difference to your formula application for my schedule?

 

[DonkeyOte]

In retrospect, if the last 1 appeared in B9 and you want "A1" result rather than "A9" then given the cell-esque header references:

=IF(COUNTIF($B$9:$W$32,1)=0,"n/a",SUBSTITUTE(CELL("address",INDIRECT("R"&SUBSTITUTE(MAX(IF($B$9:$W$32=1,ROW($B$9:$B$32)-ROW($B$9)+1+(COLUMN($B$9:$W$32)-COLUMN($B$9)+1)/1000)),".","C"),FALSE)),"$",""))
confirmed with CTRL + SHIFT + ENTER

The existence of your COUNTIFs in row 34/35 should not affect the above.

 

[Tosca White]

Hi, it is me again, your formula worked perfectly for Cell O34 with title:
"Period 1 stopped at Mark:" It returned N1 as it should have since the pink 1 ended in N1. But if I enter the formula in the Cell O35 for "Period 2 stopped at Mark:" I get a VALUE error. This must obviously be due to the fact that I replaced the wrong 2's in the formula you supplied. I am sort of understanding what you have done but am still learning. Kindly inform how I should rectify the formula to read the green 2's once entered on the schedule?

Thanks
Tosca

 

[DonkeyOte]

Did you "commit" the formula in O35:

=IF(COUNTIF($B$9:$W$32,2)=0,"n/a",SUBSTITUTE(CELL("address",INDIRECT("R"&SUBSTITUTE(MAX(IF($B$9:$W$32=2,ROW($B$9:$B$32)-ROW($B$9)+1+(COLUMN($B$9:$W$32)-COLUMN($B$9)+1)/1000)),".","C"),FALSE)),"$",""))

with CTRL + SHIFT + ENTER ?

If the Array has been set correctly the formula will appear encased within { } ... these can not be added manually.

 

[Tosca White]

Hi DonkeyOte

Thank very much you for your help, I have sucessfully commited the array to the second cell. It is just longwinded to retype and easy for an error to creep in as it seems you cannot copy the array formula accross to another cell.

Regards
Tosca