Named Range Area Number

[Jeffrey Mahoney]

Is it possible to get the Area Number from the intersection of another range? In the formula below I'm trying to return the top left cell of one of the areas named in InstructionAreas. The top left cell is the name of of each section. I want that to show in the header of the report. There are 8 sections, so the formula is a test of two of the sections.

If I can get the area number from the intersection then I don't have to test all areas.

I'm using Cell("Row") to get the row where the cursor is

=IF(ISERR(INDEX(InstructionAreas,,,4) INDEX($A$1:$BB$10000,CELL("row"),))=FALSE,INDEX(InstructionAreas,1,1,4),IF(ISERR(INDEX(InstructionAreas,,,3) INDEX($A$1:$BB$10000,CELL("row"),))=FALSE,INDEX(InstructionAreas,1,1,3),"Not"))

 

[Scott Huish]

Not sure what you mean exactly. It's much easier to get the top left cell of a named range than that. Can you provide some sample data, the results you expect and why?

 

[Jeffrey Mahoney]

Strangely, the XL2BB widened all the columns, they're supposed to 2.5 pixels wide.

Anyway, in this example I want to cursor through the instructions and have Cell A2 show the name of the current section. I write instructions for many workbooks and some of the sections are pages long. I want the user to know which section they are reading. I colored the named range "InstructionAreas" hot pink and put borders around it so you could see the range.

In my formula example in the previous post, I'm returning the top left cell in each of the sections. In the picture below, imagine my cursor in row 9. The intersection of row 9 and InstructionAreas is cell A9. Is there a formula trick to get the Area number of the range InstructionAreas at cell A9?

Daily Manager_2_Test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	Instructions for the Daily Manager
2	CHECKLIST SHEET
3	CHECKLIST SHEET
4
5		R	This helps track daily tasks by providing simple instructions, folder and file locations, and emails that need to be sent
6		R	The blue column headers indicate a field you can edit
7		R	The status column is used to indicate the task is completed or not
8			w	Double click this column to toggle status
9		R	The period should be changed after you copy the last month to a new month to start over
10		R	WD is based on weekdays and non-holiday days
11		R	Task, Title, are changeable by you
12		R	The Notes column may have formulas to help with dates and other variables that need to changed
13		R	The Hyperlink column allows you to open folders or files with the provided path and filename
14			w	No other text should be put in these cells other than paths for folders and path/filename combos
15			w	Double click the cells in this column to open the source
16		R	The Email column is to add an email into outlook based on the name of the email instructions in each cell
17			w	The name in the cell coincides with a set of instructions on the Email Sheet
18			w	Double click a cell in the Email column to create a new email
19			w	Right click a cell in the Email column to edit the email instructions and choose yes
20			w	It's suggested that a link to the name of the email instructions be put in this cell so that if the name is edited, the link won't break
21				°	Make sure to anchor the reference with $
22			w	The Crit2 column helps the Workday Calendar
23			w	Follow the instructions below under EMAILS SHEET to learn how to edit the email instructions
24
25
26	WORKDAY CALENDAR
27
28		R	This shows all the daily tasks for a month
29		R	If there items not finished in the past, the day turns red
30		R	Items get removed from the calendar as they get checked off on the Checklist
31		R	Use Ctrl-Up, Ctrl-Down in column B to navigate by month
32
33
34	MANUAL INVOICE TRACKER
35
36		R	Use this to reminder yourself of tasks that need attention, especially accruals and manual invoices
37		R	Make sure to populate each column to make it easy on what the outcome should be
38
39
40	EMAILS SHEET
41
42		R	This sheet is used to provide all the details for a certain email that gets sent monthly
43		R	Formulas can be used to specify date ranges and other changing parameters
44		R	The [CSPR] found at the end of subject lines is used by a Rule in Outlook to move them to the Central Services "CSPR Inbox" folder
45		R	You can input [Emailaddress] as FROM for those emails that are Central Services oriented
46			w	Make sure to setup Outlook to show the From field in your outgoing emails
47		R	Add [Email Address].com to the BCC field so as to not get multiple emails back
48		R	There are 4 fields that help populate the Body of the email, Body A, Body 1, Body 2, and Date Formula 1
49			w	Because formula cells can only contain 256 characters, Body 1 and Body 2 can be used to extend the text in the email
50			w	Use formulas in Body 1 and Body 2, then just concatenate those two fields into Body A
51			w	Use the Date Formula field for more complex data calculations and include that in Body A
52		R	Standard windows formatting cannot be used in emails, like bold, underline, and new lines
53			w	Use examples from other email configurations to see how to use HTML formatting
54			w	There are simple instructions on the Email sheet
55			w	You can put hard returns in the text to show what it looks like, but <br> must be used for Outlook to recognize a new line
56			w	Some codes must surround the text, some codes do not
57		R	Attachments is the name of the file. Use formulas to change dates at the end of reoccuring files
58		R	Path: add the server location or local path of the attachment
59
60
61	WD MAP
62
63		R	This is the Workday calculator for determining non-holiday weekdays
64			w	This provides information to the Checklist and Calendar
65			w	Cell E3 is where you set the first date in the series
66			w	It best to cover from January 1st of the last year onward
67
68	HOLIDAYS
69
70		R	This calculates the holidays based on the rules provided
71			w	The only thing that needs changing is the year at the top
72			w	There are notes to explain how the formulas work
73
74	SETUP
75
76		R	There are some tables for validation and the Current Year
77			w	The Current Year is used for formulas on the Email sheet
78
Instructions

Cell Formulas
Range	Formula
A2	A2	=section()

Lambda Functions
Name	Formula
GetSectionName	=LAMBDA(x,IF(ISERR(INDEX(Instructions!InstructionAreas,,,x) INDEX(Instructions!$A$1:$BB$10000,CELL("row"),))=TRUE,GetSectionName(x+1),INDEX(Instructions!InstructionAreas,1,1,x)))

 

[Jeffrey Mahoney]

I haven't found a solution to get the area number from the intersection of two ranges, so , I created a Lambda function. It still has to compare each area of the Sections Range with the Current row, but it is faster.

Provide the Maximum Area and the starting Area
=LAMBDA(xMax,x,IF(x>xMax,"All",IF(ISERR(INDEX(Instructions!InstructionAreas,,,x) INDEX(Instructions!$A$1:$BB$10000,CELL("row"),))=TRUE,GetSectionName(xMax,x+1),INDEX(Instructions!InstructionAreas,1,1,x))))

Use it in a cell this way
=GetSectionName(8,1)