Is this possible with Excel?

VoucherMan

After my recent success with VBA (getting someone to write it for me) A friend asked me if it could be used to help on one of his sheets.
Not sure it's within the scope of Excel so thought I make some enquiries before pursuing it.

A rough outline.

He has lists of songs with the artist at the top of the list. Whenever a new song is available he adds it to the list (using the check-box to the right if he later gets the song).

A bit like this



There are, I think, 10 artists/20 columns on each sheet then he starts a new sheet.


The question asked - would it be possible to add a sheet at the beginning where he could type in the artist's name & the song title, and then have the song automatically added to the next empty line of the specified artist's column.

I'm guessing something like this

DevCoder

In VBA? Yes easily.

Pseudo code
Assuming the artist names are store in row a on a sheet called artistdetailsheet, create this as range "artistdetailname"
Assuming the inputting sheet is "Mainsheet", create the cell to be input "artist" as a range "ArtistName", the song as "songName"

With Mainsheet.Range("ArtistName").Value
for each cell in artistdetailsheet.range(artistdetailname)
if cell.value = Mainsheet.Range("ArtistName").Value ' we have found the artist column, store the column letter
foundcolumn = chr(ActiveCell.Column+64) ' A is ascii 65 so adding 64 to the activecell.column number gives you an alpha equivalent when passed to chr
end if
Next
End
' If foundcolumn is blank then the artist doesn't exist, youll need to create a new column using VBA with the artistname in row a (I can show code how to do this if needed)
'Now we know the column lets iterate down and see if the song already exists

With Mainsheet.Range("SongName").Value
for each cell in artistdetailsheet.Range(foundcolumn & "1:"& foundcolumn & "100")

if cell.value = Mainsheet.Range("SongName").Value ' we have found the song name, it already exists so don't create, move 1 to a found integer.
FoundSong = 1
End if

Check if the cell is blank, if so we can assume end of list of songs in column

if cell.value ="" then Exit For
Next
End

If FoundSong = 0 then ActiveCell.Value = Mainsheet.Range("SongName").Value

This is really rough pseudo code, but you should get the general idea of what its doing.

johnmc

VoucherMan wrote: »

The question asked - would it be possible to add a sheet at the beginning where he could type in the artist's name & the song title, and then have the song automatically added to the next empty line of the specified artist's column.

In short "Yes".

He will need a list of artistes so that it actually matches up with the tab, otherwise he will get lots of #N/A.

One way of doing this is to look up the tab names when the spreadsheet opens. This will populate a lookup table which can be used as the source for a dropdown box on the name field.

Next is to record a macro that uses the artiste field name to GOTO the tab.

The best way to learn how to do these things and all the other wonderful stuff that Excel has to offer is to actually take it on as a project.

There are lots of online resources for this.

Register for free with www.utteraccess.com and use Google for specific "how to".

Personally, though, I would use Access.

youtellme90

It's certainly possible, the specific sheet where the artist resided would just be 'looking' at the front sheet and when something was added it would add it to the list.

You could even have it so you filled in a few and had a 'run' button which 'copied' the information you'd entered to the relevant sheet.

Have you thought about doing it with a form instead? Using Access would potentially make it even easier? No need for multiple 'sheets', you'd just have a large database. (Although I understand if there's a large amount of information already in Excel you/he would be hesitant to move it).

This link might help with the setup of forms:

http://spreadsheets.about.com/od/datamanagementinexcel/ss/090717_data_form.htm

victor2

It does look like it's really a database application.
You've already had suggestions how Excel could handle it, but future requirements could become more of a challenge, when a database could easily handle it.
Not a great fan of Access, but there's a good chance an Excel user will also have it available.

DevCoder

As mentioned its betetr in a dabatbase but you can do excel

Further to the pseudo code above I thought I would give it a shot.

Ive made some progress but gotta go cook dinner, heres the code so far, the only issue is the Iterationd down the foundcolumn doesnt iterate (+1) when searching the column for the song name. If no one else has spotted my stupid mistake when I get back Ill sort it

Sub Button2_Click()

Dim FoundSong As Integer
Dim FoundColumn As String
Dim FoundRow As Integer
Dim ArtRange As Range
Dim SongRange As Range
Dim ArtName As String
Dim SongName As String

ArtName = ThisWorkbook.Sheets("InputSheet").Range("C3").Value
SongName = ThisWorkbook.Sheets("InputSheet").Range("C4").Value

ThisWorkbook.Sheets("DetailSheet").Activate

For Each ArtRange In ThisWorkbook.Sheets("DetailSheet").Range("A1:M1")
 If ArtRange.Value = ArtName Then
   ' Found Artist Name
   FoundColumn = Split(ActiveCell(1).Address(1, 0), "$")(0)
   MsgBox (FoundColumn)
   Exit For
End If
     
If ArtRange.Value = "" Then
  Exit For
End If
Next

If FoundColumn = "" Then ' We didnt find the artist name
  ActiveCell.Value = ArtName
  FoundColumn = Split(ActiveCell(1).Address(1, 0), "$")(0)
