Changing a formula dynamically.

[Guest]

Hi Folks,

Hoping someone can give me some suggestions. I have a workbook, one
worksheet, called 'Summary', is a summary of what is found on the other
sheet, called 'Report1'. Worksheet 'Summary' contains about 50 'sumproduct'
formulas in the format....

=SUMPRODUCT((Report1!$A$2:$A$2572=Sheet1!$B11)*(Report1!$B$2:$B$2572=Sheet1!M$4),Report1!$L$2:$L$2572)

Each week this workbook is changed. I paste the new data into the sheet
named Report1 and the formulas on worksheet 'Summary' recalculate on the new
data. Problem is I then have to go and adjust the 50 sumproduct formulas as
the last row of data is different each week. Is there a way to make the
sumproduct formula adjust automatically to the last row of data so it would
look something like...

=SUMPRODUCT((Report1!$A$2:$A$LAST_ROW=Sheet1!$B11)*(Report1!$B$2:$B$LAST_ROW=Sheet1!M$4),Report1!$L$2:$L$LAST_ROW)

Thanks in advance for any help.

John

 

[Guest]

Make your ranges dynamic:

See here ...

http://www.contextures.com/xlNames01.html#Dynamic

 

[Guest]

Is there any chance of creating your summary as a pivot table, rather than a
group of 50 sumproducts?

As an alternative to dynamic ranges - which work but can slow down a large
sheet, you can do a search & replace - search for the 'old' last row and
replace with the 'new' last row