Create relationship between 2 spreadsheets

I am new to this forum. So I will try to explain as best as possible.

I work in a high school and am trying to use Excel for assigning lockers to students. I am using Excel 2008 for Mac with system 10.4.11

I am using two Spreadsheets. SS #1 has column A (Lock Serial #s) and Column B (the corresponding combination for each serial #). See attached

SS #2 includes the locker #, student ID#, student name, Serial # (Column D) and Combination (Column E). See Attached.

On SS #2 we input the lock serial number being assigned to the locker by scanning a barcode serial # tag. What we want the SS #2 to do at that point is, to search for that lock serial number in SS #1 Column A, and when it finds a match, take the corresponding combination from SS #1 Column B, and input it into SS #2 Column E.

I believe I need to create a formula using Lookup, etc., but have no idea of how to do this. Any help would be greatly appreciated. Thank you.

1) Open both worksheets to set this up.

2) In SS2 cell E2 put this formula, then copy down as far as you'd like;

=IF(ISNUMBER(MATCH($D2,'[SS #1.xls]Sheet1'!$A:$A, 0)), INDEX('[SS #1.xls]Sheet1'!$B:$B, MATCH($D2, '[SS #1.xls]Sheet1'!$A:$A, 0)), "not found")

3) Now save/close the SS1

4) The sheet2 formulas should now have changed to include the full path to the SS1 file.

5) Save the SS2 file.

You can now use SS2 without opening SS1.

The formula is self-adjusting. The $D2 should change itself to $D3, $D4 etc as you copy that cell downward. You're copying the cell downward. not the formula, yes?

1) Enter the formula in E2
2) Grab the lower-right corner "handle" of that cell and drag downward

Originally Posted by UncleI

Can I use multiple spreadsheets at the same time for relationships.

Yes, of course.

Can we use SS #2 to also search either the name or the ID# fields and have it enter the corresponding information (name or ID# and grade, while still using the other fields that you helped me on.

Yes, we've already shown you how to do it matching on the serial #. There is absolutely no difference in looking up student IDs, you simply adjust the formula to search the correct workbook in the correct column.

You need to step back and absorb the INDEX/MATCH function. If you understand it, you can change it over and over for all your needs. The key part of the formula you have so far is:

=INDEX('[SS #1.xls]Sheet1'!$B:$B, MATCH($D2, '[SS #1.xls]Sheet1'!$A:$A, 0))

That simplifies to:

=INDEX(valuestoreturn, MATCH(valuetomatch, rangetomatch, exactmatchrequired))

Let that sink in and then start making substitutions for your new need. If it's the name you're trying to look up, then the valuestoreturn will be column with names in the workbook you're searching.

The valuetomatch is the cell where you're entering the student id.

The rangetomatch is column with student ids in the workbook you're searching.

Once you get that worked out, you fix the first part of the formula, too. The first part checks to make sure there IS a match with the MATCH() formula. So once you have the new references, just copy the MATCH() portion into the beginning where the ISNUMBER() test is.

Give it a try. This is worth figuring out.

That throws a lot of people. In it's purest form, yes, ISNUMBER() is used to test a cell for a numeric value.

But that's not how I'm using it. I'm using it to test the results of the MATCH() formula. MATCH() always returns a numeric value or an error. If the MATCH() returns an error, it would cause the INDEX() to error and the result of your formula would be an error. Most people hate that.

So, I test the MATCH() first to see if it properly returns a number. I wrap it in an ISNUMBER to see what we get.

If we get a number, I run the INDEX/MATCH formula.
If we don't get a number, I don't run the formula at all, just return a null value.



That's why I pulled out the INDEX / MATCH and explained it. That's the formula you need to get working. Once it works, drag it around and you'll see what I mean about getting errors sometimes. Then put the test back in the front.

Post up the troublesome workbook(s). ZIP them if necessary.

1) You have a typo in your formula, its StudentsDB, not StudentDB.

2) You have to edit your INDEX() column to the column of data you want returned.

In D2 you have INDEX([StudentsDB.xls]Sheet1!$A:$A....but column A on that sheet is the student ID, you want the GRADE, so that needs to change to $C:$C.

3) Then you want to MATCH() the value in B2 to the student ID, so you're MATCH formula needs to be:
MATCH($B2, [StudentsDB.xls]Sheet1!$A:$A, 0)

...and that needs to be fixed in two places in that formula.

The final result after editing to the correct columns is:

=IF(ISNUMBER(MATCH($B2,[StudentsDB.xls]Sheet1!$A:$A, 0)), INDEX([StudentsDB.xls]Sheet1!$C:$C, MATCH($B2, [StudentsDB.xls]Sheet1!$A:$A, 0)), "not found")

I've never done a mail merge myself, but it's done all the time so I imagine it's not that difficult. Press F1 and look up MAIL MERGE in the help files. This is what came up for me when I did that:

Print labels by using Excel data in a Word mail merge
By creating a mail merge in Word, and then linking to a list in Excel, you can turn worksheet data such as the addresses of your customers into printable mailing labels.

1. In Microsoft Excel, set up the data to use in the mail merge.

