I want to use a named range, which is a single value as the "equal to"
criteria in the advanced filter dialog box.

I have a named range of a single cell I call "FlicNum".

I have a list of 'flicnums' and actor numbers. With over 130 thousand
DVDs in the main list, the actor list index is about 660 thousand records
long.

If I filter on "FlicNum" I should only see a list of actor numbers that
tie to that FlicNum. That is the goal. Getting the filter to accept my
"variable" is the obstacle.

If I enter a 'flicnum' (DVD ID number) by hand, it filters on it just
fine. How do I plug in a "variable", or named range into a filter dialog
or specification?

Describe everything you have. When you apply the filter what's the criteria
range, what is in the criteria range, what are the headers in your table
etc.

When I create a test table, with the headers Flicnum and Actornum, put some
data in there, then use H1:H2 as criteria range with Flicnum in H1 and
=Flicnum in H2 it filters fine, when I change the value in the named cell
called Flicnum and refresh the filter it works fine and filter for the new
value
--


Regards,


Peo Sjoblom


"Pieyed Piper" g wrote in
message ...


I want to use a named range, which is a single value as the "equal to"
criteria in the advanced filter dialog box.

I have a named range of a single cell I call "FlicNum".

I have a list of 'flicnums' and actor numbers. With over 130 thousand
DVDs in the main list, the actor list index is about 660 thousand records
long.

If I filter on "FlicNum" I should only see a list of actor numbers that
tie to that FlicNum. That is the goal. Getting the filter to accept my
"variable" is the obstacle.

If I enter a 'flicnum' (DVD ID number) by hand, it filters on it just
fine. How do I plug in a "variable", or named range into a filter dialog
or specification?

On Tue, 10 Nov 2009 08:47:15 -0800, "Peo Sjoblom"
wrote:

Describe everything you have. When you apply the filter what's the criteria
range, what is in the criteria range, what are the headers in your table
etc.

When I create a test table, with the headers Flicnum and Actornum, put some
data in there, then use H1:H2 as criteria range with Flicnum in H1 and
=Flicnum in H2 it filters fine, when I change the value in the named cell
called Flicnum and refresh the filter it works fine and filter for the new
value

I have two columns One is headered "ID" the other "ActorNumber".

"FlicNum" comes from another worksheet, but I can replicate it in this
worksheet, though it should not be required.

I made the range into a "table". To use advanced filters on the table,
I can manually enter any number to sort on, and the list sorts to only
entries with that number just fine. It appears like a worksheet that only
has those few records (rows).

I want to be able to plug that sort criteria number into the sort
dynamically. based on the FlicNum "variable" I create on the other sheet.

So far, manual filtering works fine.

Is everyone asleep?


On Wed, 11 Nov 2009 04:49:07 -0800, Pieyed Piper
g wrote:

On Tue, 10 Nov 2009 08:47:15 -0800, "Peo Sjoblom"
wrote:

Describe everything you have. When you apply the filter what's the criteria
range, what is in the criteria range, what are the headers in your table
etc.

When I create a test table, with the headers Flicnum and Actornum, put some
data in there, then use H1:H2 as criteria range with Flicnum in H1 and
=Flicnum in H2 it filters fine, when I change the value in the named cell
called Flicnum and refresh the filter it works fine and filter for the new
value

I have two columns One is headered "ID" the other "ActorNumber".

"FlicNum" comes from another worksheet, but I can replicate it in this
worksheet, though it should not be required.

I made the range into a "table". To use advanced filters on the table,
I can manually enter any number to sort on, and the list sorts to only
entries with that number just fine. It appears like a worksheet that only
has those few records (rows).

I want to be able to plug that sort criteria number into the sort
dynamically. based on the FlicNum "variable" I create on the other sheet.

So far, manual filtering works fine.

Excel 2007 Advanced Filter
Nothing new here.
Did it in my sleep.
http://www.mediafire.com/file/gmvzyz0mzgt/11_14_09.xlsm

On Sat, 14 Nov 2009 17:12:44 -0800 (PST), Herbert Seidenberg
wrote:

Excel 2007 Advanced Filter
Nothing new here.
Did it in my sleep.
http://www.mediafire.com/file/gmvzyz0mzgt/11_14_09.xlsm


