• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Match function with 2 criteria between 2 workbooks

M

melaniez

New Member
Hello


I'm trying to link 2 workbooks together so that I only have to update one (source workbook) and the other (linked workbook) updates automatically. Both workbooks have multiple spreadsheets and I am trying to link the two master sheets from each.


Basically I have a list of people with different assignments over a period of time. When I update the start/end dates of their assignment(s) on the source workbook I want the linked workbook to retrieve the new dates.

I figured I need to set up an index/match formula with 2 criterias (person's name and assignment)but I haven't managed to make it work.


I've tried different formulas:


The basic:


=Index(Source Workbook Date Range, Match (Linked Workbook Name&Assignement, Source Workbook NameRange&AssignmentRange, 0))

this returns a #VALUE error


then I tried something like this:

=INDEX(Source Workbook Date Range, MATCH(1, (Source Workbook Name Range = linked workbook Name) * (Source Workbook Assignment Range = linked workbook Assignment), 0))

but it is returning and #N/A, even though I've copied and pasted the criteria to make sure spelling was correct.


finally this one, but this time I get a #VALUE error. I'm thinking this might be because the value is a date and not a number.

=INDEX(Source Workbook Date Range, MATCH(1, INDEX((Source Workbook Name Range = linked workbook Name) * (Source Workbook Assignment Range = linked workbook Assignment),0), 0))


A couple of additional notes:

on the source workbook, the assignment cell value is already the result of an index/match formula, so I'm wondering if using a cell that contains a formula as a criteria might be a problem.

on the linked workbook, the assignment is a fix cell (it's not part of a table), so that part of the formula would actually be something like this (Source Workbook $B$2:$B$16 = linked workbook $G$2)


I am not sure what the "1" at the beginning of the Match brackets stands for in both these formulas, maybe that would explain why it doesn't work.


Finally, I did manage to make the first formula work when testing the Source workbook and a blank worksheet, but the dates returned where incorrect ( 20 oct 2007 when it should have been 2011)


So I'm hoping somebody can point out the potential mistakes i'm making, or if there is a better formula to use in this particular case.

thanks in advance !
 
Melanie,


1) Using the result of an index/match as a criteria in another index/match is okay.

2) I *think* that your 2nd and 3rd formulas can't work because of the '=' sign within the formula. I don't think it works that way.

3) On the 1st formula, are you using concatenated Name & Assignment values in ONE cell or "concatenating the columns" as the lookup location (Excel can't handle that)? Like "Columns A&B"...


If you're trying to concatenate the columns, it won't work. What you can do is -- in another column -- concatenate the two name and assignment cells for each row, then point your match to that column. Your match criteria, however, CAN be concatenated cells (and should be cells not columns -- dbl check that too).


Hope this works.
 
Yes, i think you pointed out my mistake, I was concatenating the columns together. I'll try creating a hidden column with the concatenated info from both columns and see if that work. thanks!
 
hi Jeremy


I concatenated the Name&Assignment into one column, and my formula now looks like this:


=INDEX(Source Date range, MATCH(Linked Name&Assignement,Source Concatenated Column,0))

but now I get a #REF error....
 
ok, scratch that, there was a mistake in my formula.

the cell now returns 0, when it should be returning a date...
 
ok, so as a test, I've copied and Pasted the value only of some of my source info into a new blank worksheet, and I've used the third formula in my linked document, and it works...

so it probably has something to do with the fact my source cells contain formulas....
 
Melaniez


Firstly, Welcome to the Chandoo.org Forums


Have a read of the post and comments at Daily Dose of Excel:

http://www.dailydoseofexcel.com/archives/2004/12/01/indirect-and-closed-workbooks/
 
Hi Hui


Thanks for your input. I've gone over the post, but it seems a bit advanced for my own capabilities. Plus I'm not too worried about making sure the workbook gets updated. I usually have to work on both at the same time, so they're usually both open anyway.

My problem for now is to actually successfully link the too...

Also Both are actually quite large (10-15 worksheet each)and contain a lot of formulas (I've created gant charts with cells changing colors depending on dates for each individual/assignment. So I also want to try and avoid anything that would make either workbook slower...

but thanks for your help. I'll keep looking at forums and online help to figure it out !
 
Hi Melaniez ,


Sorry to join the conversation at this late stage , but is it possible you can just copy + paste any two formulae from your two workbooks , which are linked to one another, one from your master workbook , and the other from your linked workbook ? I am sure this forum can help you resolve your problem.


Just to give you an example , I have two workbooks open ( Book1 and Temp ) ; in Temp , I have the following formula :


=INT([Book1]Sheet1!$E$82/15)


When I enter 75 in cell E82 of Sheet1 ( in Book 1) , I get 5 in this linked cell in Temp.


Narayan
 
Hi Narayank991

Thank you for your suggestion. I've tried and it doesn't work. It's returning blank or #N/A. Plus, because I'm using a set and varying formulas in the source page, I would have to copy and paste every single cell into the linked workbook for the returned value to be accurate...

I'll keep looking :)
 
Hi Melaniez

I haven't had time to read all above but if this is the latest formula returning the error one thing wrong and one thing to check:


=INDEX(Source Date range, MATCH(Linked Name&Assignement,Source Concatenated Column,0))


1) you have no Index column specified


=INDEX(Source Date range, MATCH(Linked Name&Assignement,Source Concatenated Column,0),?)

The match is the eow part of the index but you need the column defined for Source Data range that you want to return.


2) Check Source C.... Column is just a single column array it can't be more than one - sounds like you have this right.


Hope it helps.

John


Source Concatenated Column
 
So, I'm making progress. Using the last formula (=INDEX(Source Date range, MATCH(Linked Name&Assignement,Source Concatenated Column,0))), I do get a date returned (not sure why it's working today but didn't yesterday...), but the value is wrong. Yet when I turn the cell format to a general value, it's the same numerical number on both workbook, so I'm not sure why the date value would be different....


@ John

thanks for your suggestion, although I don't think I need to specify an index column since the Source Date Range is a single column. I did try adding the column number anyway, but it returned a #REF message...
 
Problem solved! my workbooks where in two different calculation systems (1900 and 1904).

So to conclude, this is the formula that works:

=INDEX(Source Date range, MATCH(Linked Name&Assignement,Source Concatenated Column,0))

Now I just need to make sure that using it over the 15 tabs of my workbook won't make it crazily slow...
 
Melaniez I can hear the groan from here on discovering that one. That problem is the last thing anyone thinks off but seems so obvious afterwards. I have this problem every now and then between timesheets and a payroll calculation sheet that I load into. I might see if it's possible to write a little macro checking the date settings of the source and destination spreadsheet and giving a msgbox alert if they are different.
 
You must log in or register to reply here.
Back
Top