Search multiple worksheets for multiple data and display in userform

You have the basic coding in the SEARCH Userform where selecting RECORDING gives the required data so why cannot this be used/modified to find Songs?

I can see no reason for not "combining" the 2 searches.

And do you need sheets RECORDING/TRACKS/RECORDING -TRACKS rather just than the latter as your "database"?

Please Login or Register  to view this content.

One "issue": the search is not a predictive one but simply all songs containing "p" in the title (in the example).

And illustates the need for a single "database"! Or better "links" between multiple "databases".

As I stated before, you have the basis of the required code ..

Please Login or Register  to view this content.

txtArtistSearch is replaced by txtSongSearch

lstSearch by lstSearchTrks

etc

... is the code not yours?

You stiil (IMHO) need to resolve how to best organise your data.

Originally Posted by P.alexander

AlphaFrog, I discovered something that will require a little different coding, but I don’t know how. I see that you used the
sheet(Recordings-Tracks) to pull the data from, which works great until you get to a point where the Recordings side has blank cells.
This results in the Artist and Recording columns being blank (see attached screen shot). Is there a way to code this using
Sheet2 and Sheet4 in combination instead of Sheet6? Or maybe something else?

It would require more than a little different coding. Finding matching tracks on the Tracks sheet and then doing another lookup to find the recording of that track on on the Recordings sheet is technically do-able, but a bit of a PITA. Are you asking to search for the matching Rec. ID on the Recordings sheet for each matched Track?

JohnTopley, I believe you are absolutely correct. Do you know how this could be coded to get the results I need? Thanks.

I'm confused. You agree with Topley about consolidating the data onto one sheet. Then ask again how to do this without consolidating the data. What's the issue with consolidating to one sheet? It seems that a cleanup of the Recordings-Tracks sheet would get you what you want. What am I missing?

Originally Posted by P.alexander

Thanks for the info. The code I currently have was given to me through Excelforum. My knowledge of Excel VBA is very limited. It seems if I use the same code with "txtSongSearch" it won't limit the results to just Trk No, Song Title, Artist and Recording. I don't want the columns of Format, Genre, Released, and Notes to be in my result. Also, the same code will only search the Recordings Sheet and not the Tracks sheet.

I didn't follow that at all. Sorry.

Please Login or Register  to view this content.

Still need to correct the "Recording" ("dummy" in attached): CORRECTED

Originally Posted by P.alexander

AlphaFrog, I am so sorry for the confusion. It is totally acceptable to me to use the "Recordings-Tracks" sheet if the results will give me what I want. The code you sent me before was so very close, except for the missing Artist and Recording columns data. What can I do to make up for this? Thanks again for all your help with this.

This issue is not the code, but the missing Artist and Recording information listed on the Recordings-Tracks sheet. If you fill-in the missing info on the sheet, your problem is solved.

Did you try (updated) code from post #14?

And take a look here on how to organise data .....

https://www.youtube.com/watch?v=rcrsqyFtJ_4

As an example, a CD has many songs but the same song can appear on many CDs [one-to-many relationship].

Similary, an artist can appear on many CDs and a song can be "covered" by many artists [one-to-many relationships].

I do not know the volume of data you have (or plan to ) but I would give some thought how best to organise your "database".

My previous suggestion of combining 2 of sheets was to avoid data redundancy, as the same data is in both sheets, BUT it will be better to "normalise" the data.

On the TRACKS sheet, put this formula in G2

Formula:

Please Login or Register  to view this content.

And this in H2

Formula:

Please Login or Register  to view this content.

Copy these two formulas down the length of the data table

Use this code

Please Login or Register  to view this content.

With the current formula, you would get an "N/A#" if "REC ID" 49 did not exist in "Tracks" so this suggests there no formula in rows 583 down

Change formula to

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

For future reference: please post a file not a picture, which is of no value when trying to identify/correct problems.

In the frmNewTracks code module, add the two .Formula lines from below to the Sub WriteDataToSheet.


Private Sub WriteDataToSheet()
Dim newRow As Long
With Sheet4
newRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(newRow, 1).Value = txtTrackID.Value
.Cells(newRow, 2).Value = txtTrackNo.Value
.Cells(newRow, 3).Value = txtTrackTitle.Value
.Cells(newRow, 4).Value = txtComposer1.Value
'.Cells(newRow, 5).Value = txtComposer2.Value
'.Cells(newRow, 6).Value = txtComposer3.Value
.Cells(newRow, 5).Value = txtLength.Value
.Cells(newRow, 6).Value = txtRecordingID.Value
.Cells(newRow, 7).Formula = "=VLOOKUP(F" & newRow & ",Recordings!A:B,2,0)"
.Cells(newRow, 8).Formula = "=VLOOKUP(F" & newRow & ",Recordings!A:C,3,0)"
End With
'frmRecordings.lstRecordings_Click
End Sub