Xl2bb Named Ranges Showing on Wrong Sheet and missing Named Ranges

[Jeffrey Mahoney]

I posted this table in a thread and noticed some issues. In the named ranges section, HardwareTbl, HardwareType, and MountingStyle are named ranges on a different Sheet. There are two other named ranges that didn't get named called MountingCol and MountingFltrCol (Col D and Col G respectively) that are on Sheet1 and within the range I selected for the Mini Table.

Maybe it is supposed to show named ranges from other sheets as long as some of the cells reference those named ranges? Then I guess the Cells column are showing which formulas contain those named ranges?

Book45a 20240313.xlsm
	C	D	E	F	G	H
1	HardwareCol	MountingCol			MountingFltrCol
2	Cabinet Knob				Through-bolted	Surface-mounted
3	Closet Hook	Back-to-Back			Back-to-Back
4	Drawer Pull	Through-bolted			Through-bolted
5	Robe Hook	Surface-mounted			Surface-mounted
6
Sheet1

Cell Formulas
Range	Formula
G2:H2,G3:G5	G2	=TRANSPOSE(FILTER(MountingStyle,HardwareType=Sheet1!C2,""))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
HardwareCol	=Sheet1!$C$2:$C$74	G2
HardwareTbl	=Sheet2!$L$3:$M$7	G2:G5
HardwareType	=Sheet2!$L$3:$L$7	G2:G5
MountingStyle	=Sheet2!$M$3:$M$7	G2:G5

Cells with Data Validation
Cell	Allow	Criteria
C2:C22	List	=HardwareFilter#
D2:D26	List	=G2#

 

[Peter_SSs]

Maybe it is supposed to show named ranges from other sheets as long as some of the cells reference those named ranges? Then I guess the Cells column are showing which formulas contain those named ranges?

That is correct. The named ranges that were not listed were not relevant to the data/formulas posted so are not listed. If there was 100 other named ranges not relevant (or possibly relevant) to what was posted it would just clutter the mini sheet & the thread if they were all listed.

 

[Jeffrey Mahoney]

Peter

So that comment you quoted was about named ranges on other sheets. HardwareCol is the data belonging to Column C (Activesheet); It appeared on the list. MountingCol and MountingFltrCol also should have been listed as they were part of the selection; do you agree?

 

[smozgur]

@Jeffrey Mahoney: Peter's response is correct and clarifies the intended functionality of XL2BB. However, in rare instances, certain elements may be overlooked due to the unique structure of the data. Would you mind sharing this specific workbook with me? I'd like to analyze its structure, observe how XL2BB processes it, and provide an answer based on my findings. You can send it to contactus@mrexcel.com. Thank you.

 

[Peter_SSs]

My understanding of the logic is this (though happy to be contradicted by @smozgur if I am wrong)

HardwareCol is the data belonging to Column C (Activesheet); It appeared on the list.

The formulas in col G of Sheet1 refer to cells in column C that are within HardwareCol hence it is included since there is an intersection between cells in the formula and cells in the named range.

MountingCol and MountingFltrCol also should have been listed as they were part of the selection; do you agree?

Before commenting I would like to know exactly what those two named ranges are?


As a side note, it is a bad idea to use the sheet name of the sheet that the formula is on in a formula as it is not needed and can in fact lead to (non-obvious) errors in some circumstances.
I am referring to this in the formulas in col G of Sheet1

=TRANSPOSE(FILTER(MountingStyle,HardwareType=Sheet1!C2,""))

 

[Jeffrey Mahoney]

Peter,

With all intentional humor regarding using a sheet name on the same sheet, you should talk to Microsoft. When I reference ranges on other sheets building a function, it always puts the sheet reference in for the same sheet. I usually take the time to edit them out, because, you're right, it is easier to read. When I see a sheet name in a formula I automatically assume, NOT THIS SHEET!

Regarding the original issue. I put those named ranges in for the person I was trying to help so they could reference. Less of an issue after I thought about it.

 

[Peter_SSs]

I usually take the time to edit them out, because, you're right, it is easier to read.

I am not suggesting editing them out to make it easier to read, I was suggesting it because leaving them in can give (without obvious warning) incorrect results. To see what I mean, try this yourself in a new workbook.

Put this in Sheet1

Sample.xlsm
	A	B	C
1		Name	Sales
2		John	20
3		Mary	12
4		Peter	14
5		John	18
6		Mary	18
7		Peter	20
Sheet1

Set up Sheet2 like this.
Confirm that the sales figures per person are correct but also note that I have used the Sheet2 name in my formulas in Sheet2

Sample.xlsm
	A	B
1	Name	Sales
2	John	38
3	Mary	30
4	Peter	34
Sheet2

Cell Formulas
Range	Formula
B2	B2	=SUMIF(Sheet1!$B$2:$B$7,Sheet2!A2,Sheet1!$C$2:$C$7)

Now in Sheeet2, suppose we want the list to be in order of sales, ascending. We select A1:B4 and invoke the Sort dialog and sort by Sales, smallest to largest.
This is the result. No warning, no error message, just wrong results!!
Peter gets the bonus when it should be John.
John gets the wooden spoon when it should be Mary

Sample.xlsm
	A	B
1	Name	Sales
2	Mary	34
3	Peter	38
4	John	30
Sheet2

Cell Formulas
Range	Formula
B2:B3	B2	=SUMIF(Sheet1!$B$2:$B$7,Sheet2!A3,Sheet1!$C$2:$C$7)
B4	B4	=SUMIF(Sheet1!$B$2:$B$7,Sheet2!A2,Sheet1!$C$2:$C$7)

In a large data sample with lots of input, such wrong results may not be at all obvious & could easily flow through to subsequent calculations or decisions.

If you repeat the exercise without the Sheet2 name in the Sheet2 formulas, these wrong results do not occur.

 

[Jeffrey Mahoney]

I see what you're talking about. Hmmm. I'm really trying to wrap my head around the logic. It's as if the row is locked. Is that a feature or a bug?

 

[Peter_SSs]

I'd call it a bug, but it is definitely a reason to try to get in the habit of always removing the sheet name to be on the safe side.