Separate Numbers by Color
- Thread starter deciog
- Start date
As per the file, look like you can use Name range with GETCELL function to create a helper criteria in L3:L8 for the cell color number,
then,
try use the same technique to create a helper cell color number table (Y3:AH12) for the Input table (N3:W12).
After that,
using formula to extract number in referring to the helper table (Y3:AH12), helper criteria (L3:L8) and Input table (N3:W12).
Regards
Bosco
then,
try use the same technique to create a helper cell color number table (Y3:AH12) for the Input table (N3:W12).
After that,
using formula to extract number in referring to the helper table (Y3:AH12), helper criteria (L3:L8) and Input table (N3:W12).
Regards
Bosco
Last edited:
Hi Decio,
You could use this shorter non-array formula instead.
In N14, formula copied across and down :
=IFERROR(AGGREGATE(15,6,$N$3:$W$12/($Y$3:$AH$12=$L3),COLUMN(A1)),"")
Remark : all helper cells can be hidden by using format cell with white color font and white color background.
Regards
Bosco
Last edited:
Peter Bartholomew
Well-Known Member
The attached addresses a related problem of how does one colour a randomly generated table.
1. RANDARRAY is used to create the numbered table.
2. SUMIFS is used within a helper range to return the row number corresponding to each element of the array.
3. Conditional formatting and implicit intersection between sheets is used to colour the table.
1. RANDARRAY is used to create the numbered table.
2. SUMIFS is used within a helper range to return the row number corresponding to each element of the array.
3. Conditional formatting and implicit intersection between sheets is used to colour the table.
Peter Bartholomew
Well-Known Member
@deciog
I think this is closer to the problem, as originally specified, in that I have built the lists from the table rather than colouring the table to conform to the list. As I have generated the random distribution of colours using a table of indices, I have then used the helper table to create the lists rather than 'reading' the colours. I have used the LET function to organise the formula. I am not sure whether you have the function yet; otherwise it is a case of reverting to defined names or helper ranges to unpivot the tables.
I think this is closer to the problem, as originally specified, in that I have built the lists from the table rather than colouring the table to conform to the list. As I have generated the random distribution of colours using a table of indices, I have then used the helper table to create the lists rather than 'reading' the colours. I have used the LET function to organise the formula. I am not sure whether you have the function yet; otherwise it is a case of reverting to defined names or helper ranges to unpivot the tables.