Dynamic Range Selection

jl2509

jl2509

Board Regular
Joined
Oct 30, 2015
Messages
198
Office Version
  1. 365
Platform
  1. Windows
Hi

I am trying to setup a dynamic range using offset in name manager which will select every 6th cell in a row starting at E11
i.e. E11, K11, Q11 etc would be selected but only where data is present

Any thoughts please.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Hi,

If the cells you want to select are the only ones that are filled in... or if they are the only ones with formulas, or only ones with constants, or numbers... then you could go to them most easily by goto special (control+g, alt+s, then pick 'constants' or 'formulas' and pick the data type).

If that doesn't achieve what you want then you will probably need to provide the following info to get a good answer:

Why would you want to do that? Why select cells, which is rarely a good idea? What is the point of this?
When you say "only where data is present" ... what do you mean? Do you mean that there will be data for example in column AE, and so you want every 5th cell that happens before AE? Or do you mean there may be no data in K thru CB, but there is data in E thru J and CC thru DD so you want to pick up only every 5th cell that is filled (there will be a gap)?
For the cells that are filled, do you know whether they will be text or numbers?

Tai
 
Last edited:
Upvote 0
Hi

I would like to use a dynamic range as a reference point for a chart.

I am not actually selecting the cells, they are only indicated by the "Dancing ant lines round them"

Data only exists in every 6th cell starting at E11 but I need the range to expand to the end of the row where data exists.
So... E11, K11, Q11 etc
This row can grow and shrink but I dont blank data in my chart by setting a fixed range.
Data is text values (Names of people)

Thanks
 
Upvote 0
Hi,

Thanks for the additional info. I don't know how to do this without VBA. Maybe someone does and will chime in... I tried google for dynamic non-contiguous named range and didn't find it right away - answers relate only to finding the bottom row when there are blanks, and then creating a contiguous range that includes blanks...

The VBA is easy; you could trigger it if you want by any change in row 11. You could add this code directly to that sheet (not in a standard module - right-click the actual sheet and add the code). Obviously the drawback is that macros have to be turned on. You could also put a button "update chart" and link it to a similar sub in a standard module, then the user will get some kind of error message if macros are turned off. Let me know if you want any help with an approach like that.

Code: 
Option Explicit




Private Sub Worksheet_Change(ByVal Target As Range)


    'only do something if a change impacts row 11 of this sheet
    If Intersect(Target, Me.Range("11:11")) Is Nothing Then Exit Sub
    
    'the named range will include ALL CONSTANTS (formulas are excluded) in row 11, without regard to positioning (eg every 6 columns)
    ActiveWorkbook.Names.Add Name:="myChartPeopleNames", RefersTo:=Me.Range("11:11").SpecialCells(xlCellTypeConstants, 23)


End Sub

Tai
 
Upvote 0
Hi I really do appreciate the help, but I do need a formula, not VBA.
Macros cannot be enabled on this worksheet. I should have mentioned that before wasting your time.

Thanks
 
Upvote 0
Hey no problem, I spend way more time in much more wasteful ways just about every day.

I am not sure if it is possible with a stand-alone formula. There are lots of people on here who are going to know before I am, but I will come back if I think of something.

There is a relatively obvious answer with formulas, but it does involve helper data sets. You could consider putting a hidden sheet to handle this in-between step:

Named range definition: =OFFSET(Sheet2!$A$1,,,LOOKUP(9.99E+307,Sheet2!$B:$B),1)

Sheet1 (has your data):

ABCDEFGHIJKLMNOPQ
SallySueSarah

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​


Sheet2 (helper formulas):
AB
Sally
Sue
Sarah

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]#N/A[/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A1[/TH]
[TD="align: left"]=INDEX(Sheet1!$11:$11,,(ROWS(A$1:A1)-1)*6+5)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=IF(A1=0,NA(),ROW(B1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Tai
 
Upvote 0
I understand, I will keep trying, but just for calrrity, I dont need the data displayed anywhere, it is already on Sheet 1.
The named range should pull the information from sheet1 into a chart, but I cannot get this offset range tied down.

I use named ranges in many charts and they work great, but not the offset one.


Thanks again
 
Upvote 0
OK, what i'm saying is the helper sheet facilitates getting the data into a named range - it is not for purpose of display, it is to populate the named range for the chart. With that hidden sheet facilitating, the dynamic solution works on formulas as in post 6. Without helper sheet or macro I'll be surprised if it's possible - but I have been surprised many times before!
 
Upvote 0
No worries, I understand. Ill keep hoping for a result as a formula

Many thanks again
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Group last name based on range in alphabet
Replies
2
Views
159
Peter_SSs
Peter_SSs
R
  • Question Question
Linked dynamic named ranges - unexpected results.
Replies
2
Views
157
Ramses505
R
D
  • Solved
Dynamic AutoFill
2
Replies
18
Views
205
MARK858
MARK858
O
  • Solved
Dynamic (row) selection in index formula based on date selection
2
Replies
10
Views
493
DanteAmor
DanteAmor
M
  • Question Question
Is it possible to dynamically add chart series' based on dynamic named range?
Replies
1
Views
665
StephenCrump
S

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

Share this page

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