Requesting help with dynamic named range that does not recognize full column of data

[shstrating]

Excel 2016 / Win7Pro SP1

I have several dynamic named ranges defined on a worksheet.

The formula I am using (found on OZGRID) is:

=OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1)​

My only modifications to the original formula were define the Sheet Name and to set my starting cell at Row7 instead of the example, which used Row1.​

I am using this formula for dynamic named ranges in Columns B, D, E, H, I, L, O, P with no problems.

The only change from column to column is the Column letter.​

However, when I try to use it in my Column M it will only return a range of 3 cells down.

This is true no matter which cell I use as my starting cell.​

Even if I start in a cell that's beyond my data range, it only returns a range of 3 cells down.
This is the non-working formula:​

=OFFSET(Product_Categories!$<sheet name="">M$7,0,0,MATCH("*",Product_Categories!$<sheet name="">M:$M,-1),1)</sheet></sheet>​

I believe the fact that it only ever returns a 3-cell range tells me it's not working at all in ColM, but for the life of me I cannot determine why.

All of my columns (B-P) are set to General for Number Format, and all of the columns contain text.

This is a small sample of the data from the worksheet: (Cols G & K are Hidden in the worksheet / Cols C, F, J, N are helper columns)

	A	B	C	D	E	F	H	I	J	L	M	N	O	P
KnollOffice	Accessories	AccessoriesParts	KnollOffice
KnollStudio	Seating	CommunicationBoards	KnollOffice
Systems	ComputerSupportAccessories	KnollOffice
UniversalStorage	ConsumerProducts	KnollOffice
UniversalTables	Lighting	KnollOffice

<tbody>
[TD="align: center"]6[/TD]
[TD="bgcolor: #5B9BD5"]CC1 Code[/TD]
[TD="bgcolor: #5B9BD5"]One Knoll 1 - Division[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #ED7D31"]CC2 Code[/TD]
[TD="bgcolor: #ED7D31"]One Knoll 2 - Product Category[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #A5A5A5"]One Knoll 3 - Product Sub-Category[/TD]
[TD="bgcolor: #A5A5A5"]CC3 Code[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFC000"]One Knoll 4 - Product Line[/TD]
[TD="bgcolor: #FFC000"]CC4 Code[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #4472C4"]One Knoll 5 - Product Type[/TD]
[TD="bgcolor: #4472C4"]CC5 Code[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #DDEBF7"]KNO[/TD]
[TD="bgcolor: #DDEBF7"]Knoll Office[/TD]

[TD="bgcolor: #FCE4D6"]ACC[/TD]
[TD="bgcolor: #FCE4D6"]Accessories[/TD]

[TD="bgcolor: #EDEDED"]Accessories Parts[/TD]
[TD="bgcolor: #EDEDED"]ACP[/TD]

[TD="bgcolor: #FFF2CC"]Anchor[/TD]
[TD="bgcolor: #FFF2CC"]ANC[/TD]

[TD="bgcolor: #D9E1F2"]Adjustable-Height Stools[/TD]
[TD="bgcolor: #D9E1F2"]AHS[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #DDEBF7"]KNS[/TD]
[TD="bgcolor: #DDEBF7"]KnollStudio[/TD]

[TD="bgcolor: #FCE4D6"]STG[/TD]
[TD="bgcolor: #FCE4D6"]Seating[/TD]

[TD="bgcolor: #EDEDED"]Communication Boards[/TD]
[TD="bgcolor: #EDEDED"]CMB[/TD]

[TD="bgcolor: #FFF2CC"]Antenna[/TD]
[TD="bgcolor: #FFF2CC"]ANT[/TD]

[TD="bgcolor: #D9E1F2"]Admin/Median[/TD]
[TD="bgcolor: #D9E1F2"]ADM[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE4D6"]SYS[/TD]
[TD="bgcolor: #FCE4D6"]Systems[/TD]

[TD="bgcolor: #FFFF00"]Computer Support Accessories[/TD]
[TD="bgcolor: #EDEDED"]CSA[/TD]

[TD="bgcolor: #FFF2CC"]Autostrada[/TD]
[TD="bgcolor: #FFF2CC"]AUT[/TD]

[TD="bgcolor: #D9E1F2"]Aperture and Enclosure Components[/TD]
[TD="bgcolor: #D9E1F2"]AEC[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE4D6"]UST[/TD]
[TD="bgcolor: #FCE4D6"]Universal Storage[/TD]

[TD="bgcolor: #EDEDED"]Consumer Products[/TD]
[TD="bgcolor: #EDEDED"]CPR[/TD]

[TD="bgcolor: #FFF2CC"]Calibre[/TD]
[TD="bgcolor: #FFF2CC"]CAL[/TD]

[TD="bgcolor: #D9E1F2"]Applied Walls[/TD]
[TD="bgcolor: #D9E1F2"]APW[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE4D6"]UTB[/TD]
[TD="bgcolor: #FCE4D6"]Universal Tables[/TD]

[TD="bgcolor: #EDEDED"]Lighting[/TD]
[TD="bgcolor: #EDEDED"]LGT[/TD]

[TD="bgcolor: #FFF2CC"]Chadwick[/TD]
[TD="bgcolor: #FFF2CC"]CHD[/TD]

[TD="bgcolor: #D9E1F2"]Bags[/TD]
[TD="bgcolor: #D9E1F2"]BAG[/TD]

</tbody>

Product_Categories
​

Any ideas as to what might be causing this formula to fail only when used in ColM?

Thanks,
Steve

 

[sunny102]

Hi Steve,

Can you share you workbook here, s that I can have a look at it and find out the issue?

Thanks and I'm able to respond in real time to you until it's solved.

 

[shstrating]

sunny102,

Thanks for the offer, but I've checked upstream and because there is proprietary, unreleased product data in the workbook they are not allowing me to distribute it anywhere for any reason.

 

[sunny102]

You can share with some dummy data or only data on columns where you have named ranges and also Column M.