Dynamic Ranges using non-contiguous cells and dependent on a cell value

[Carlo Paoloni]

Hi,

I am trying to find a solution on that but i cannot. Does anybody is
able to help me? What i need is to define Dynamic Ranges of values
(column B) using non-contiguous cells values (column A).

Please find attached a relevant discussion on the topic dating back to
1999
Thanks
Carlo

-------------------------------

Da: Phil Bosinoff - vedi profilo
Data: Mer 24 Mar 1999 00:00
Email: "Phil Bosinoff" <[email protected]>
Gruppi: microsoft.public.excel.misc,
microsoft.public.excel.worksheet.functions
Non ancora classificatoValutazione:
mostra opzioni
Rispondi | Rispondi all'autore | Inoltra | Stampa | Messaggio singolo |
Mostra originale | Segnala illeciti | Trova messaggi di questo autore


Using XL97, how can I define a range who's included elements depend on
the
values of other cells? In particular, suppose I have:

A B
1 False Apple
2 False Pear
3 True Orange
4 False Plum
5 True Grape
6 True Blueberry


What name can I define that would refer to the reference
="$B$3,$B$5,$B$6"
for these values in the A column, but would automatically change to
reflect
alternate Boolean values in the A column. In other words, if the A
column
were changed so that only A1 and A3 were TRUE, then the defined name
would
refer to ="$B$1,$B$3".


The dynamically defined name must return a reference to the
non-contiguous
region, not the values themselves.


I have looked at INDEX, CHOOSE, and OFFSET and not been able to come up
with
any combination of functions which return a non-contiguous dynamically
defined reference. Perhaps I missed something.


Is there any way to do this?


-Phil


Rispondi Vota questo post: Text for clearing space


Da: jaf - vedi profilo
Data: Mer 24 Mar 1999 00:00
Email: "jaf" <[email protected]>
Gruppi: microsoft.public.excel.misc,
microsoft.public.excel.worksheet.functions
Non ancora classificatoValutazione:
mostra opzioni
Rispondi | Rispondi all'autore | Inoltra | Stampa | Messaggio singolo |
Mostra originale | Segnala illeciti | Trova messaggi di questo autore


Hi Phil,

Select just the cells you want while holding down the control key
and...
insert>names>define. fini.


John



Phil Bosinoff wrote in message ...


|Using XL97, how can I define a range who's included elements depend on
the
|values of other cells? In particular, suppose I have:
|
| A B
|1 False Apple
|2 False Pear
|3 True Orange
|4 False Plum
|5 True Grape
|6 True Blueberry
|
|What name can I define that would refer to the reference
="$B$3,$B$5,$B$6"
|for these values in the A column, but would automatically change to
reflect
|alternate Boolean values in the A column. In other words, if the A
column
|were changed so that only A1 and A3 were TRUE, then the defined name
would
|refer to ="$B$1,$B$3".
|
|The dynamically defined name must return a reference to the
non-contiguous
|region, not the values themselves.
|
|I have looked at INDEX, CHOOSE, and OFFSET and not been able to come
up
with
|any combination of functions which return a non-contiguous dynamically

|defined reference. Perhaps I missed something.
|
|Is there any way to do this?
|
|-Phil
|
|
|

Rispondi Vota questo post: Text for clearing space


Da: Phil Bosinoff - vedi profilo
Data: Mer 24 Mar 1999 00:00
Email: "Phil Bosinoff" <[email protected]>
Gruppi: microsoft.public.excel.misc,
microsoft.public.excel.worksheet.functions
Non ancora classificatoValutazione:
mostra opzioni
Rispondi | Rispondi all'autore | Inoltra | Stampa | Messaggio singolo |
Mostra originale | Segnala illeciti | Trova messaggi di questo autore


John,

You have missed my point; that would not be dynamic. I want the range
definition to adjust automatically, as a function of the values in the
A
range. I do not want to have to redefine it myself.


DynName=f(A1:A6,B1:B6), or, more generally:


DynName=f(BoolRange, TargetRangeofSameShape).


