Dynamic Range Selection

[jl2509]

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.

 

[taigovinda]

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

 

[jl2509]

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

 

[taigovinda]

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.

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

 

[jl2509]

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

 

[taigovinda]

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):

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3
4
5
6
7
8
9
10
11					Sally						Sue						Sarah

<tbody>
</tbody>

Sheet1​

Sheet2 (helper formulas):

	A	B
1	Sally	1
2	Sue	2
3	Sarah	3
4	0	#N/A
5	0	#N/A
6	0	#N/A
7	0	#N/A
8	0	#N/A
9	0	#N/A

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula A1 =INDEX(Sheet1!$11:$11,,(ROWS(A$1:A1)-1)*6+5) B1 =IF(A1=0,NA(),ROW(B1)) <tbody> </tbody>

<tbody>
</tbody>

Tai

 

[jl2509]

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

 

[taigovinda]

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!

 

[jl2509]

No worries, I understand. Ill keep hoping for a result as a formula

Many thanks again