Define Range Based on Cell Value

MrDB4Excel

Active Member
Joined
Jan 29, 2004
Messages
341
Office Version
  1. 2013
Platform
  1. Windows
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
ABCDEFGHI
1F:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\1zTRUETOC145Rows
2ActionFilms\1z<<< Jump To This Sheet's Link In The Summary SheetRange Name This Sheet: Data01 = A5:F456Columns
3ActionFilms1<<< This Sheet's Name#N/A
4IndexFile NameContaining FolderConcatenated File NameHyperlinksDate Modified
51CIA- Exiled (Full Movie) - Action Thriller. ex-CIA operative.urlF:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\F:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\CIA- Exiled (Full Movie) - Action Thriller. ex-CIA operative.urlF:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\CIA- Exiled (Full Movie) - Action Thriller. ex-CIA operative.url06/23/2019 16:20
62Frederick Forsyth's Icon - 2005 Action Thriller - Patrick Swayze - PART 1.urlF:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\F:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\Frederick Forsyth's Icon - 2005 Action Thriller - Patrick Swayze - PART 1.urlF:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\Frederick Forsyth's Icon - 2005 Action Thriller - Patrick Swayze - PART 1.url06/23/2019 16:15
73Lost at War (Action War Movie, Adventure, Science Fiction, Full Length, English) free full movies.urlF:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\F:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\Lost at War (Action War Movie, Adventure, Science Fiction, Full Length, English) free full movies.urlF:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\Lost at War (Action War Movie, Adventure, Science Fiction, Full Length, English) free full movies.url06/23/2019 16:20
84Ready, Willing & Able (Action Movie, AWARD-WINNING, Drama) free movie on youtube.urlF:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\F:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\Ready, Willing & Able (Action Movie, AWARD-WINNING, Drama) free movie on youtube.urlF:\PrevDsktpFldrs\Films\uTubeDatabase\ActionFilms\Ready, Willing & Able (Action Movie, AWARD-WINNING, Drama) free movie on youtube.url06/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
RangeFormula
B1B1=RIGHT(C5,LEN(C5)-0)
C1C1=LEFT(C3,1)&"z"
E1E1=COUNTIF(Data01,A5)>0
B2B2=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)
C2C2=C1
D2D2=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")
B3B3=LEFT(B2,LEN(B2)-1)
C3C3=MID(CELL("filename",C3),FIND("]",CELL("filename",C3))+1,32)
H3H3=SUM(A5:INDEX(Data01,H5,H6))
A5:A10A5=VLOOKUP('1z'!$A5,'1z'!$A:$A,COLUMN('1z'!$A:$A)-COLUMN('1z'!$A:$A)+1,0)
B5:B10B5=VLOOKUP('1z'!$B5,'1z'!$B:$B,COLUMN('1z'!$B:$B)-COLUMN('1z'!$B:$B)+1,0)
C5:C10C5=VLOOKUP('1z'!$C5,'1z'!$C:$C,COLUMN('1z'!$C:$C)-COLUMN('1z'!$C:$C)+1,0)
D5:D10D5=CONCATENATE(C5,B5)
E5:E10E5=HYPERLINK(D5)
F5:F10F5=VLOOKUP('1z'!$D5,'1z'!$D:$D,COLUMN('1z'!$D:$D)-COLUMN('1z'!$D:$D)+1,0)
Named Ranges
NameRefers ToCells
'1'!Data01='1'!$A$5:$A$5:'1'!M38H3, E1
'1z'!ExternalData_1='1z'!$A$4:$D$8A5:A10
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A5:F45Expression=AND($A5<>"",$G$1=ROW())textNO


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

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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.
 
Upvote 0
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.
 
Upvote 0
... my brain ... is on vacation ...
I think you just summed up how my day is going perfectly :eek:

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.
 
Upvote 0
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
 
Upvote 0
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.
1597916513753.png

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.
1597916598741.png
 
Upvote 0
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.
VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

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