RangeName Define Limitations

[ssGuru]

I have a lookup table and I want a defined range name that returns
only those records where ACTIVE = "Y"
I am using this defined name in a field data validation with a list
dropdown.

Table Structure with 4 fields, Column A,B,C, and D.
Ltype LTypeSh LicFee LicActive
Add-on Apr08 Add Apr08 550 N
Add-on Jan08 Add Jan08 500 Y
Tmp1 Apr08 T1 Apr08 10500 N
Tmp1 Jan08 T1 Jan08 10000 Y


This formula does NOT work to return the 2 active records I expect
where the LicActive field = "Y".
Name Define: LicRec
OFFSET(Lists!$B$2,0,0,COUNTIF(Lists!$D:$D,"='Y'"),1)

This formula DOES return all and only the 4 records
Name Define: LicRec
OFFSET(Lists!$B$2,0,0,COUNTIF(Lists!$D:$D,">a'"),1)

Any thoughts would be appreciated,
Dennis

 

[Peter T]

Hi Dennis

Try this in the Refersto
=OFFSET(Lists!$B$2,0,0,COUNTIF(Lists!$D:$D,"Y"),1)

Regards,
Peter T

 

[Peter T]

I quickly adapted your formula with minor changes to get it working and
return the correct count. However I'm not at all sure it returns the records
you want.

Get it working correctly in cells first, ie array-enter it in cells down
some rows.

Peter T

 

[Dave Peterson]

If your data isn't sorted, then I don't think you're going to end up using a
formula that can define that range.

But since you posted in the .programming newsgroup, I would think that a macro
would be ok.

If that's true:

Option Explicit
Sub testme()
Dim myRng As Range
Dim wks As Worksheet
Dim myCell As Range
Dim myYRng As Range

Set wks = Worksheets("Sheet1")
With wks
Set myRng = .Range("D2", .Cells(.Rows.Count, "D").End(xlUp))
End With

For Each myCell In myRng.Cells
If LCase(myCell.Value) = "y" Then
If myYRng Is Nothing Then
Set myYRng = myCell.Offset(0, -2) 'column B
Else
Set myYRng = Union(myYRng, myCell.Offset(0, -2))
End If
End If
Next myCell

If myYRng Is Nothing Then
MsgBox "No Y's found!"
Else
'a workbook level name
myYRng.Name = "LicRec"
'or worksheet level name
'myYRng.Name = "'" & wks.Name & "'!LicRec"
End If
End Sub

 

[Ron Coderre]

Try this:

With your posted list in cells A15

Put this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER
(instead of just ENTER) on the Lists sheet:

G1:
=IF(COUNTIF($D$2:$D$5,"Y")>=ROW(),INDEX($B$1:$B$5,SMALL(IF($D$2:$D$5="Y",
ROW($D$2:$D$5)),ROW()),0),999)

Copy G1 and paste into G2 and down as far as you need.

Then....Create this range name:
Names in workbook: LicRec
Refers to:
=OFFSET(Lists!$G$1,0,0,MATCH(LOOKUP(REPT("z",255),Lists!$G:$G),Lists!$G:$G,0))

To test:
Select cell H1
<data><validation>
....Allow: List
....Source: LicRec

Using the above example,
the DV dropdown in H1 displays 2 items:
Add-on 8-Jan
T1 8-Jan

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[ssGuru]

Thanks Ron,
My lookup table can grow to any length so I don't want to add formulas
to an unknown number of cells beside the table.

My original range name formula nicely produced ALL records including
only one blank and I have used this paradigm successfully before for
data validation lists. In this instance however when sorting on
column B I also want to restrict to just those records where there is
a "Y" 2 columns to the right (D).

Any other ideas why the OFFSET formula won't work exactly right when
looking for the Ys?

Dennis

 

[ssGuru]

Thanks Dave,
I tried the code with minor changes (Sheet1 to Lists) and ...If
LCase(myCell.Value) = "y" Then... to "Y".

I placed the code in the "Lists" sheet.

After compiling and saving I thought this would create a WorkBook
range name "ListRec". I added =ListRec to a cell data validation to
test and received the Error "A named range you specified cannot be
found" when OKing the change.

Not sure what I did wrong. Any ideas so I can test this process??

Dennis

 

[Ron Coderre]

You won't be able to use the offset function to return the array of
non-contiguous matching cells that can be used by a Data Validation
or lookup function, but maybe a triggered query can build the list.

Example:
Before beginning...Save the workbook

Then..on the LISTS sheet...

A14 contains your sample data
E1: =COUNTIF(D,"Y")&" items"

