| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Ranges using non-contiguous cells and dependent on a cell value
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" 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" 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... insertnamesdefine. 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" 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... insertnamesdefine. 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 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" 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, 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: Stephen Bullen - vedi profilo Data: Gio 25 Mar 1999 00:00 Email: Stephen Bullen 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" 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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Ranges using non-contiguous cells and dependent on a cell value
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 "Carlo Paoloni" wrote in message ups.com... 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" 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" 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... insertnamesdefine. 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" 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... insertnamesdefine. 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 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" 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, 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: Stephen Bullen - vedi profilo Data: Gio 25 Mar 1999 00:00 Email: Stephen Bullen 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" 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Dynamic Ranges using non-contiguous cells and dependent on a cell value
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 "Carlo Paoloni" wrote in message ups.com... 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" 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" 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... insertnamesdefine. 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" 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... insertnamesdefine. 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 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" 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, 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: Stephen Bullen - vedi profilo Data: Gio 25 Mar 1999 00:00 Email: Stephen Bullen 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" 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Simple question - auto populate cells - dependent on other cell value. | Excel Discussion (Misc queries) | |||
| Cell locking dependent on Cell value | Excel Discussion (Misc queries) | |||
| How do I get a cell to flash depending on another cell's value? | Excel Worksheet Functions | |||
| Furmula Dependent Cell References | Excel Discussion (Misc queries) | |||
| Trace ALL dependent cells in any worksheet anywhere in Excel | Excel Worksheet Functions | |||
Linear Mode
