Searching for a matching value from one workbook to another.

Hi folks, I've got a question concerning conditional links between two workbooks... I'm not a very experienced Excel user (at least beyond the basics), but I'm trying to create a system for data entry.

I have two workbooks: "Site Visits" and "Schedule". In "Site Visits" I record the ID number of the site I visit and the date it was visited on. In "Schedule" I have a list of all site IDs. What I want to do is to have the "Schedule" workbook look up the most recent occurrence of a given site ID, then relay back to the "Schedule" the contents of the cell next to it. Getting the contents of the cell is the easy part, I think, but I don't now how to get the appropriate cell in "Schedule" to find only the last, most recent entry in "Site Visits" that matches the correct cell content.

This is basically what I'm trying to write in, say, cell B5 of "Schedule" (sans syntax): "Access 'Site Visits.xls' and find the cell in Column A that matches the content of cell A5 of this workbook, 'Schedule.xls'. Search from bottom up [so that the last entry is the one recognized. Let's say the last entry is in cell A27 of 'Site visits.xls']. Link to the contents of cell B27 in 'Site visits.xls' and add 7 to that value. Print the result in this cell."

Also, would this be any easier if I just used two worksheets within the same workbook?

Any help would be appreciated! I'm using Excel 2010, by the way.

Hey thanks a lot for your help, though that doesn't seem to be working.

I have found that I can get what I want with a VLOOKUP whereby I search for the value that matches my original cell and then return the value of the cell next to it.

HOWEVER, this does not seem to work in reverse. The worksheet that has the original values contains multiple occurrences of the same value. For example:

1B1 12-Apr-10
1B2 12-Apr-10
1B4 12-Apr-10
3B4 15-Apr-10
1B1 15-Apr-10
12B3 19-Apr-10
1B1 23-Apr-10

I need to return the value that corresponds to the last, most recent occurrence of 1B1. In this case it would be "23-Apr-10". VLOOKUP seems to always search top down and returns "12-Apr-10". I haven't found much online regarding the LOOKUP function - at least that pertains to Excel 2010 (Microsoft's own support page makes no mention of it). I wonder if INDEX and MATCH might serve the purpose, but again, I'm not sure if I can search only 1 column, and return the position of only the last occurrence of the value to be searched for.

Any idea how to make this work?

Ok I've figured out something that will work.

First step is to the determine the row number of the last occurrence of a particular value. Let's use my example from above...

This is my primary worksheet (in the same workbook), called "Site Visits.xls"

1B1 12-Apr-10
1B2 12-Apr-10
1B4 12-Apr-10
3B4 15-Apr-10
1B1 15-Apr-10
12B3 19-Apr-10
1B1 23-Apr-10

In my secondary worksheet, "Visit Schedule." I want to know the last time a particular site was visited. I have a row for each "site", but it only appears once. So there is only one row for 1B1, even though it appears several times in the "Site Visits" file"

So let's say that my first entry in "Visit Schedule" is 1B1, in cell A1. In cell A2 I enter the following formula:

=MAX(IF('Site Visits'!A1:A100=A1,ROW('Site Visits'!A1:A100)))

IMPORTANT: You have to hit "Ctrl-Shift-Enter" rather than just enter, which makes this an array formula. I don't really know what that means, other than it works. The resulting formula will have { } brackets around it and will look like this: {=MAX(IF('Site Visits'!C8:C2860=A7,ROW('Site Visits'!C8:C2860)))}. However as soon as you select the cell, the brackets are no longer visible. You MUST hit "Ctrl-Shift-Enter" any time you select the cell contents or the formula will revert to an error state.

Ok, that returns the row number of the very last time the value contained in cell A1 occurs in worksheet "Site Visits". In this example, that value will be "7" since that is the row that last entry of "1B1" is in. That doesn't do a whole lot as it is, but thankfully there is the INDEX function.

In cell A3 of "Visit Schedule" I enter this formula:

=INDEX('Site Visits'!$A:$B,'Visit Schedule'!A1,2)

This takes the value obtained in cell A1 and looks for that row number in the worksheet "Site Visits", within the column range specified (A to B). Then it goes back to the worksheet "Visit Schedule" and returns the value in the 2nd column from the original one specified (which was column A). In this situation, the value returned is "23-Apr-10", which is in the second column from the 7th row.

So far this has been working well... I'm using the following formula:

=INDEX('[Site Visits 2012.xls]Site Visits'!$B:$C,'Visit Schedule'!F6,2).

The problem is that when I sort the data, the cell that contains the appropriate row number to lookup (in this case F6, which was derived using a Max/If) does not update. For instance if this cell was moved after sorting, from C6 where it currently is to C35, the formula in the cell would remain unchanged instead of becoming =INDEX('[Site Visits 2012.xls]Site Visits'!$B:$C,'Visit Schedule'!F35,2), therefore returning incorrect data. I have no idea why this is a happening, and I just spent at least an hour scouring various forums to find out why with no luck. Note: I've tried writing the cell number as F$6 as well, but this also does not work...

As an alternative to this, I also tried the formula suggested above (which appears in many other forums). But every time I do this, I end up with a #N/A. I've tried many variants of the formula and and cell ranges used, all with the same result. This is what I am using (The values I'm looking up are in another workbook):

=LOOKUP(2,1/'[Site Visits 2012.xls]Site Visits'!B$1:B$3000=A6,'[Site Visits 2012.xls]Site Visits'!C$1:C$1000)

Any idea either, how to ensure that my INDEX formula updates when sorting, or what I'm doing wrong with the lookup formula?

Thanks

Yes calculations are set to "automatic".

Does an "Index" formula generally update like any other formula when sorted? I have a couple others in the worksheet, including the Max/If formula mentioned in an earlier response, and they sort fine. I know that putting a $ in a cell number is supposed to take care of that, but in this case it does not. However, if I copy down, the cells are filled in ascending order as they should be...

I've come across the "Lookup(2,1..." formula several times while searching for a fix. I've tried several times with that as well, but all I get is that damn #N/A! I'm as positive as I can be that my cell values are all what they should be...

Yes, here are the files...

Perfect.

Many thanks!