How to dynamically set array size in sumproduct function.

[sumdumgai]

Other than using the 'Indirect' function (or VBA), is there a way to get the row numbers of a named range of rows in an unopened workbook and use those row numbers in a formula in the active workbook? I'm using a 'sumproduct' (Excel 2003) function to count the number of cells in a range of rows in the unopened workbook that match the value in column 'A' of active workbook. Problem is that the range of rows in the unopened workbook changes. The 'sumproduct' works but only if the range of rows (the array) is fixed in size. In the following example, 'H$5002' has been arbitrarily set and is fixed. I want to be able to change that row number as rows are added to the unopened workbook range.

For example:

=SUMPRODUCT(1*('C:\Folder1\[UnopenedWB.xls]Sheet1'!H$7:H$5002=A1888))

Thanks.

[ayman9450]

Hello,

you can use Format as Table tool

[Pepe Le Mokko]

Administrative note
Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
Thanks

[sumdumgai]

Sorry, but how do you do that? Thanks.

[ayman9450]

Select the table and add as much columns and rows as you need, then Home tab, then Format as Table, select any format