Dynamic range reference

Hi-

I have a sumproduct function that is aimed at a big block of data. Is there a way that I can write the function to where it will always find the last row of the data set and adjust accordingly?

For example if right now the range is A1:G2000 and next month I add 20 rows of data I want the sumproduct ranges to automatically adjust to A1:G2020.

Is this possible?

Assuming every row has data you could use this as the range

A1:INDEX(G:G,COUNTA(G:G))

Thanks Daddylonglegs. I tried that and it didn't seem to work. I will keep messing around with it though and see if I can make it work using that approach.

Anyone else?

I think it might not be working because I have some blank rows above my headers. So A1:G5 are all blank.

Originally Posted by NBVC

How doesn't it work?

What kind of data do you have in column G?

Do any of these work?

A1:INDEX(G:G,Match(9.9999999e+307,G:G))

A1:INDEX(G:G,Match(Rept("z",255),G:G))

Hi NBVC-

Thanks for your response. Here is an example of the formula and how I'm using it.

Take a look at the Formula tab, cell B8.

I'm looking for a way that when I add next months data that the sumproduct functions I'm using will automatically adjust to the new, longer data set.

Originally Posted by NBVC

That shouldn't matter...

My formulas looks for the last entry in column G and makes that the bottom of the range...

Did you try my formulas?

Also, be specific about what's not working... is it just taking the wrong range? Or is it giving a specific error?

If you still have problems, attach a workbook here.

Worked like a charm! Thanks NBVC!

So can you explain how this function works? I like to think I am pretty good with the sumproduct functions and others as well but INDEX is a weakness as I've never learned how to use it.

Originally Posted by NBVC

Try this in B8, copied down and across the table.

Please Login or Register  to view this content.

Thank you sir... always love learning a new trick.

Originally Posted by NBVC

Check here for how the Index/Match functions work:

http://www.contextures.com/xlFunctions03.html