Is it possible to make TYPE and CELL functions to work on a selected range?

[Rnkhch]

Hello,

Just wondering if it is possible to make the TYPE and CELL functions work on a selected range, such that the output would be a spill corresponding to each of the cells (and not the entire selection) within the selected range. Here is an example of what I mean exactly:

Blank power workbook1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1										44750	144750	44750
2		a	n	t	5	3	g	6	TRUE	#DIV/0!		t	?
3		7.9	#N/A	#SPILL!	FALSE	h		89	book	8-Jul	8	i	TRUE
4
5	individual cells dragged:	2	2	2	1	1	2	1	4	16	1	2	2
6		1	16	16	4	2	2	1	2	1	1	2	4
7
8	selected range:	64
9
10
11	individual cells dragged:	l	l	l	v	v	l	v	v	v	b	l	l
12		v	v	v	v	l	l	v	l	v	v	l	v
13
14	selected range:	l
15
16
Sheet1

Cell Formulas
Range	Formula
K1	K1	=TEXT(J3,10)
J1	J1	=VALUE(J3)
J2	J2	=1/0
C3	C3	=NA()
D3	D3	={1,2,3}
G3	G3	=IF(C1>2,1,"")
B5:M6	B5	=TYPE(B2)
B8	B8	=TYPE(B2:M3)
B11:M12	B11	=CELL("type",B2)
B14	B14	=CELL("type",B2:M3)

Here, B8 returns the type of the entire selection (which is of course its expected job), but would it be possible to make the formula in B8 to spill the same results as in B5:M6? And similarly, would it be possible to make the formula in B14 to spill the same results as in B11:M12?

Thanks for any input

 

[Fluff]

You cannot use Row or Column on an array, only on a range.

 

[smozgur]

Ok, this is getting more interesting.

It is working with the MAP function, and although it is not necessary, it works with LET function as well (and also as a Lambda function).

=MAP(B2:M3,LAMBDA(cll,CELL("type",cll)))
=LET(range,B2:M3,MAP(range,LAMBDA(cll,TYPE(cll))))
=LAMBDA(range, MAP(range,LAMBDA(cll,CELL("type",cll))))(B2:M3)

=MAP(B2:M3,LAMBDA(cll,TYPE(cll)))
=LET(range,B2:M3,MAP(range,LAMBDA(cll,CELL("type",cll))))
=LAMBDA(range, MAP(range,LAMBDA(cll,TYPE(cll))))(B2:M3)

 

[Rnkhch]

This is fabulous and indeed very interesting! Thank you! Very educational thread

I'm cooking some interesting LAMBDAs one of which will use this thread's solution. I'll post them sometime soon and would love to hear you guys' feedback.

 

[smozgur]

This is fabulous and indeed very interesting! Thank you! Very educational thread

I'm cooking some interesting LAMBDAs one of which will use this thread's solution. I'll post them sometime soon and would love to hear you guys' feedback.