End If
  
 ' Code to Find SongName
 For Each SongRange In ThisWorkbook.Sheets("DetailSheet").Range(FoundColumn & "2:" & FoundColumn & "100")

 If SongRange.Value = SongName Then
   ' Found Song Name
   FoundRow = ActiveCell.Row
   MsgBox (FoundRow)
End If
    
If ActiveCell.Value = "" Then
   ActiveCell.Value = SongName
  Exit For
End If

Next

End Sub

DevCoder

Duh

Working Code

Sub Button2_Click()

Dim FoundSong As Integer
Dim FoundColumn As String
Dim FoundRow As Integer
Dim ArtRange As Range
Dim SongRange As Range
Dim ArtName As String
Dim SongName As String

ArtName = ThisWorkbook.Sheets("InputSheet").Range("C3").Value
SongName = ThisWorkbook.Sheets("InputSheet").Range("C4").Value

ThisWorkbook.Sheets("DetailSheet").Activate

For Each ArtRange In ThisWorkbook.Sheets("DetailSheet").Range("A1:M1")
 If ArtRange.Value = ArtName Then
   ' Found Artist Name
   FoundColumn = Split(ArtRange.Address(1, 0), "$")(0)
   MsgBox (FoundColumn)
   Exit For
End If
     
If ArtRange.Value = "" Then
  Exit For
End If
Next ArtRange

If FoundColumn = "" Then ' We didnt find the artist name
  ArtRange.Value = ArtName
  FoundColumn = Split(ArtRange.Address(1, 0), "$")(0)
End If
  
 ' Code to Find SongName
 For Each SongRange In ThisWorkbook.Sheets("DetailSheet").Range(FoundColumn & "2:" & FoundColumn & "100")

 If SongRange.Value = SongName Then
   ' Found Song Name
   FoundRow = SongRange.Row
   MsgBox (FoundRow)
End If
    
If SongRange.Value = "" Then
   SongRange.Value = SongName
  Exit For
End If

Next SongRange

End Sub

DevCoder

XLSM file here

https://anonfiles.com/file/de60988dd91a1f2fc2579f604a28e8f9

VoucherMan

Thanks for the suggestion & comments.

At this stage I hadn't been thinking of more than 'can it be done?'

Certainly not 'how does he do it?'

It'll probably be a few days now before my heads clear enough to try and understand it.

I think he's aware that a database would be better but the spreadsheet works so he's not bothered changing it. I'm not sure if he even has Access. He's still using Excel 98 despite my attempts to get him to upgrade (if it ain't broke why fix it).

krisdorey wrote: »

Assuming the artist names are store in row a on a sheet

They're on the top row but over several sheets. 10 per sheet (A1,C1,E1,.....S1) repeated over several sheets, but it's easy enough to name a range covering multiple sheets. At least in Excel 2007. I assume it's the same in '98.

johnmc wrote: »

The best way to learn how to do these things and all the other wonderful stuff that Excel has to offer is to actually take it on as a project.

I did once consider learning it but never really got into it. I can usually find a standard formula to do what I want. For the limited times VBA would be useful it didn't seem a worthwhile use of time. I've a number of other time consuming projects on the go as it is. Plus there seem to be plenty of people happy to help out, many even seem to relish the challenge.
With that in mind I'm hoping to get someone to write a complete function which can be dropped into the workbook. I might volunteer to sort out some of the other details for him. Obviously a few named ranges will need adding and probably a few other small changes but the main code I'd prefer not to touch. I know there's no way he would.


Edit.

Since I started writing this krisdorey has made a couple of replies (which would seem to confirm the comment I made about enjoying the challenge)
Thanks krisdorey I'm going to give it a try although with my current concentration level I don't think I'll get far.
By the way I'm not sure what's up with the file you uploaded. It says 35kB on the page but just downloaded an empty zip file. Doesn't matter though as I copied the code from the post above.

DevCoder

No worries voucherman. You're right some coders do like the challenge (albeit Im more an android and iOS developer)

If you send me (email kris.dorey"at"gmail.com) the xls that youll be working with then I can build the code into that (multi tab support etc).

It's not particulary elegant (as mentioned a DB is really what would be best) but it does work.

VoucherMan

It's amazing some of the people you come across on the internet. If only our I.T. department was this helpful maybe we'd have a system that didn't break down so often!

Certainly wasn't expecting this. I've not got a copy of the file. In fact I'm not sure he'd be willing to let it go if he knew. Doesn't like comments about his choice of music:D
I've put together a dummy file though. the layout is the same as his. It's actually 13 artists along the top, with titles below & the column to the right just being used to count.
I've only added 3 sheets to this file. His had 10 the last time I saw it. I think it's got 14 or 15 now!

Also the sheet names he uses are fairly random.

If you are able to do anything with it thanks. Even if you can't thanks for the offer.

Meanwhile Happy Christmas and I hope this won't be keeping you from any family duties. Although I've used the 'I can't come over this afternoon I've got some important work to do once so I can't really comment.:cool:



I've emailed the file. Just in case there's a copy here. Different host as I had the same problem again with an empty file.

Song File.zip
https://gigafileupload.com/file/0461186364283082.zip