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
6	CC1 Code	One Knoll 1 - Division		CC2 Code	One Knoll 2 - Product Category		One Knoll 3 - Product Sub-Category	CC3 Code		One Knoll 4 - Product Line	CC4 Code		One Knoll 5 - Product Type	CC5 Code
7	KNO	Knoll Office	KnollOffice	ACC	Accessories	Accessories	Accessories Parts	ACP	AccessoriesParts	Anchor	ANC	KnollOffice	Adjustable-Height Stools	AHS
8	KNS	KnollStudio	KnollStudio	STG	Seating	Seating	Communication Boards	CMB	CommunicationBoards	Antenna	ANT	KnollOffice	Admin/Median	ADM
9				SYS	Systems	Systems	Computer Support Accessories	CSA	ComputerSupportAccessories	Autostrada	AUT	KnollOffice	Aperture and Enclosure Components	AEC
10				UST	Universal Storage	UniversalStorage	Consumer Products	CPR	ConsumerProducts	Calibre	CAL	KnollOffice	Applied Walls	APW
11				UTB	Universal Tables	UniversalTables	Lighting	LGT	Lighting	Chadwick	CHD	KnollOffice	Bags	BAG

<tbody>
</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.