Xl2bb Named Ranges Showing on Wrong Sheet and missing Named Ranges

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
2,856
Office Version
  1. 365
Platform
  1. Windows
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?


1710448212753.png


Book45a 20240313.xlsm
CDEFGH
1HardwareColMountingColMountingFltrCol
2Cabinet KnobThrough-boltedSurface-mounted
3Closet HookBack-to-BackBack-to-Back
4Drawer PullThrough-boltedThrough-bolted
5Robe HookSurface-mountedSurface-mounted
6
Sheet1
Cell Formulas
RangeFormula
G2:H2,G3:G5G2=TRANSPOSE(FILTER(MountingStyle,HardwareType=Sheet1!C2,""))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
HardwareCol=Sheet1!$C$2:$C$74G2
HardwareTbl=Sheet2!$L$3:$M$7G2:G5
HardwareType=Sheet2!$L$3:$L$7G2:G5
MountingStyle=Sheet2!$M$3:$M$7G2:G5
Cells with Data Validation
CellAllowCriteria
C2:C22List=HardwareFilter#
D2:D26List=G2#
 
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.
 
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)

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,""))
 
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.
 
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
ABC
1NameSales
2John20
3Mary12
4Peter14
5John18
6Mary18
7Peter20
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
AB
1NameSales
2John38
3Mary30
4Peter34
Sheet2
Cell Formulas
RangeFormula
B2B2=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
AB
1NameSales
2Mary34
3Peter38
4John30
Sheet2
Cell Formulas
RangeFormula
B2:B3B2=SUMIF(Sheet1!$B$2:$B$7,Sheet2!A3,Sheet1!$C$2:$C$7)
B4B4=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.
 
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.
 

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top