• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Can I pop up a dynamic match in another sheet? (Take 2)

Bill42

New Member
Okay, I have to admit that I didn't follow the rules, and I posted a question without a sample spreadsheet. I'm probably going to violate another rule by creating a new post instead of adding it to the first one, but I'm afraid everyone will have already discounted my first one. I'll try to add a comment to the first one pointing here just in case.

I have a spreadsheet with 2 sheets. It contains information about movies that I have digital copies of.

Let's start with Sheet2. It is pretty much static, unless I get a new movie. It has 2 columns, the movie name and the movie description.
60773

Sheet1 contains a list of which movie is stored where on my hard drive. This information is very dynamic, based on when I add or delete movie files. I regularly run a directory listing and copy/paste the findings into this sheet. It also has 2 columns, the location of the movie and the movie name. This is what it might have in it at one point in time.
60774

However, if I add a movie, it would look like this after I did a copy/paste of the current contents of the disk:
60775

I would like to tie these two sheets together, so that the movie name in Sheet1 (column B) is tied to the movie name in Sheet2 (column A). In other words, the movie "The Money Pit (1986)" always ties to cell A7 in Sheet2, even though after the copy/paste it moves from B5 to B6 in Sheet1. The same would hold true if I add ore delete movies in Sheet2, causing a specific movie to move up or down.

Furthermore, I would like to be able to hover over the movie name in Sheet1, and have the corresponding description from Sheet2 pop up without having to go to Sheet2 to see it. Although a hover-over/pop-up would be the most convenient, I would be okay if I actually had to click on the movie name in Sheet1. I would also be okay if I had to add a 3rd column with a "generic" formula that could be used to link the movie name in column B of Sheet1 with the appropriate names in column A of Sheet2, as long as it could just be typed into cell C1 and replicated down to the bottom of the sheet.

Here is why I would like this functionality. I have lots (and lots) of movies stored on a hard drive. When my wife and I go on vacation, we take a Raspberry Pi loaded up with LibreELEC (aka Kodi), that we use to watch movies on the TV at our vacation house. Before we leave, we go through the list of movies and load a subset onto a SD Card on the Pi. If I can figure out how to do the above, then we can scroll through Sheet1, picking the movies we want to take with us, and marking them with an "X" in the location column. If we forget what a movie is, we could just hover over it to see what it is. I can then export Sheet1 to a csv file, and run a Perl script to parse it and copy the files from the hard drive to the SD Card.

I know one solution would be to use comments, but (1) I have over 500 movies I would have to create comments for, and (2) I would either lose the comments or they would point to the wrong movie the first time I copied & pasted a new list into Sheet1.

Thanks to anyone who can help me figure out a solution to this. Actually, thanks to anyone who reads this, and wishes me well in my search for a solution, whether they can help or not.
 

vletm

Excel Ninja
Bill42
Did You really reread https://chandoo.org/forum/threads/site-rules-new-users-please-read.294/ ?
You could add new information to You previous same opened thread:
... and there were also replies for You.
You should upload a Sample Excel-file here
- of course, some would create own version from Your sent pictures.
 

Bill42

New Member
Forget it. When I Googled my problem, this site came up several times (in general, not with a solution), and several of the hits referred to how helpful the members were. I never would have bothered if I had known the site admins were such a PITA, and were so picky about following the rules. Yes, I did reread the newby article. No, I didn't attach my 500 line spreadsheet, and I didn't take the time to create a subset of it. After 37 years of IT, I've never felt so put off by a reaction to my posts from an admin.

You can close this thread, and remove my account.
 

vletm

Excel Ninja
Bill42
After 37 years of IT, You know, even without any rules how to get replies and what are rules for.
You copied some of Your datas from file and You could paste those to the new file ... and so on.
 

Marc L

Excel Ninja
Hi !​
A so common issue when using Excel as a database, a mess for beginners !​
As it's easier just using Access for example or any database software …​
With such initial post, I just can give some directions like for example​
use easy formulas at beginner level (via MATCH or VLOOKUP) or​
use the Range Find & Copy methods like explained in the VBA help with their samples and of course,​
as you can operate manually, don't forget the Macro Recorder in order to get your own base of code …​
If you are expecting more, just respect this forum rules and, whatever the forum, it's the same !​
 
Top