Forum Discussion
How to Link Excel Data across Multiple Sheets
I have some questions for you:
- I don't see any passwords on any of the three sheets. Where are they?
- What is the purpose of each of the three sheets? (I'm asking for the distinct purpose for each sheet)
- 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.
Answering your three questions:
- Because this is only for showing what my actual file looks like.
- 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.
- To be able to remember my passwords. There a lot of emails and I cannot remember all of them.
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.
Can you give a small example of each function?
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.