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.


On Dec 24, 6:38*pm, iliace wrote:
Peggy wrote:
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.

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,ArtistL ist,
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.