How to Link Excel Data across Multiple Sheets

[jaypee1650]

In an Excel file, I'm writing my emails and passwords.

In the first sheet, there are all the emails that I have with their passwords respectively on each row. Each row of them has an ID.

In the second sheet, there are all the websites that I have registered an account with. Also, each website has an ID.

In the third sheet, I put the ID of the email from the first sheet, its corresponding website ID, and some info in an additional column.

All of these are added manually. Nothing is automated.

When I register a new email, I will surely add it to the end of the first sheet. Then I have to sort the emails column from A to Z. This action disorders the IDs of the rows. That's why I have to re-order the numbers from the smallest to the largest. But by doing this, the IDs of the emails in the third sheet are changed and not correct. These IDs I've added them by pasting the address of the cell that contains the ID in the original sheet.

I've included below an Excel file that looks similar to the original file just to show what I'm dealing with.

Note: I'm using Excel 2006

[#2]

@jaypee1650 

I have some questions for you:

1. I don't see any passwords on any of the three sheets. Where are they?
2. What is the purpose of each of the three sheets? (I'm asking for the distinct purpose for each sheet)
3. What is the purpose or goal of the entire workbook?

My reason for asking the last two questions is that it would seem to me (as a person who has designed a number of databases over the years) that a single table might serve your purposes. Granted you may need different kinds of sorts--those are all easily accomplished with a single database (single table).

And a security question: if you're doing this to keep track of all your passwords for all the websites you visit--a task well worth undertaking--are you aware that you might be setting yourself such that a determined hacker could pretty easily gain access. Excel's password protection on spreadsheets is not as secure as some apps you could get (1Password, for example: https://1password.com/ ).

That said, 1Password--which I use--has a single file for login information on the hundreds of such combinations of user id and password that I have. There are also separate "windows" for, oh, credit cards, passports, drivers licenses, etc etc....but the kind of info  you're collecting is all in one spot, with extra fields for notes, URLs, etc.   So if you're determined to go ahead and create your own, that's fine, but let's see if we can help you develop the most effective way to organize it.

[#3]

@mathetes 

Answering your three questions:

1. Because this is only for showing what my actual file looks like.
2. Already said it in the question: the first one is for all the emails, the second one is for all the websites and the third one is to link the first two.
3. To be able to remember my passwords. There a lot of emails and I cannot remember all of them.

[#4]

@jaypee1650 

Do you actually have different emails as well as different passwords for each website?

I understand unique passwords totally; and I even have three or four emails, and I make use of dual-factor authentication on a number of (financial) websites, with an app that generates a six or more digit number that is only good for a limited period.

So I get your overall purpose entirely. I'm having difficulty wrapping my head around the structure of your database here, and why you're organizing it as you are.

Linking one sheet to another--or data from one sheet to another--is pretty simple. I see you've done some of that by direct cell references. Are you familiar with VLOOKUP, or INDEX and MATCH. If not, those could be what you're looking for.

If, in fact, you have, say, 10 different email addresses, it would be easy to create a single table with those, each of them identified by a shorter mnemonic code, so that on your master sheet you can just link them via that code to the various websites for which you use each email...no need to have it redundantly connected with each.

So let's begin there...those functions--VLOOKUP, or INDEX and MATCH--are among the most popular methods of linking data across multiple sheets. And having one sheet with emails--given that you seem to have quite a few--would make sense.

It seems to me still, however, that only one other single sheet is necessary, that one holding all the websites and passwords, with (I presume) the passwords actually having a one-to-one relationship with each website, even though the emails are one-to-many. 

[#5]

@mathetes 

Can you give a small example of each function?

[#6]

@jaypee1650 

I've used each of those two methods in your "website accounts" sheet.

Most people find the VLOOKUP fully adequate for the kind of thing you're doing. Under some circumstances, INDEX and MATCH pair up to provide greater versatility; you don't need that.

You might find it useful to read through the complete description of these (and other) functions on a website like this one. https://exceljet.net/search?query=vlookup

And again, I think you'd be fully able to realize your goal by two sheets only,

• sheet for email addresses, containing a mnemonic code and the full email
• a sheet websites, containing
  • email code
  • VLOOKUP to retrieve full email
  • website name
  • website URL
  • password
  • other notes

The reason for having only those two: all of the data on the websites sheet is unique to each website (assuming you never re-use passwords); but you do (I'm assuming) re-use some emails. I say that because there's really no reason to have a unique email/username for each and every website. Maybe for some, but not for every one.