Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default Dynamic Range Name that's driving me nuts

I've created a Dynamic Range Name that uses the OFFSET() function with some embedded MATCH(), VLOOKUP(), & SUMIF() functions. That Dynamic Range is being used as a Validation list.

Basically, I'm trying to drive a Validation List that changes, depending on what value a user has selected into one of two adjacent cells. Those "driving" selections are based on static Validation Lists.

The user is only supposed to select into a cell within one of those two adjacent columns, but in the event that they select something in both, one of the columns prevails.

Depending on what's been selected within those two Validated cells (ideally only one user selection), my target Dynamic Range Validation List is supposed to return a contiguous subset of one large range of data.

So, for my two "driving" pick lists, one of them is a list of values & the other includes those values with a suffix, where the value & the suffix are delimited by a period (".").

My Dynamic Range checks for the presence of data in a cell that captures just the value. It it's there, then use that as the basis for the Dynamic Range Validation List.

If the cell that captures just the value ISBLANK(), then I fall back to the one that includes the suffix & apply a LEFT() function to lop off the suffix & the "." & then use that value to determine the basis for the Dynamic Range Validation List.

When I just specify the value, my Dynamic Range Validation List works beautifully.

However, when I try to use the column that includes the suffixes, my Dynamic Range contains -0- rows.

To test whether my nested MATCH() & SUMIF() formulas don't have an error, I've isolated those embedded formulas into their own cell to see what values they generate under the different driving list selections.

Those embedded formulas return the exact same values using either selection method. I can't for the life of me figure out why the Dynamic Range List works under one scenario but not the other.

Below, I've broken the OFFSET() formula into its separate components, separated by a carriage return. The MATCH() & SUMIF() formulas are pretty lengthy.

DynamicCurveList range formula
=OFFSET('Deal XRefs & validation'!$BP$1
,MATCH(VLOOKUP(IF(NOT(ISBLANK(OFFSET(Pricing!J2,0, COLUMN(Pricing!$H2)-COLUMN(Pricing!J2)))), OFFSET(Pricing!J2,0,COLUMN(Pricing!$H2)-COLUMN(Pricing!J2)),LEFT(OFFSET(Pricing!J2,0,COLUM N(Pricing!$G2)-COLUMN(Pricing!J2)),FIND(".",OFFSET(Pricing!J2,0,C OLUMN(Pricing!$G2)-COLUMN(Pricing!J2)))-1)),Provisions,3,FALSE),Base_Oils_Price_Curve_Grou ping,0)
,0
,SUMIF(Price_Curve_Grouping,"="&VLOOKUP(IF(NOT(ISB LANK(OFFSET(Pricing!J2,0,COLUMN(Pricing!$H2)-COLUMN(Pricing!J2)))),OFFSET(Pricing!J2,0,COLUMN(P ricing!$H2)-COLUMN(Pricing!J2)),LEFT(OFFSET(Pricing!J2,0,COLUM N(Pricing!$G2)-COLUMN(Pricing!J2)),FIND(".",OFFSET(Pricing!J2,0,C OLUMN(Pricing!$G2)-COLUMN(Pricing!J2)))-1)),Provisions,3,FALSE),Price_Curves_Counter)
,1)

As an alternative, the whole spreadsheet is 47kb, so I could email it if someone would be kind enough to take a look.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default Dynamic Range Name that's driving me nuts

Have you considered using 'dependant' DV lists, where user selects 1st
column and this determines what appears in the 2nd column's DV, ..and
so on?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default Dynamic Range Name that's driving me nuts

Have you considered using 'dependant' DV lists, where user selects
1st column and this determines what appears in the 2nd column's DV,
..and so on?


For example...

http://www.contextures.com/xlDataVal02.html

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default Dynamic Range Name that's driving me nuts

On Friday, September 9, 2016 at 12:13:44 PM UTC-5, GS wrote:
Have you considered using 'dependant' DV lists, where user selects
1st column and this determines what appears in the 2nd column's DV,
..and so on?


For example...

http://www.contextures.com/xlDataVal02.html

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus


That's almost what I'm trying to do, with a slight variation.

All of those examples require separate lists to drive the dependent Validation List. I don't need separate lists, I need the final picklist to be sourced from the same data. Even the lower examples where they differentiate between yellow & red fruit require separate lists.

Imagine, within the example on the page you forwarded, that the user can either pick (in column B, as shown) "Fruit", OR they can leave column B blank & instead pick (within column A) "Fruit.Grocer" or "Fruit.FarmersMarket".

The Dynamic pick list in Column C needs to be the sourced from the same data range, regardless of how they select it.

