Access or Excel??

P

Peggy

This must be so simple and has probably been done a million times. I want a
list of Artist that opens a list of cd's that opens a list of songs on the
cd. I would rather do this in Excel as my Excel abilities far out weigh my
Access abilities.
 
I

iliace

Peggy said:
This must be so simple and has probably been done a million times. I want a
list of Artist that opens a list of cd's that opens a list of songs on the
cd. I would rather do this in Excel as my Excel abilities far out weigh my
Access abilities.
Click to expand...

Let's say you have a sheet called Albums. Header row (starting in A1)
contains artist name, and below each artist name you have album names.
Define this name:

Name: ArtistList
Refers to: =OFFSET(Albums!$A$1,0,0,1,COUNTA($1:$1))

Now, let's say you have some other sheet where you use the drop-downs
for Artist and Album, let's say A1 and B1 on some other sheet.
Validation rule for A1 is Type: List, and Source: =ArtistList. For
B1, it will be:

Type: List
Source: =OFFSET(INDIRECT("Albums!$A$1"),1,MATCH(A1,ArtistList,
0)-1,COUNTA(INDIRECT("Albums!$"&MATCH(A1,ArtistList,
0)&":"&MATCH(A1,ArtistList,0)),1))

What this does is match the artist name in A1 to the first row of
Albums list, thus determining the column. Then, it counts the number
of non-blank cells in that column, and returns the corresponding array
for your validation drop-down.

Once you have the first instance of this working, copy down as
needed. In your own example, where you presumably replace A1 with
something else, ensure the references are relative, and you're good to
go.
 
I

iliace

Sorry, forgot the last part... if you want the list of songs to drop
down, I'd create a new sheet called Songs and arrange the data on it
similarly to what I outlined for Albums. Then, use a similar
validation rule to match it up. You might have some combination of
"Artist - Album" matching to ensure that albums with the same name by
different artists match the correct song list.
 
Top