If(countif) problem

[Silvabod]

Range N5:S15 (6 wide, 11 deep) is named range "drawn", and is either empty,
or random whole numbers between 1 and 49, which may include duplicated
numbers.
Have a 7x7 matrix (49 cells) - to display EITHER blanks OR the numbers 1 to
49, IN ORDER in rows, IF the number appears in "drawn" range.

So - if the number 7 appears once, or more than once(immaterial) then "7"
should appear in the 7th cell, top row

Am bogged down with the IF(COUNTIF) which I thought was the right route.
Help? please? Silvabod

 

[Herbert Seidenberg]

Put headers (1 thru 7) at the left and top margin of your 7x7 array
and name them r_a and c_a respectively.
Insert > Name > Define
Names in Workbook array_7x7
Refers to =(r_a-1)*MAX(c_a)+c_a
Fill your 7x7 array with
=IF((SUMPRODUCT(--(drawn=INDEX(array_7x7,r_a,c_a)))>=1),
INDEX(array_7x7,r_a,c_a),"")

 

[Don Guillett]

doesn't
=if(countif(myrange,7),7,"")
work?

 

[Silvabod]

Herbert, Don - thanks for your input.
Have just solved it, by a different and perhaps simpler route.
Keyed numbers 1 - 49 in order in the 7 x 7 array, with font colour WHITE (=
invisible)
Selected the 7 x 7 array, then Format/Conditional format,
formula is =COUNTIF(drawn,N49)>0 (which automatically formatted array
N49:T55 referencing each cell correctly)
Then formatted the Font to black (almost got this wrong - accidentally left
it as "automatic" then wondered why it didn't work! - in this instance,
automatic was the pre-defined White!)

"drawn" is the named range of random numbers
N49 is the top rightmost cell of the 7x7 array.

Amazing how many different solutions there can be, to one specific scenario!
Thanks again
Silvabod.