Define Range Based on Cell Value

[MrDB4Excel]

I have a workbook containing 15 sheets that retrieve data from power query sheets in the same workbook. The attached XL2BB refers to sheet 1.
Sheet 1 includes a named range called Data01 which includes A5:F45.
Is it possible to set up a formula or multiple formulas without VBA to dynamically change the range of the named range?
For example, Data01 includes A5:F45. If a value is input into a cell, say 50, then the range automatically changes in Data01 to A5:F55.

uTubeDB.xlsm
	A	B	C	D	E	F	G	H	I
1		F:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\	1z		TRUE	TOC	1	45	Rows
2		ActionFilms\	1z	<<< Jump To This Sheet's Link In The Summary Sheet	Range Name This Sheet: Data01 = A5:F45			6	Columns
3		ActionFilms	1	<<< This Sheet's Name				#N/A
4	Index	File Name	Containing Folder	Concatenated File Name	Hyperlinks	Date Modified
5	1	CIA- Exiled (Full Movie) - Action Thriller. ex-CIA operative.url	F:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\	F:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\CIA- Exiled (Full Movie) - Action Thriller. ex-CIA operative.url	F:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\CIA- Exiled (Full Movie) - Action Thriller. ex-CIA operative.url	06/23/2019 16:20
6	2	Frederick Forsyth's Icon - 2005 Action Thriller - Patrick Swayze - PART 1.url	F:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\	F:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\Frederick Forsyth's Icon - 2005 Action Thriller - Patrick Swayze - PART 1.url	F:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\Frederick Forsyth's Icon - 2005 Action Thriller - Patrick Swayze - PART 1.url	06/23/2019 16:15
7	3	Lost at War (Action War Movie, Adventure, Science Fiction, Full Length, English) free full movies.url	F:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\	F:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\Lost at War (Action War Movie, Adventure, Science Fiction, Full Length, English) free full movies.url	F:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\Lost at War (Action War Movie, Adventure, Science Fiction, Full Length, English) free full movies.url	06/23/2019 16:20
8	4	Ready, Willing & Able (Action Movie, AWARD-WINNING, Drama) free movie on youtube.url	F:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\	F:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\Ready, Willing & Able (Action Movie, AWARD-WINNING, Drama) free movie on youtube.url	F:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\Ready, Willing & Able (Action Movie, AWARD-WINNING, Drama) free movie on youtube.url	06/23/2019 16:17
9	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
10	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
1