That is nice, and thank you very much for your work, but is there no
way to perform these list generations without macros?

The file shows both methods:
Manual: See the "Advanced Filter" picture.
Macro: See macro linked to "Advanced Filter" button.

On Sun, 15 Nov 2009 07:43:52 -0800 (PST), Herbert Seidenberg
wrote:

The file shows both methods:
Manual: See the "Advanced Filter" picture.
Macro: See macro linked to "Advanced Filter" button.


So, it still, in each case, requires a manual operation?

Is there no way to have the FlicNum selection I make on my main sheet
cause this filtering to occur dynamically, ie without the need for a
button or user injection of the number?

On Sat, 14 Nov 2009 17:12:44 -0800 (PST), Herbert Seidenberg
wrote:

Excel 2007 Advanced Filter
Nothing new here.
Did it in my sleep.


I didn't say it was an advanced, advanced filter question. :-)

The director lookup is a simple vlookup, since there is only one per
movie.

The actor listing is what I am after. It is a one-to-many database
operation I am asking for in a spreadsheet... I know.

Thanks for your help.

Here is my screenshot. It is now updated to a single DVD list instead
of the three section list I am almost done with it:

http://i255.photobucket.com/albums/h.../screensht.jpg

Excel 2007
Advanced Filter
Turned Macro into an Event Macro.
http://www.mediafire.com/file/jngxzj...11_14_09b.xlsm

On Mon, 16 Nov 2009 07:42:00 -0800 (PST), Herbert Seidenberg
wrote:

Excel 2007
Advanced Filter
Turned Macro into an Event Macro.
http://www.mediafire.com/file/jngxzj...11_14_09b.xlsm


That one is cool. Thanks!

I now use a "button" to "pop" my DVD case images into my sheet.

It starts by deleting the previous image, so there is no pile of shots
there.

It also relies on the FlicNum value, and works quite well. Though the
lookup occurs before the macro runs, so the macro does not reference it.
It references a file name I pass to it.

Can I place my routines inside yours, and have the DVD image 'pop' in
whenever I change FlicNum?

Here is my current code.

The only other question I have is Can you place "table4" in another
sheet, and modify your code to point at and fill that table in the other
sheet?

I was able to do it with the button macro, but the always on macro
doesn't want to let me put the other sheet name in place of "Data" in the
Table4 reference area.

My code pops in a picture. Yours pops in a list. Mine shows up as a
macro, your only under "code". I do not understand the difference in how
things are declared here. Anyway, if you could encapsulate my routine
inside yours and place "Table4" on another sheet, I will be able to adapt
it into my sheet names.

I can supply those structures as well, if needed, but I did pretty well
adapting the button macro version.

Here is my code:

Sub Pop()
On Error Resume Next
ActiveSheet.Shapes("Popped").Delete
InsertPicture Range("H7").Value, _
Range("H7:I22"), "Popped"
End Sub
Sub InsertPicture(PictureFileName As String, TargetCells As Range,
picName As String)
' inserts a picture and resizes it to fit the TargetCells range
Dim p As Object, t As Double, l As Double, w As Double, h As Double
If TypeName(ActiveSheet) < "Worksheet" Then Exit Sub
If Dir(PictureFileName) = "" Then Exit Sub
' import picture
Set p = ActiveSheet.Pictures.Insert(PictureFileName)
'Name the picture so you can delete it later....
p.Name = picName
' determine positions
With TargetCells
t = .Top
l = .Left
w = .Offset(0, .Columns.Count).Left - .Left
h = .Offset(.Rows.Count, 0).Top - .Top
End With
' position picture
With p
.Top = t
.Left = l
.Width = w
.Height = h
End With
Set p = Nothing
End Sub

I have a sheet called "Master_Pane", which is where I want table 4 to
end up.

The "Data" table is called "Acted_In" and the fields (headers) are
called "ID" for the flic number and "Actor_id" is the number which is
referenced to on the "Actors" sheet as "Actor_id" and "Actor".

That is how I keep the actor list only once by using numbers in the
"Acted_In" reference and doing a lookup on the numbers in the Actors
sheet. Otherwise the database (spreadsheet) size nearly doubles.

