How to return multiple column array without using Offset function?

Hi Everybody

I have a named range referencing 26 columns and 'n' number of rows of data from a worksheet in a workbook. This workbook is closed and must remain closed whilst data from this workbook is being referenced.

I need to fetch a subset of the above data (data between columns 3 and 14)from this workbook - whilst it remains closed - in to another workbook.

I am using the combination of 'Sumproduct()' and 'Index()' functions to realize this. Whilst I am able to return any one (1) column using the combination of functions, I don't know how to do so to for multiple columns.

I believe that I cannot use the 'Offset()' function since it does not work across closed workbooks.

Any suggestions!

Thanks


Best regards



Deepak Agarwal

The following should reference the desired range...

INDEX(NamedRange,1,3):INDEX(NamedRange,n,14)

Hope this helps!

Domenic

What you suggested didn't work for me.

Maybe I didn't explain myself as well as I should have.

Assuming that the named range is given the name of "test" referring to data in the range "A1:E100" on Sheet1 in a workbook which is now closed.

The idea is to be able to obtain the sum total of the data contained in a subset of the above named range "test" say in the range "B1:D100" viz for all the rows but in the columns 2, 3 and 4 in another workbook without opening the workbook that contains the data the named range is referring to.

Had this named range "test" been in the current workbook, I could have used the formula like so :-

=sum(offset(index(test,,1),0,1,,3))

This would have returned the sum total of the data in all the rows in the range consisting of 3 columns begining from the column 1 column to the right of the 1st column in the named range "test".

However, since my named range "test" in another workbook which is closed, the offset function returns an error and hence, the overall result is an error.

But trying to do the same with your suggestion using either sum or sumproduct doesn't seem to work for me - or perhaps I am not doing what you thought I should be doing.

Could you please elaborate.


Thanks and best regards


Deepak Agarwal

Place your formula in the source workbook, then in your other workbook retrieve it using a simple...

='[Workbook.xls]Sheet1'!$A$2

Change the reference accordingly.

Hope this helps!

Hi

Do you need to return all the raw data from the range, or do you want to return the results of functions on that range. So if your subset was B1:D50, do you want to bring all that data into say the range A1:C50???

rylo

Domenic
This is not what I need. I cannot use the formula in my source workbook - it is a data file and gets updated automatically daily.


Rylo
No, I don't have to bring in the data in the range at all. I only need to get result of the function, in this case, the sum total of the data contained in the smaller subset of the data contained in the named range.

The subset as I have said in my example is in the range "B1:D100" whilst the full named range was "A1:E100"


Hope this clarifies to some extent.


Best regards


Deepak Agarwal

Hi

Would it be possible to bring in the entire range to a temp area in the current workbook, get the function result, then delete this temp area? Can bring in the data without opening the workbook.....


rylo

Rylo

That was exactly what I am now thinking that I will have to do since offset doesn't work with closed workbooks and I don't think that there is any other way but to get the data in to this workbook without opening the source workbook, which I can certainly do.

But that's ok! Genuinely appreciate your input though!

It might be something that Microsoft might want to consider in their next version of Excel to make Offset work across closed workbooks.


Best regards


Deepak