Phil



- Nascondi testo tra virgolette -
- Mostra testo tra virgolette -

jaf wrote in message ...

Hi Phil,

Select just the cells you want while holding down the control key and...
insert>names>define. fini.

John

Rispondi Vota questo post: Text for clearing space


Da: Stephen Bullen - vedi profilo
Data: Mer 24 Mar 1999 00:00
Email: Stephen Bullen <[email protected]>
Gruppi: microsoft.public.excel.misc,
microsoft.public.excel.worksheet.functions
Non ancora classificatoValutazione:
mostra opzioni
Rispondi | Rispondi all'autore | Inoltra | Stampa | Messaggio singolo |
Mostra originale | Segnala illeciti | Trova messaggi di questo autore


Hi Phil,

The dynamically defined name must return a reference to the non-contiguous
region, not the values themselves.

I'll answer this with a question - why?

Most things that can use such an input will also work with the values.
What
are you going to use this range for?


Regards


Stephen Bullen
Microsoft MVP - Excel
http://www.BMSLtd.co.uk


Rispondi Vota questo post: Text for clearing space


Da: Phil Bosinoff - vedi profilo
Data: Gio 25 Mar 1999 00:00
Email: "Phil Bosinoff" <[email protected]>
Gruppi: microsoft.public.excel.misc,
microsoft.public.excel.worksheet.functions
Non ancora classificatoValutazione:
mostra opzioni
Rispondi | Rispondi all'autore | Inoltra | Stampa | Messaggio singolo |
Mostra originale | Segnala illeciti | Trova messaggi di questo autore


Hi Stephen,

This is getting a little off the topic, but...


The idea was to have a table driven VBA macro, which had a list of
defined
range names on the subject sheet along with the desired attributes of
each
range, i.e. background colors, fonts, cell protection. The table
itself is
on a separate sheet. A VBA routine could then run down this list and
change
the attributes of each range according to entries in the table. It
just
seems like a clean way to customize the visual format of a sheet.
These
attributes can change as one uses the program, and this mechanism
provides a
simple way to set them back to their default. Unfortunately, the
desired
format of some of the cells is data dependent. So, I tried to set up a
data
dependent range definition.


Meanwhile I've added the VBA code to go thru the range and modify the
attributes, depending on the data. But the data dependent range name
would
be a far more elegant solution.


Really what I'm looking for is a Dfunction (database function) that
doesn't
perform any operation on the cells that meet the criteria (like DSUM or

DAVERAGE), but simply returns a reference to them. Sort of a
DREFERENCE.


Phil




- Nascondi testo tra virgolette -
- Mostra testo tra virgolette -

Stephen Bullen wrote in message ...

Hi Phil,

I'll answer this with a question - why?

Most things that can use such an input will also work with the values. What
are you going to use this range for?



Stephen Bullen
Microsoft MVP - Excel
http://www.BMSLtd.co.uk

Rispondi Vota questo post: Text for clearing space


Da: Stephen Bullen - vedi profilo
Data: Gio 25 Mar 1999 00:00
Email: Stephen Bullen <[email protected]>
Gruppi: microsoft.public.excel.misc,
microsoft.public.excel.worksheet.functions
Non ancora classificatoValutazione:
mostra opzioni
Rispondi | Rispondi all'autore | Inoltra | Stampa | Messaggio singolo |
Mostra originale | Segnala illeciti | Trova messaggi di questo autore


Hi Phil,

The idea was to have a table driven VBA macro, which had a list of defined
range names on the subject sheet along with the desired attributes of each
range, i.e. background colors, fonts, cell protection.

Thanks for the explanation. I guess what you're asking for is an
INDIRECT()
call which uses an array formula (an hence returns multiple range
references). Unfortunately, the indirect() function does not seem to
accept
arrays for its input, so I doubt if it can be done in a one-liner.

Your concept is sound and the VBA approach may be the best way to go.


Depending on your situation, you may also be able to use Styles to
change
ths visual appearance of the sheet.