i.e. whether the user picks "Fruit" in column B or they pick "Fruit.Grocer" in column A, Column D needs to show "Apple, Banana, Lemon, Peach"

In my case, as long as the user picks within column B, everything is fine. However, if they pick from column A, even after I've isolated the portion of the column A selection that just says "Fruit", my dynamic list doesn't return any rows.

The puzzling thing is that when I break out the formulas that calculate the OFFSET parameter, both methods result in the same values. That leads me to conclude that I don't have errors in those formulas and that it's got to either be a limitation in Excel or a configuration somewhere that I've missed.

I even checked to see whether Range Names would accept the Array format (Ctrl+Shft+Enter). BTW, they don't accept those....
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default Dynamic Range Name that's driving me nuts

Those dependant DV lists have proven themselves over many years to work
'flawlessly' when properly configured. DV doesn't have the ability to
use filters, thus why your breakout formulas work. Rethink how your
project works and go with proven methods!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default Dynamic Range Name that's driving me nuts

On Friday, September 9, 2016 at 4:11:05 PM UTC-5, GS wrote:
Those dependant DV lists have proven themselves over many years to work
'flawlessly' when properly configured. DV doesn't have the ability to
use filters, thus why your breakout formulas work. Rethink how your
project works and go with proven methods!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus


But I'm not really filtering anything, I'm adjusting the starting row shift & the depth of a single column OFFSET() range. The formulas behind the components of the OFFSET() function return the same integer values, regardless of how the selections are made, so why doesn't the Dynamic Range respond the same way?

I'm just trying to determine whether I've hit a limit of Excel functionality or I've got an error in my formulas/configuration.

