Dynamic named range in sumproduct formula

[dkuyper]

Hi All,

I am putting together a workbook that is retrieving data from a source system using excel4apps into one tab. On a second tab I am using a sumproduct formula to reference some of the retrieved data (I am using sumproduct as I am summing on column and row but, would be happy to change if there is a better way). I'm current using cell references in the sumproduct formula but, the retrieved data can vary in the number of columns and rows each time it is refreshed. Is there anything I can do so that the user doesn't have to change the cell references in the sumproduct formula each time the data is refreshed?

Hope this makes sense.

Any help would be greatly appreciated.

Thanks,

Dean

 

[Joe4]

You can use named ranges in SUMPRODUCT formula.
If there is logic behind determining how to identify the range, you can use VBA code to build those named ranges.

 

[dkuyper]

Hi Joe,
Thanks for the response. The problem I have is that the range changes each time I refresh the data. The no. of columns is consistent but the number of rows changes each time.

 

[Joe4]

The problem I have is that the range changes each time I refresh the data. The no. of columns is consistent but the number of rows changes each time.

That is what I meant when I asked if there is logic involved. We may be able to have VBA re-size the change, on-the-fly, if we know how this works.
We would need to know the following things:
- In your data, can you tell us a column letter that will ALWAYS have data? We can use this to determine the end of our data.
- Can you post the exact Sumproduct formula, for an example, so we can see which ranges (columns) are needed?
- When exactly would you like this code to run? Would you run it manually, or would you like Excel to try to do it automatically?