MrExcel Publishing
Your One Stop for Excel Tips & Solutions

array formula question

Posted by rainy on July 17, 2001 8:39 AM

i want to sum columns with the following condition:

only the range of data that i need to use changes dynamically.
the user selects the year he wants to view, and i imort the data from a file.
each year will appear in a diffrent location, and i dont know how many rows of data it will have.
how can i change the formula so it will work for that?

Posted by Barrie Davidson on July 17, 2001 9:15 AM

I would recommend using named ranges instead of cell addresses. For example, name range B2:B12 to Range1 and change your formula to:

This way, all you need to do is to name the imported data range (I assume you are doing the import via a macro). You can do the same for E2:E12.

Hope this helps,