What is wrong with my index match formula?
Workbook4.xlsx
What is wrong with my index match formula?
Workbook4.xlsx
it links to another workbook with loads of tabs that you havent reproduced
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
well a bit of a clue is your formula looks like this
Formula:Please Login or Register to view this content.
so with a bit of find and replace i set it up in another workbook like this
Formula:Please Login or Register to view this content.
Last edited by martindwilson; 01-05-2014 at 12:58 AM.
How can I tell if it links to another workbook? How did you figure that out. I am a beginner at excel. Please explain.
How do I unlink it? And just make the formula work in the workbook you downloaded?
see my last post
How can I manually get rid of links in the current document? Is there a link menu?
Did the Workbook get linked to another workbook because I copied and paste data from another workbook into the current workbook we are working on?
probably!
you can replace that formula with the shorter
Formula:Please Login or Register to view this content.
i really dont get how the formula is linked to another workbook when all I see in the formula bar is:
=IF(NOT(ISERROR(INDEX(Inventory_Sheet!$A:$H,MATCH(Log!$D2,Inventory_Sheet!$D:$D,0),MATCH(Log!G$1,Inventory_Sheet!$A$9:$H$9,0)))),INDEX(Inventory_Sheet!$A:$H,MATCH(Log!$D2,Inventory_Sheet!$D:$D,0),MATCH(Log!G$1,Inventory_Sheet!$A$9:$H$9,0)),"-")
I am just trying to reference to the sheet not the formula you gave me.
I am trying to use index match from the log sheet to grab the information from the inventory sheet into the paid and sold cells.
when you copied to the test workbook it automaticaly created the links
i certainly dont have this file path
C:\Users\acer\Downloads\eWealth\invoices\2013\
'C:\Users\acer\Downloads\eWealth\invoices\2013\[inventory2013.xlsx]September'!$A:$P, ...
inventory2013.xlsx is the linked workbook (always in brackets)
'C:\Users\acer\Downloads\eWealth\invoices\2013\ is the path to that workbook.
Ben Van Johnson
I tried to manually type the code in, instead of copying and pasting:
=IF(NOT(ISERROR(INDEX(Sheet2!$A:$H,MATCH(Sheet1!$D1,Sheet2!$C:$C,0),MATCH(Sheet1!G$1,Sheet1!$A$1:$H$1,0)))),INDEX(Sheet2!$A:$H,MATCH(Sheet1!$D1,Sheet2!$C:$C,0)*MATCH(Sheet1!G$1,Sheet2!$A$1:$H$1,0)),"-")
But I still get in the compatibility report:
Formula contains a link to a closed workbook.
Why cant I type the code in manually?
how do I unlink the document, so I can write the formula without another document interfering?
Here is a screen shot
Screen shot 2014-01-04 at 10.34.57 PM.jpg
any other ideas? much appreciated.
you haven't done anything to the original except copy some stuff
so just junk Workbook4.xlsx and start again
in the meantime you haven't actually said why the original doesn't work ,what goes wrong ?
pick out each match section of the formula as i did in my sample enter those on a new sheet in the original and see what they return
figured it out. Please delete this thread. Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks