Named range #VALUE error when displaying in another workbook

Question

Hello Community

I want to read a named range in one workbook and display the values in another workbook. Here goes:

This is workbook src.xslx with a named range ‘five’:

This is workbook dst.xlsx which reads the named range to A1-A5

Works ok.

... but when the destination rows are not the same as the source rows …

(#VÄRDEFEL = #VALUE)

 

However, placing the range in another column on row 1 works:

How do I go about to place the named range on any location in the destination workbook?

Regards

Peter

Answer 1

Either use an Array-Entered (entered using Ctrl-Shift-Enter) link of the same size - select five consecutive cells starting anywhere in a column  and use:

=src.xlsx!five

or use an INDEX function:

=INDEX(src.xlsx!five,1)

or a copy-able function

=INDEX(src.xlsx!five,ROW(A1))

copied down for 5 cells.

Answer 2

Bernie,

Thanks, preselecting the desired area and entering the named area as an array worked.

Regards

Peter