Dynamic Name Range to a closed workbook

[pinkpeace7]

Hi

I need to use sumproduct in getting certain data I need from a closed workbook and the range changes depending on what is the last row defined in my Closed Workbook sheet in the example I attached.
I know indirect doesn't work with a closed workbook, and I try certain other methods which doesn't give me what I need as well...

Is there any ways I can make this, 'Closed workbook'!$A$1:$A$9, dynamic and still work on a closed workbook?

[JohnTopley]

There are no formulas in your sample file and column A is text (to columns?) so I am not sure what is required.

[pinkpeace7]

I'm so sorry! I attached the wrong file.

This is the file.

I like to make all the name range - 'Closed Workbook'!$A$3:$A$11, 'Closed Workbook'!$B$3:$B$11 and 'Closed Workbook'!$C$3:$C$11 dynamic.

Using Mid and Cell together with Match("Last Row", $A:$A, 0) I would be able to obtain the path of my closed workbook but using indirect then would not work with the workbook closed.

Thank you!

[JohnTopley]

This is an example of a dynamic named range in column I on sheet "Parameters"

=OFFSET(Parameters!$I$2,0,0,COUNTA(Parameters!$I:$I)-1,1)

Is this what you need?

[pinkpeace7]

Would you have the excel file with the sheet "Parameters" ?

[JohnTopley]

See attached .....

[pinkpeace7]

Thanks! But I read that Offset function doesn't work referencing to a closed workbook.
Would there be any other function that will work when my main data workbook is closed?

[JohnTopley]

I am not aware of any other method. Is the Closed Workbook range "infinite"? If not, why not fix the range at a sensible maximum? Why does it have to be dynamic?