External Cell References

[Sephiroth]

Ok so now I invented a new issue for myself: How to tie in a bunch of excel spreadsheets, into one reference, or how to incorporate data from a few different sources into 1.

So lets say I have Books1-5, Book5 is the master, book1-3 work together and Book 4 needs to populate book 5 as needed.

As far as the individual references I'm seekingare concerned I want to do the following:

IF data from Book 2, matches data from Book 3 somewhere in a column, then the corresponding column in book1 needs to give me the information

For the sake of simplicity lets say Book1 is my attachment, I need to reference both book2 and Book3 to fill column D and E accordingly. The data I'm seeking is text based, and as you'll see I can reference an excel sheet in multiple different ways, using singular equations and references, but as soon as I try combining them I get errors galore! I'm not sure if only a few EXCEL Formulas work well with external references or if I need to play around with VBA. If I need to go the VBA route, I'd definitely like some information on how to find the right methods, or maybe some reference links to research the correct methodology with VBA.

Excel Training LMAO.xlsx This is what my Book1 looks like and my master copy Book5 will simply check if there are any "No's" if there are then the corresponding column will have a bright red "Error" in it, if everything is green it'll say "Good" that's not hard to reference and I'm not having issues making singular external references that don't need a formula. My problem is linking two different external references into one equation to give me a singular result. Line 44 on my attached spreadsheet is EXACTLY how I want the external reference mechanisms to work, but I have had zero luck making it work. I even tried using the SUMPRODUCT(--(REF#>= Book2 ColumnA),--(REF#2<= Book3 ColumnB)) To no avail when using the external links. I'm not sure if the syntax just doesn't apply to an external link or if I'm using the wrong formula, or if VBA is the correct option to use.

I did read something about turning excel into a Database and if that's the way I should go then that's what I'll work on next! (tips on that would also be appreciated!)

[quekbc]

I'm confused but here's what I suspect is happening. The MATCH function is being used incorrectly.

In A46 you've done MATCH(find this, in this, 1)
That last parameter of 1, tells the formula that you want to find the "closest approximate", which will only work correctly if the "in this" parameter is in ascending order. As you are looking for a text, I suspect you didn't know this.

Same issue with A52, you've done MATCH(find this, in this) with no 3rd parameter. By default, in the absence of the third parameter, its the same as putting in a 1 - see above.

In the MATCH functions, it seems you are looking for the name Sam. There are 8 instances of Sam's in column D of the table. You'll need to be more specific in the MATCH function to determine which SAM you are looking for.