Forum Discussion
How to Link Excel Data across Multiple Sheets
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.