My model depends upon asking other people to provide the data in that list (I know what it looks like & how to get it, but I don't have access to extract it myself) and it's likely the data will need to be refreshed a few times before it's finalized. I'd much rather give them a single select statement that they can save in a single results file than a dozen select statements that they have to save into a dozen files.

I understand that code is code & sometimes the arrangements of the 1's & 0's result in something that falls outside of design. Maybe that's where I'm at & I need to request an enhancement (that may or may not ever be incorporated into the product).

However, I don't advocate letting "the way we've always done things" stand in the way of "the way we ought to do things".

If we limit our thinking to "=A1+A2+A3" because it's a tried & true method, we'll never get:
=SUM(A1:A3)
=SUMIF(A1:A3, "0")
and {=SUM((A1:A30)*(A1:A3))}

Again, at present, I'm just trying to assess whether it's a software limit, or a logic/configuration error.

If it's a software limit, then it sounds like I'll have to break the data into separate lists, exactly as you've described. If I've simply not configured something correctly, a single list is more considerate to the folks I have to rely on.
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default Dynamic Range Name that's driving me nuts

On Friday, September 9, 2016 at 4:11:05 PM UTC-5, GS wrote:
Those dependant DV lists have proven themselves over many years to
work 'flawlessly' when properly configured. DV doesn't have the
ability to use filters, thus why your breakout formulas work.
Rethink how your project works and go with proven methods!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus


But I'm not really filtering anything,

Using a conditional formula in the DV ListRange field forces DV to
filter the list via evaluating the formula. This is not supported!

I'm adjusting the starting row
shift & the depth of a single column OFFSET() range. The formulas
behind the components of the OFFSET() function return the same
integer values, regardless of how the selections are made, so why
doesn't the Dynamic Range respond the same way?

I'm just trying to determine whether I've hit a limit of Excel
functionality or I've got an error in my formulas/configuration.


IMO, you have an error in your approach based on how DV works. You're
asking DV to make choices; -it doesn't matter whether it's a
'limitation' or not because DV works how it works. Our job is to
understand how it works and use it accordingly to fit our needs.

My model depends upon asking other people to provide the data in that
list (I know what it looks like & how to get it, but I don't have
access to extract it myself) and it's likely the data will need to be
refreshed a few times before it's finalized. I'd much rather give
them a single select statement that they can save in a single results
file than a dozen select statements that they have to save into a
dozen files.


Not sure why you think this! No reason everything can't be saved in 1
file.

I understand that code is code & sometimes the arrangements of the
1's & 0's result in something that falls outside of design. Maybe
that's where I'm at & I need to request an enhancement (that may or
may not ever be incorporated into the product).

However, I don't advocate letting "the way we've always done things"
stand in the way of "the way we ought to do things".

If we limit our thinking to "=A1+A2+A3" because it's a tried & true
method, we'll never get: =SUM(A1:A3)
=SUMIF(A1:A3, "0")
and {=SUM((A1:A30)*(A1:A3))}


I agree! In this case we can't change how Excel is designed to work;
-we can change how we work with it, though!

Again, at present, I'm just trying to assess whether it's a software
limit, or a logic/configuration error.

If it's a software limit, then it sounds like I'll have to break the
data into separate lists, exactly as you've described. If I've
simply not configured something correctly, a single list is more
considerate to the folks I have to rely on.


You could use VBA to evaluate the input/selection cell and populate the
DV with the appropriate list.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default Dynamic Range Name that's driving me nuts

On Sunday, September 11, 2016 at 3:29:19 PM UTC-5, GS wrote:
On Friday, September 9, 2016 at 4:11:05 PM UTC-5, GS wrote:
Those dependant DV lists have proven themselves over many years to
work 'flawlessly' when properly configured. DV doesn't have the
ability to use filters, thus why your breakout formulas work.
Rethink how your project works and go with proven methods!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus


But I'm not really filtering anything,

Using a conditional formula in the DV ListRange field forces DV to
filter the list via evaluating the formula. This is not supported!

I'm adjusting the starting row
shift & the depth of a single column OFFSET() range. The formulas
behind the components of the OFFSET() function return the same
integer values, regardless of how the selections are made, so why
doesn't the Dynamic Range respond the same way?

I'm just trying to determine whether I've hit a limit of Excel
functionality or I've got an error in my formulas/configuration.


IMO, you have an error in your approach based on how DV works. You're
asking DV to make choices; -it doesn't matter whether it's a
'limitation' or not because DV works how it works. Our job is to
understand how it works and use it accordingly to fit our needs.

My model depends upon asking other people to provide the data in that
list (I know what it looks like & how to get it, but I don't have
access to extract it myself) and it's likely the data will need to be
refreshed a few times before it's finalized. I'd much rather give
them a single select statement that they can save in a single results
file than a dozen select statements that they have to save into a
dozen files.


Not sure why you think this! No reason everything can't be saved in 1
file.

I understand that code is code & sometimes the arrangements of the
1's & 0's result in something that falls outside of design. Maybe
that's where I'm at & I need to request an enhancement (that may or
may not ever be incorporated into the product).

However, I don't advocate letting "the way we've always done things"
stand in the way of "the way we ought to do things".

If we limit our thinking to "=A1+A2+A3" because it's a tried & true
method, we'll never get: =SUM(A1:A3)
=SUMIF(A1:A3, "0")
and {=SUM((A1:A30)*(A1:A3))}


I agree! In this case we can't change how Excel is designed to work;
-we can change how we work with it, though!

Again, at present, I'm just trying to assess whether it's a software
limit, or a logic/configuration error.

If it's a software limit, then it sounds like I'll have to break the
data into separate lists, exactly as you've described. If I've
simply not configured something correctly, a single list is more
considerate to the folks I have to rely on.


You could use VBA to evaluate the input/selection cell and populate the
DV with the appropriate list.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus


Thank you for the help.

I tried to implement the segregated lists, but it was going to result in more significant changes.

I found a work-around that appears to have gotten me over the hurdle.

I'll keep this in mind if I ever try to do Dynamic Validation again. ;-)
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,182
Default Dynamic Range Name that's driving me nuts

Thank you for the help.

You're welcome; -always glad to help!

I tried to implement the segregated lists, but it was going to result
in more significant changes.

I found a work-around that appears to have gotten me over the hurdle.

I'll keep this in mind if I ever try to do Dynamic Validation again.
;-)


FWIW:
I often build a master list in colA, then run sublists horizontally off
that for the dependent lists. Makes it much easier to manage dependent
lists, IMO!

I dupe all my Excel addins as VB6.EXE apps using Farpoint's Spread.ocx
spreadsheet control. Three features it lacks are DV, CF and
GroupOutlining so I have to code for those when I want my worksheets to
have these. While the coding may seem somewhat more complex, it's
actually easier for doing dependent DV lists than structuring global
scope named ranges that will work reliably.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need Help, this is driving me nuts heitorfjr Excel Discussion (Misc queries) 2 January 15th 06 04:10 PM
question driving me nuts Esaam Excel Discussion (Misc queries) 3 December 1st 05 07:03 PM
Sum and Count are driving me nuts!! Mattrapps Charts and Charting in Excel 1 May 9th 05 07:08 PM
Driving me nuts. Need more nested than 7 Stressed Excel Discussion (Misc queries) 5 April 12th 05 06:20 PM
Excel / VB is driving me nuts!! Andrew Excel Worksheet Functions 2 November 29th 04 05:06 AM


All times are GMT +1. The time now is 09:51 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"