Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-23-2015, 05:14 PM
Granpa49 Granpa49 is offline Index/match accross two workbooks Windows 7 64bit Index/match accross two workbooks Office 2010 32bit
Novice
Index/match accross two workbooks
 
Join Date: Jun 2015
Posts: 1
Granpa49 is on a distinguished road
Default Index/match accross two workbooks

I am having difficulty trying to get index/match to work across two workbooks. I've looked at several forums and this is a common topic. I've not seen a solution, only statements like "There is no reason this won't work". It doesn't seem to work in Excel 2010 (unless I just don't know how to use it). I don't know if it works in other versions of Excel.



Worksheet "DesignValues" in Workbook A contains a table of design values. Workbook B is a project that needs to pull values from "DesignValues".

My formula is: =INDEX(Fb,MATCH(1,INDEX((A4=SYSTEM)*(B4=SPECIE)*(C 4=SIZE)*(D4=GRADE),0),0)))

The formula works if it is in the same worksheet as the data ranges SYSTEM, SPECIE, SIZE, GRADE & Fb. However, when I try to use it in another workbook I get "N/A".

When the formula is located in the same worksheet the range names are in the form of =DesignValues!$A8$A146 Scope = Workbook

When the formula is located in another workbook the range names are in the form of ='[WORKBOOK A.xls]DesignValues'!$A$8:$A$146 Scope = Workbook

I've tried IFERROR & ISERROR and I get a nice 0. I've created a more simpler index/match formula and the problem seems to be that match/index just doesn't work across workbooks.

Any insight to this problem would be appreciated.
Reply With Quote
  #2  
Old 06-25-2015, 10:03 PM
macropod's Avatar
macropod macropod is offline Index/match accross two workbooks Windows 7 64bit Index/match accross two workbooks Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Assuming you've done the referencing correctly, the formula will work BUT you will need the source workbook to be open for the formula to return a result. That's quite possibly why you're getting the "N/A" errors. You can get around that by creating a reference sheet in your target workbook that explicitly references each cell in the source workbook, then point the formula in your target workbook to that new sheet (which can be hidden and protected, if needs be).
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Translate INDEX(MATCH) into VBA grexcelman Excel Programming 1 03-08-2015 05:06 PM
Index match vba grexcelman Excel 0 03-05-2015 10:55 PM
Index Match syntax grexcelman Excel Programming 0 03-05-2015 07:17 AM
UDF multiple IFs INDEX-MATCH grexcelman Excel Programming 0 02-22-2015 04:20 PM
Index/match accross two workbooks Index Match function jackzha Excel 5 12-03-2014 12:43 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:09 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft