Xl2bb not adding the named ranges

[felixstraube]

Hi, first time user of the forum and the Xl2bb add-in. I wanted to reply to a question asked by another user an paste my bb code. Thing is I used some named ranges to make it more readable, but the named ranges are not listed at the of the code.

I'll paste my bb code here so you can see what I mean. The named ranges like "CurrentMonth", "HireDate", "TerminateDate", "LoaEndDate", "LoaStartDate" and "DaysInMonth" are not listed.
Can you tell me what im doing wrong? I made sure the "Named Ranges" was checked before copying the code.
Thanks in advance

Libro1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
4	Hire date	Terminate date	LOA start date	LOA end date	Status	ene-24	feb-24	mar-24	abr-24	may-24	jun-24	jul-24	ago-24	sep-24	oct-24	nov-24	dic-24
5	2022-08-08		2023-09-15	2024-09-14	Active	0	0	0	0	0	0	0	0	0,53333333	1	1	1
6	2022-08-08	2024-04-15			Terminated	1	1	1	0,5	0	0	0	0	0	0	0	0
7	2022-08-08		2023-09-15	2024-09-14	LOA	0	0	0	0	0	0	0	0	0,53333333	1	1	1
Hoja1

Cell Formulas
Range	Formula
H5:S7	H5	=SUM(LET(days,SEQUENCE(31,,H$4,1),(MONTH(days)=CurrentMonth)*(days>=HireDate)*(days<=TerminateDate)*(NOT((days<=LoaEndDate)*(days>=LoaStartDate))))*1)/DaysInMonth

Cells with Data Validation
Cell	Allow	Criteria
G5:G7	List	=StatusList

 

[smozgur]

Welcome to the MrExcel Message Board!

I can't think any reason but checking the Named Ranges checkbox "before" clicking on the Create Mini Sheet button, unless it is something specific to the workbook or an unknown bug.

Can you share the workbook somewhere that I can download? Or I can start a conversation with you if you can't but send via email only.
Please let me know. Thanks.

 

[felixstraube]

Hi, thank you for your response.
I have uploaded the workbook here.
Thanks again.

 

[Peter_SSs]

@smozgur
You are probably already onto it but I think the issue is that the named ranges are not absolute ranges but relative ranges. For example, when dealing with row 5 HireDate is C5 but when dealing with row 6 HireDate is C6 etc.

So the XL2BB above shows the formula for H5 but indicates that it applies for H5:S7. Each of those 36 cells uses 6 (relative) named ranges where each of the named range combinations is different.
I'm not too sure how XL2BB could show that information.

 

[smozgur]

@smozgur
You are probably already onto it but I think the issue is that the named ranges are not absolute ranges but relative ranges. For example, when dealing with row 5 HireDate is C5 but when dealing with row 6 HireDate is C6 etc.

So the XL2BB above shows the formula for H5 but indicates that it applies for H5:S7. Each of those 36 cells uses 6 (relative) named ranges where each of the named range combinations is different.
I'm not too sure how XL2BB could show that information.

Aha! Thanks for the explanation, Peter! I have never created or used a relative named range. In fact, I didn't know about them until today!

Hi, thank you for your response.
I have uploaded the workbook here.
Thanks again.

@felixstraube: Thanks for the sample file. However, as Peter explained, XL2BB can't show that information.

 

[Peter_SSs]

@felixstraube
You would need to explain in detail to the OP just how to set up those named ranges, or probably simpler in this case not to use named ranges at all but revert to using the relevant cell addresses.

 

[felixstraube]

Thank you both for your replies.
I could replace the named ranges with variables in the LET function.
I learn that you could use relative named ranges like variable and include functions in them from some Microsoft examples and was oooh, wow, and used them since then for clarity. But that was way before i got Excel 365. Now this isn't needed anymore with the LET function. Will have to get used to 365.

Maybe XL2BB could show these named ranges for a specific cell, like the first one in which it is used, and specify how it is defined for that cell. Something like:

Named Ranges
Name	Type	Relative to	Refers To	Cells
CurrentMonth	Relative	H5	=MONTH(Hoja1!H$4)	H5:S7
Data	Absolute		=Hoja1!$M$15:$P$25	B1

 

[felixstraube]

I think maybe the relative named ranges could be shown similar to how formulae are shown. It is basically the same.