Re: Access or Excel??

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 <iasaf...@xxxxxxxxx> 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,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.

.

Relevant Pages

  • This is WHY the Beatles are the greatest
    ... songs or albums were placed atop, top 5, top 10, etc. ... Musical rankings feats: number one songs, ... Total number of different songs covered by other artists (their ... Total number of visual references in other artists’ ...
    (rec.music.beatles)
  • Re: Simple DBMS in VB
    ... I am very familiar with binary file access in VB. ... songs of a particular genre, Number of songs by a particular artist, ... Number of albums, number of albums by a particular artist, Number of ... Display a list of artists then the list of albums for each artist ...
    (microsoft.public.vb.general.discussion)
  • Saving/Loading Table Data to/from file
    ... Master Table = Artists ... Detail Table = Albums ... Detail Table = Songs ... Bmp: TBitmap; ...
    (alt.comp.lang.borland-delphi)
  • Saving/Loading Table data to/from file
    ... Master Table = Artists ... Detail Table = Albums ... Detail Table = Songs ... Bmp: TBitmap; ...
    (borland.public.delphi.database.ado)
  • Re: GLEE volume three
    ... Most actual musical artists release original material, ... make an album, I would think that the desire should be to create fresh hits, ... Ronstadt songs for the first time on her original albums. ...
    (rec.arts.tv)