Access or Excel??
 

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.

Access or Excel??
 

I think I'd use dependent lists to do this. Take a look at this

http://www.contextures.com/xlDataVal02.html
--
HTH,
Barb Reinhardt



"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.

Access or Excel??
 

"Barb Reinhardt" wrote:

I think I'd use dependent lists to do this. Take a look at this

http://www.contextures.com/xlDataVal02.html
--
HTH,
Barb Reinhardt


Thanks. I have never done lists in Excel however I have done then in access
so I should be able to figure it out.


"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.

Access or Excel??
 

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.

Access or Excel??
 

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.