Regards


Stephen Bullen
Microsoft MVP - Excel
http://www.BMSLtd.co.uk


Rispondi Vota questo post: Text for clearing space


Da: Phil Bosinoff - vedi profilo
Data: Gio 25 Mar 1999 00:00
Email: "Phil Bosinoff" <[email protected]>
Gruppi: microsoft.public.excel.misc,
microsoft.public.excel.worksheet.functions
Non ancora classificatoValutazione:
mostra opzioni
Rispondi | Rispondi all'autore | Inoltra | Stampa | Messaggio singolo |
Mostra originale | Segnala illeciti | Trova messaggi di questo autore


Stephen,

Yes, you understand what I'm looking for. INDEX, INDIRECT, and OFFSET
return references, but don't seem to be able to handle multiple ranges.

Depending on your situation, you may also be able to use Styles to change
ths visual appearance of the sheet.

I had completely forgotten about Styles! I guess it's easy to overlook
this
feature of Excel. Thanks for the suggestion. I'll look into Styles as
a
possible way to enhance my current approach.

Regards,
Phil

 

[Otto Moehrbach]

Carlo
The only solution I can see is to use VBA. Perhaps someone else can
come up with a formula solution.
Using VBA to come up with the range, the question becomes: How do you
want to use this range once VBA comes up with it? For instance, do you want
VBA to name the range (with a worksheet name) so that you can use that name
in some formula?
Another point that needs to be resolved before a VBA solution can be
developed is: Are the values in Column A derived from formulas in the
Column A cells or are those values entered manually?
The following macro will do what you want. This macro is written with the
assumptions that:
The range of the Boolean values is A1:A6.
The cells you want to refer to are in the range B1:B6.
The values in A1:A6 are entered manually (not by formulas).
The worksheet name you want to assign to the Column B range of
non-contiguous cells is "TheName".
IMPORTANT: The name "TheName" will be deleted by this macro if none of the
Column A values is TRUE. This will effect any formulas you have in the file
that refer to that name.
Note that this macro is a sheet macro and MUST be placed in the sheet module
of the sheet that has the values in question. To access that module,
right-click on the sheet tab, select View Code, and paste this macro into
that module. "X" out of the module to return to your sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
Dim TheRng As Range
Dim Temp As Range
Dim i As Range
If Not Intersect(Target, Range("A1:A6")) Is Nothing Then
Set TheRng = Nothing
For Each i In Range("A1:A6")
If i.Value = True Then
If TheRng Is Nothing Then
Set TheRng = i.Offset(, 1)
Else
Set TheRng = Union(TheRng, i.Offset(, 1))
End If
End If
Next i
If TheRng Is Nothing Then
On Error Resume Next
Set Temp = Range("TheName")
If Err.Number = 0 Then
ActiveWorkbook.Names("TheName").Delete
Else
Err.Clear
End If
On Error GoTo 0
Else
TheRng.Name = "TheName"
End If
End If
End Sub

 

[Arvi Laanemets]

Hi

For cases like this (and for a lot of cases slightly different) I use next
approach.

Let's have a sheet List0 with a table like your example with fruits (data
start from A1:B1). Add a column to left of it - so that your data start now
from B1:C1.

A1=IF(AND($C1<>"",$B1),COUNTIF($B$1:$B1,TRUE),"")
Copy A1 down for any number of rows you like.

Define a named range List0
=OFFSET(List0!$A$1,,,COUNTA(List0!$C:$C),3)

Create another sheet - List
A1=IF(ISERROR(VLOOKUP(ROW(),List0,3,0)),"",VLOOKUP(ROW(),List0,3,0))
Copy A1 down - again for any number of rows you like. You get a list of
fruits from List0 for which value in column B was true - and without any
gaps.

Define a named range List
=OFFSET(List!$A$1,,,COUNTIF(List!$A:$A,">"""),1)
You may now set the sheet List to be hidden or very hidden - no need for it
to be visible.
In other formulas, refer to named range List.


Arvi Laanemets