Select G1
<data><import external data><new database query>
Databases: Excel Files.....Click: OK
Browse to your file...select it....Click OK
Tables: Lists$......Click: Add.....Click: Close
In the results section...Click the dropdown and select: LTypeSh
Expose the Criteria section (by clicking the funnel/glasses button
Criteria Field: LicActive
Value: 'Y' And Not Like [DummyParam]
<file><return data to microsoft excel>
Click the [parameters] button
Check: Get the value from the following cell
Select cell E1 (containing the COUNTIF function)
Check: Refresh automatically when cell value changes.
Click: [OK]
Click: [Properties}
UNcheck: Include field names
Click [OK]
Click [OK]...to close the window

Now you have a query that lists
the LTypeSh items with a LicActive value of "Y"
and that automatically refreshes whenever the count of "Y"
items changes.

Next build a Dynamic Range Name that only includes
the list items returned from the query.

Use that DRN as the list source for the
Data Validation of an input cell.

Done...Whenever the count of "Y" items changes,
the query refreshes and the DV cell will display the
new list.

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



Thanks Ron,
My lookup table can grow to any length so I don't want to add formulas
to an unknown number of cells beside the table.

My original range name formula nicely produced ALL records including
only one blank and I have used this paradigm successfully before for
data validation lists. In this instance however when sorting on
column B I also want to restrict to just those records where there is
a "Y" 2 columns to the right (D).

Any other ideas why the OFFSET formula won't work exactly right when
looking for the Ys?

Dennis

 

[Dave Peterson]

If you check any lower case string, it'll never be equal to an upper case Y.

Change that line back.

 

[ssGuru]

Thanks Ron and especially thanks for the excellent explicit
directions. I've done a lot of VB and Access programming so quite
familiar with queries but I've never looked at the query wizard in
Excel. It was a great look at the Query Wizard. Things didn't work
exactly as shown for me using v2003 but I was able to fumble around
until I got it working. There was one dialogue box that was very
difficult to expose in the process and the sheet seemed to hang till I
was able to expose it.
I was never able to see the parameters to point the update to cell E1
changes as they were always grayed out.
Instead I have it renewing upon opening the template which will work
fine for me.

My template will be distributed to customers so I want to make sure
that the linked query is part of the package I send. I have placed
the query in a sub folder named Query under their main folder on the C:
\drive. Do you think the link will remain active when distributed if
everything placed in the same folders on the clients HD?

I did my range name for my data validation pick list based on a name
using =OFFSET(Lists!$F$2,0,0,COUNTIF(Lists!$F:$F,">a"),1) to expose
just the populated records in col F that are updated by the new query
on sheet activation. Still don't know a better way to just show just
the populated records in a column and ignore all the blanks.

Thanks again for the push in a direction that produced the results I
needed.
Dennis

You won't be able to use the offset function to return the array of
non-contiguous matching cells that can be used by a Data Validation
or lookup function, but maybe a triggered query can build the list.

Example:
Before beginning...Save the workbook

Then..on the LISTS sheet...

A14 contains your sample data
E1: =COUNTIF(D,"Y")&" items"

Select G1
<data><import external data><new database query>
Databases: Excel Files.....Click: OK
Browse to your file...select it....Click OK
Tables: Lists$......Click: Add.....Click: Close
In the results section...Click the dropdown and select: LTypeSh
Expose the Criteria section (by clicking the funnel/glasses button
Criteria Field: LicActive
Value: 'Y' And Not Like [DummyParam]
<file><return data to microsoft excel>
Click the [parameters] button
Check: Get the value from the following cell
Select cell E1 (containing the COUNTIF function)
Check: Refresh automatically when cell value changes.
Click: [OK]
Click: [Properties}
UNcheck: Include field names
Click [OK]
Click [OK]...to close the window

Now you have a query that lists
the LTypeSh items with a LicActive value of "Y"
and that automatically refreshes whenever the count of "Y"
items changes.

Next build a Dynamic Range Name that only includes
the list items returned from the query.

Use that DRN as the list source for the
Data Validation of an input cell.

Done...Whenever the count of "Y" items changes,
the query refreshes and the DV cell will display the
new list.

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


Thanks Ron,
My lookup table can grow to any length so I don't want to add formulas
to an unknown number of cells beside the table.

My original range name formula nicely produced ALL records including
only one blank and I have used this paradigm successfully before for
data validation lists.  In this instance however when sorting on
column B I also want to restrict to just those records where there is
a "Y" 2 columns to the right (D).

Any other ideas why the OFFSET formula won't work exactly right when
looking for the Ys?

Dennis

Try this:

With your posted list in cells A15

Put this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER
(instead of just ENTER) on the Lists sheet:

Copy G1 and paste into G2 and down as far as you need.

Then....Create this range name:
Names in workbook: LicRec
Refers to:
=OFFSET(Lists!$G$1,0,0,MATCH(LOOKUP(REPT("z",255),Lists!$G:$G),Lists!$G:$G,­­0))

To test:
Select cell H1
<data><validation>
...Allow: List
...Source: LicRec

Using the above example,
the DV dropdown in H1 displays 2 items:
Add-on 8-Jan
T1 8-Jan

Is that something you can work with?
Post back if you have more questions.
--------------------------

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

- Show quoted text -- Hide quoted text -

- Show quoted text -