Xl2bb Named Ranges Showing on Wrong Sheet and missing Named Ranges

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?

Jeffrey Mahoney said:

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?

Click to expand...

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.

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?

@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.

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

Jeffrey Mahoney said:

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

Click to expand...

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.

Jeffrey Mahoney said:

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

Click to expand...

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,""))

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.

Jeffrey Mahoney said:

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

Click to expand...

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



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



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



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.

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?

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.