1. 1. Make sure the data is in list format: each column has a label in the first row and contains similar facts, and there are no blank rows or columns within the list. You'll use the column labels to specify what data goes where in the mail merge.
   2. Make sure the column labels clearly identify the type of data in the column; this helps you select the right data as you construct the mail merge. For example, labels such as First Name, Last Name, Address, and City are better than Column 1, Column 2, Column 3, and Column 4.
   3. Make sure you have a separate column for each element that you want to include in the mail merge. For example, if you're creating form letters and want to use each recipient's first name in the body of the letter, make sure you have a column that contains first names, rather than a single column with both first and last names. If you want to refer to each recipient by title and last name, such as Mr. Smith, you can use a single column containing both titles and last names, or use one column for title and another for last name.
   4. To make the list easy to find when you select it for the mail merge, give it a name:
      • Select the entire list.
      • On the Insert menu, point to Name, and then click Define.
      • Type a name for the list.
      • Click Add, and then click Close.
2. Save and close the workbook that contains the data.
3. Switch to Microsoft Word.
4. On the Tools menu, point to Letters and Mailings, and then click Mail Merge Wizard.
5. In the first two steps of the Mail Merge Wizard, select the document type and start the document. If you're creating mailing labels, click Labels in Step 1, and then click Label options in Step 2 to select the size and type of labels to print.
   For more information about these steps, see Word Help.
   
6. In the third step, under Select recipients, click Use an existing list, and then click Browse.
7. In the Look in list, click the folder in which you saved the workbook with your data, click the workbook, and then click Open.
8. In the Select Table dialog box, locate and click your list. Make sure the First row of data contains column headers check box is selected, and then click OK.
9. In the Mail Merge Recipients dialog box, click any column labels in your data that correspond to the Word identifiers on the left. This step makes inserting your data in the form documents easier. For more information about matching fields, see Word Help.
10. If you want to include only selected recipients in the mail merge, click
11. and select the recipients you want.
12. Use the rest of the wizard steps to write, add recipient information, preview, personalize, save, and print or e-mail your documents. For information about these steps, see Word Help.

Notes

• While Word has your workbook open to create the mail merge, you can't open the workbook in Excel. If you need to see the data and its column labels, go to Step 3 or 5 of the wizard and click Edit recipient list.
• You can continue to use mail merges with Excel data that you created in earlier versions of Word. To open and use these mail merges in Word 2002 or later, you'll need to install Microsoft Query. Query is no longer required to create new mail merges in Word 2002 or later.

Ah, my bad. I think you simply need to download and install the 39 barcode font.

Google: Excel 39 barcode
...the first 10 hits seem to offer the font for free and multiple tutorials on using them.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

Put this formula in B2, then copy the cell downward:

=E2 & ", " & D2

1) Format the column as TEXT before you do anything else
2) Highlight the column and do a SEARCH/REPLACE:

Search: †
Replace: (blank)

Ok. I thought I had everything working just fine. Then when I moved them to a new location, I get an error message saying circular reference error. I am attaching the StudentDB.xls file, the LockDB.xlsb file, and the LockerAssignmentDB.xlsb file. Again, what I am trying to do is use the first two DB files to fill in on the LockerAssignmentDB.xlsb file. Giving me the option of using either the Student # to find the Student name and grade, or Student name to find the student # and grade. I am down to the last 8 days of the school year and am really in a crisis mode now. I would appreciate you looking at my spreadsheets and see if you can tell which formula I have incorrect. I again appreciate any help you can give me. Thanks again.

P.S. I am removing the letter "b" from the two xlsb files, since this forum will not allow me to upload xlsb files. Once received, you will need to add the "b" back to LockerAssignmentDB.xls and LockDB.xls files

Originally Posted by UncleI

So thanks for forcing me to learn how to do them, although I still do not totally understand how they work.

Um, ok? Not sure if you're being sarcastic or not, so I'll take this as an actual thanks for the lengthy and free assistance you have received.

And yes, learning is what this place is all about, so it's good that is occurring.

If there are specific questions you have about the processes/formulas we've created together here, state them specifically and I'll elaborate.

My favorite tool of understanding is the Evaluate Formula icon on the Formula Auditing toolbar, check it out if you have never used it before. It lets you step through the formula in a cell one calc at a time to watch it unfold. Very cool.

Is there a lock command that can lock the formulas, without locking the entries to those fields as well?

Hm, this sounds like a "can I hold my breath and still breath at the same time" kind of situation.

Your cells can have:
1) formulas intended to offer automated results based on other calculations.
2) flat data manually entered

It's up to you to decide HOW the cell will be used. If the cell has a formula and you will allow people to manually adjust the results of the cell, the formula will go away.

If you're working off of a template, so each time you open a new copy all your formulas are in place and intact, this is fine. But if this isn't a template and you're working in your main document all the time, then this bad.

If the results in a formula cell are notably wrong and require adjusting, your process should allow the user(s) to adjust the source cells the formula uses to complete its calculation, hopefully allowing the calculation to correct itself.

If you decide to allow direct entry into those cells, then those formulas will go away forever.

Even if we create a macro to put the formulas back in for you, you've already indicated the formulas are giving the wrong results, so you would be forcing bad answers back into those cells.

I think I need clarification on what you mean by the words "entries to those fields".

You are asking about manual entries into a cell with a formula, not just the ability to select the cell but not change anything?

I don't personally know anything about that particular tool, and it's not actually related to this thread's topic. Best to mark this thread solved and start a new thread with a new title, new threads garner the most attention so you will get a lot more feedback than you would on this thread, which is now pretty much just you and me.

Please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. If the prefix box is no longer available in advanced mode, just add [SOLVED] to the start of the title.