IS it possible to denote dynamic named ranges based on a single cell location?

[excelos]

Hello!

I want to denote a dynamic cell range in Excel based on the particular cell that is populated. For example, let's say we have this sheet:

	1​	4​	7​		4​
	2​	5​	8​		5​
	3​	6​	9​		6​

I want a dynamic named range to be populated cells around B3, so the range should be B2:D4 and the cells in column F should be omitted because they do not belong in the populated cells around C3. Hope you get what I mean?

I want then to load this named range into Power Query or Python no matter how wide/long it will be as we edit the Excel file.

Moreover, I would like to specify B3 by its value, i.e. the cell in the worksheet that has the value 2.

Any idea?

Thanks! PS: No VBA or volatile formulas please!

 

[KRice]

If you're considering moving the data into Power Query or Python, then you might be better served performing this trimming operation in those applications. There is likely a better formulaic approach than what I've proposed below...perhaps something that utilizes a LAMBDA function to scan up and down the targeted cell's column and left and right across the row to determine the extents of the subarray that should be extracted. I played around with that idea, but continued to bump against the problem of defining the range to operate on. So I reluctantly broke your rule and devised a method using the volatile INDIRECT (I suppose a similar idea could be undertaken using OFFSET). In this approach, the user specifies the address of a target cell located somewhere in a field of values (input in cell E10). Then four formulas determine the left column, right column, upper row, and lower row of the subarray to which the target cell belongs, based on when a blank if first encountered when moving away from the target cell in all four directions. A cursory error check is used to confirm that the target cell is not empty...in which case it does not belong to any subarray. Another error check examines whether the target cell belongs to a subarray bounded by the top or left of the worksheet, in which case a blank will not be encountered when scanning up or left (depending on the case)...so in those cases, the extents are set to row 1 or column 1 (whichever is applicable). A similar error check could be invoked for the bottom and right of the worksheet, but I did not incorporate that idea because I'm guessing it is highly unlikely that it would ever be needed (if you do need it, you'll find that the XMATCH will return an #N/A error which can be trapped with IFNA). The greatest extents of the search are established by the bigrow and bigcol variables, currently set to the maximum number of rows and columns Excel can handle. You can adjust these to more practical sizes based on your data set to improve performance. The resultant subarray spills from cell E11. (ignore the SEQUENCE function in A2...that was used as a quick column number reference for debugging)

MrExcel_20231104.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	6	7	9
2	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
3
4	8	6	3		3	9	4		11	3	8	5	6	8
5	9	3	4		5	8	0		7	5	9	7	6	0
6	0	5	2		8	8	6		9	7	6	4	5	6
7
8	8	4	3		10	6	7		23	13	43	54	46	42
9
10				Target	L5
11				Results	11	3	8	5	6	8
12					7	5	9	7	6	0
13					9	7	6	4	5	6
14
Sheet1

Cell Formulas
Range	Formula
A2:T2	A2	=SEQUENCE(,20)
E11:J13	E11	=LET(tgt,INDIRECT(E10),rtgt,ROW(tgt),ctgt,COLUMN(tgt),bigrow,1048576,bigcol,16384, lc,IFNA(XMATCH(FALSE,INDIRECT(ADDRESS(rtgt,1)&":"&ADDRESS(rtgt,ctgt))<>"",0,-1),0)+1, rc,XMATCH(FALSE,INDIRECT(ADDRESS(rtgt,ctgt)&":"&ADDRESS(rtgt,bigcol))<>"",0,1)-2+ctgt, ur,IFNA(XMATCH(FALSE,INDIRECT(ADDRESS(1,ctgt)&":"&ADDRESS(rtgt,ctgt))<>"",0,-1),0)+1, lr,XMATCH(FALSE,INDIRECT(ADDRESS(rtgt,ctgt)&":"&ADDRESS(bigrow,ctgt))<>"",0,1)-2+rtgt, res,INDIRECT(ADDRESS(ur,lc)&":"&ADDRESS(lr,rc)),IF(tgt="","blank target",res))
Dynamic array formulas.