Also "FlicNum" is on another sheet (Master_Pane). I get a fault when I
move it there since you tie it all to one sheet.

On Mon, 16 Nov 2009 20:57:43 -0800, Pieyed Piper
g wrote:

On Mon, 16 Nov 2009 07:42:00 -0800 (PST), Herbert Seidenberg
wrote:

Excel 2007
Advanced Filter
Turned Macro into an Event Macro.
http://www.mediafire.com/file/jngxzj...11_14_09b.xlsm


That one is cool. Thanks!

I now use a "button" to "pop" my DVD case images into my sheet.

It starts by deleting the previous image, so there is no pile of shots
there.

It also relies on the FlicNum value, and works quite well. Though the
lookup occurs before the macro runs, so the macro does not reference it.
It references a file name I pass to it.

Can I place my routines inside yours, and have the DVD image 'pop' in
whenever I change FlicNum?

Here is my current code.

The only other question I have is Can you place "table4" in another
sheet, and modify your code to point at and fill that table in the other
sheet?

I was able to do it with the button macro, but the always on macro
doesn't want to let me put the other sheet name in place of "Data" in the
Table4 reference area.

My code pops in a picture. Yours pops in a list. Mine shows up as a
macro, your only under "code". I do not understand the difference in how
things are declared here. Anyway, if you could encapsulate my routine
inside yours and place "Table4" on another sheet, I will be able to adapt
it into my sheet names.

I can supply those structures as well, if needed, but I did pretty well
adapting the button macro version.

Here is my code:

Sub Pop()
On Error Resume Next
ActiveSheet.Shapes("Popped").Delete
InsertPicture Range("H7").Value, _
Range("H7:I22"), "Popped"
End Sub
Sub InsertPicture(PictureFileName As String, TargetCells As Range,
picName As String)
' inserts a picture and resizes it to fit the TargetCells range
Dim p As Object, t As Double, l As Double, w As Double, h As Double
If TypeName(ActiveSheet) < "Worksheet" Then Exit Sub
If Dir(PictureFileName) = "" Then Exit Sub
' import picture
Set p = ActiveSheet.Pictures.Insert(PictureFileName)
'Name the picture so you can delete it later....
p.Name = picName
' determine positions
With TargetCells
t = .Top
l = .Left
w = .Offset(0, .Columns.Count).Left - .Left
h = .Offset(.Rows.Count, 0).Top - .Top
End With
' position picture
With p
.Top = t
.Left = l
.Width = w
.Height = h
End With
Set p = Nothing
End Sub

I have a sheet called "Master_Pane", which is where I want table 4 to
end up.

The "Data" table is called "Acted_In" and the fields (headers) are
called "ID" for the flic number and "Actor_id" is the number which is
referenced to on the "Actors" sheet as "Actor_id" and "Actor".

That is how I keep the actor list only once by using numbers in the
"Acted_In" reference and doing a lookup on the numbers in the Actors
sheet. Otherwise the database (spreadsheet) size nearly doubles.

All routine operations
Zzzz....Zzzz...

On Tue, 17 Nov 2009 11:04:11 -0800 (PST), Herbert Seidenberg
wrote:

All routine operations
Zzzz....Zzzz...

So, why will the button macro let me place Table4 on another sheet, but
the always running macro will not? Also, I need to examine FlicNum from
it's location on another sheet.

Though perhaps routine for you, I am having a hard time declaring more
than one sheet in "With sheets()". I suspect that is not the correct way
to do it, but I do not know, so it is not routine for me.

On Tue, 17 Nov 2009 17:44:33 -0800, Pieyed Piper
g wrote:

On Tue, 17 Nov 2009 11:04:11 -0800 (PST), Herbert Seidenberg
wrote:

All routine operations
Zzzz....Zzzz...

So, why will the button macro let me place Table4 on another sheet, but
the always running macro will not? Also, I need to examine FlicNum from
it's location on another sheet.

Though perhaps routine for you, I am having a hard time declaring more
than one sheet in "With sheets()". I suspect that is not the correct way
to do it, but I do not know, so it is not routine for me.


Is "Zzzz.....Zzzz..." A count of your IQ point total, Herbert?

WAKE UP!

Meow