Trouble Referencing Data in Separate Spreadsheet

Eszrai

New Member
Joined
Sep 19, 2016
Messages
3
Hi, I’ve been working with the following array formula for about 3 months now and only recently has it begun to present some problems. I understand excel fairly well, but this formula is a bit complex for me. I was able to customize this formula from one I found online. I do not understand VBA, and am hoping someone may be able to correct this or point me in the right direction.

{=IF(ISERROR(INDEX('[Equipment.xlsx]Sheet1’!$A$2:$R$1000,SMALL(IF('[Equipment.xlsx]Sheet1’!$A$2:$R$1000=$P$6,ROW('[Equipment.xlsx]Sheet1’!$A$2:$R$1000)),ROW(1:1))-1,3)),"",INDEX('[Equipment.xlsx]Sheet1’!$A$2:$R$1000,SMALL(IF('[Equipment.xlsx]Sheet1’!$A$2:$R$1000=$P$6,ROW('[Equipment.xlsx]Sheet1’!$A$2:$R$1000)),ROW(1:1)*2-1)-1,14))}

It is designed to pull data from a table referenced in another file (Equipment.xlsx) per business. The ROW reference moves down the range and pulls the next item matching the reference point [ROW(1:1), ROW(2:2), etc.]. The unique (usually) reference point is the business phone number as a number was needed as the reference field for this formula to work. Some now contain a bit of text to differentiate. The [*2-1] near the end was incorporated after the fact due to some items being duplicated.

Lately however, instead of pulling info like:

Equipment 1 $100 123ABC
Equipment 2 $150 123JKL
Etc.

It has been giving:

Equipment 1 $100 123ABC
#NUM #NUM #NUM

The table within Equipment has grown, but is still within the $A$2:$R$1000 range. I have since been told it will continue to grow annually. I can’t make it an Excel Table (Ctrl+T) due to the main person updating this sheet. Out of about 50 or so worksheet tabs in the presentation spreadsheet, some are working fine. However closer to the end, they are all giving the #NUM error above. Any assistance at all would be very much appreciated!
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
Was the reference file open before (when it was working)?

Some excel functions will only work on open workbooks, so try opening the source and see if that solves the problem?
 

Eszrai

New Member
Joined
Sep 19, 2016
Messages
3
Both actually. I've had both documents open and saved to pull the most up to date info as well as both saved and Equipment.xlsx closed. Neither seems to be different as of yet.
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
OK then are you sure there IS a match? If there IS a (visual) match, are the 2 cells really identical? Check for leading or trailing spaces, typos, spelling, etc
 

Eszrai

New Member
Joined
Sep 19, 2016
Messages
3
Hi there, yes I am sure there is a match as it is pulling partial data. If there isn't a match (extra spacing, typo, etc) the formulas are left blank with nothing. I started copy & pasting from the data document to avoid this exact scenario. Sorry, this doesn't appear to be a simple fix :(
 

Watch MrExcel Video

Forum statistics

Threads
1,122,939
Messages
5,598,967
Members
414,269
Latest member
FJXMTT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top