Cell Formulas
Range	Formula
B1	B1	=RIGHT(C5,LEN(C5)-0)
C1	C1	=LEFT(C3,1)&"z"
E1	E1	=COUNTIF(Data01,A5)>0
B2	B2	=MID(B1, FIND(CHAR(1),SUBSTITUTE(B1,"\",CHAR(1),4))+1, FIND(CHAR(1),SUBSTITUTE(B1,"\",CHAR(1),5)) - FIND(CHAR(1),SUBSTITUTE(B1,"\",CHAR(1),2))-1)
C2	C2	=C1
D2	D2	=HYPERLINK("#"&CELL("address",INDEX(Summary!$E$2:$E$33,MATCH($C$2,Summary!$E$2:$E$33,0))),"<<< Jump To This Sheet's Link In The Summary Sheet")
B3	B3	=LEFT(B2,LEN(B2)-1)
C3	C3	=MID(CELL("filename",C3),FIND("]",CELL("filename",C3))+1,32)
H3	H3	=SUM(A5:INDEX(Data01,H5,H6))
A5:A10	A5	=VLOOKUP('1z'!$A5,'1z'!$A:$A,COLUMN('1z'!$A:$A)-COLUMN('1z'!$A:$A)+1,0)
B5:B10	B5	=VLOOKUP('1z'!$B5,'1z'!$B:$B,COLUMN('1z'!$B:$B)-COLUMN('1z'!$B:$B)+1,0)
C5:C10	C5	=VLOOKUP('1z'!$C5,'1z'!$C:$C,COLUMN('1z'!$C:$C)-COLUMN('1z'!$C:$C)+1,0)
D5:D10	D5	=CONCATENATE(C5,B5)
E5:E10	E5	=HYPERLINK(D5)
F5:F10	F5	=VLOOKUP('1z'!$D5,'1z'!$D:$D,COLUMN('1z'!$D:$D)-COLUMN('1z'!$D:$D)+1,0)

Named Ranges
Name	Refers To	Cells
'1'!Data01	='1'!$A$5:$A$5:'1'!M38	H3, E1
'1z'!ExternalData_1	='1z'!$A$4:$D$8	A5:A10

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:F45	Expression	=AND($A5<>"",$G$1=ROW())	text	NO

This image shows the result of the row highlighting conditional formatting.

 

[jasonb75]

You would need to refer to the range using offset.

=OFFSET($A$5,0,0,50,6)

Any of the four numeric arguments can refer to a cell.

 

[MrDB4Excel]

You would need to refer to the range using offset.

=OFFSET($A$5,0,0,50,6)

Any of the four numeric arguments can refer to a cell.

Sorry, jasonb75, I must be blind in one eye, cannot see out the other, or the half of my brain that should interpret your answer is on vacation today.
I am lost, I do not understand. There must be more to this. Possibly I am missing something here to get this to work. All it does is return #VALUE!...
Which cells? Refer to the range how?
Did I ask the wrong question in my original post?
My version of Excel is 2013.
I am fairly familiar with Excel and formulas etcetera, but treat my question as if today I found this workbook and opened Excel for the first time ever.

 

[jasonb75]

... my brain ... is on vacation ...

I think you just summed up how my day is going perfectly

Not quite sure why you're getting #VALUE! from it, that would normally mean that you are trying to perform a mathematical calculation on something that is not a number.

Breaking the formula down, there are 5 arguments, the first 3 must be entered, the last 2 are optional.

=OFFSET($A$5,0,0,50,6)

$A$5 is the top left cell of the range that you want to assign a name to.
The next 2 parts are both 0 because we don't want the starting point to move. The first one would move the starting point up (positive numbers) or down (negative), the second would move it left or right.

The 3rd part is the number of rows in the range, so 50 would give you A5:A54 (I just noticed that you mentioned 50 rows in your post, but A5:A55 is 51 rows).
The last part, 6, is the number of columns in the range, so the above formula would give you a range of A5:F54 (50 rows, 6 columns).

Editing the formula slightly to give it a better definition, this is to go into the 'Refers to' box for a named range.

=OFFSET('1'!$A$5,0,0,Sheet2!$A$1,6)

Would give you the range of '1'!$A$5:$F$54 where Sheet2!A1 contains the number 50. You can then refer to that named range in the same way that you would refer to the normal range in formulas, conditional formatting, etc.

 

[MrDB4Excel]

Many thanks jasonb75,

I put this =OFFSET('1'!$A$5,0,0,'2'!$A$1,6) into the refers to and when I enter Data01 in the cell location indication box and hit enter it shows correctly.
I had to change the "Sheet2" to plain 2 because all my sheets are only numbers, as in 1, 2, 3...
My question now is why does the value entered in A1 on sheet 2 have to be on sheet 2? Can it be in A1 on sheet 1?
I just changed it to A1 on sheet 1 and it seems to work.
So now, the end run of this whole thing is the VBA. I can assume I would make the following change:
From:
If Not Intersect(Target, Range("a5:f45")) Is Nothing Then
To:
If Not Intersect(Target, Range("Data01")) Is Nothing Then

If this second line change is correct please let me know.
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("a5:f45")) Is Nothing Then
Range("g1").Value = Target.Row
End If
End Sub

 

[MrDB4Excel]

I just now did a test and set the value in A1 to be 3. As you saw in my initial XL2BB post there are 4 rows with values. When I changed to 3 in A1 it shows the range as seen here in this first image.

And in this image you can see when I select row 4 that the conditional formatting still works but it shouldn't based on the range.

 

[jasonb75]

My question now is why does the value entered in A1 on sheet 2 have to be on sheet 2? Can it be in A1 on sheet 1?

It can be anywhere, I just used sheet2 as an example so that it stood out a bit more.

Your assumed edit to the code is correct, although you could do the whole thing in vba without the named ranges. This could be enhanced to workbook level if it is the same for every sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A5").Resize(Range("A1"))) Is Nothing Then
    Range("g1").Value = Target.Row
End If
End Sub

Looking at your follow up post, conditional formatting doesn't work with dynamic ranges. I noticed that you had conditional formatting in your first example but I hadn't realised that this was what you wanted to make dynamic.

For that to work, you would need to delete the conditional formatting and re-apply it whenever A1 is changed.