CODEWITHSUNDEEP
excelexcel-formula
atgold18
atgold18

Reputation: 31

Issue when indexing named range with more than one section

I am trying to link data from one workbook to another, I set up a named range with 3 sections in the other workbook and trying to index it. the 3 at end of formula refers to the 3rd section of the range.

=INDEX(test.xlsm'!Named_range,1,1,3)

The formula does work and gets me the correct value however it only seems to work when the other sheet is open. if I close both workbooks and reopen the one I am linking the data to it results in a REF error and can't find the range in the other workbook. When I open the other workbook where I linked it from then it works.

Also if I put the named range as 1 section it works but the issue here is I am trying to do this with a range that has 3 sections.

is it possible to make this work without having both workbooks open?

Upvotes: 0

Views: 73

Answers (1)

Excel Hero
Excel Hero

Reputation: 14764

You need to use a fully qualified file name for the workbook, including the drive and folder. INDEX() can then access the values in the closed workbook...

=INDEX('G:\test\test.xlsm'!MyRange,1,1)

Upvotes: 1

Related Questions