Q: Dynamic Range in Excel using INDIRECT function I am trying to create a formula in Excel that calculates probability based on the day of the month.  On the second day of the month, it would add the calculated distributions for the current month based on the number of days left in the month.  I was able to successfully do this on the worksheet that contains the distribution values with the following formula:
=SUM(B2:INDIRECT(CONCATENATE("B",R1)))

R1 is the cell that contains the value for the days left in the month, and column B contains the distribution values.  The problem is that I need this calculation to occur on a different worksheet, and when I try to translate the formula to do this I get an error.  This is the formula that I created on a different page:
=SUM('Alternate Distribution'!B2:INDIRECT(CONCATENATE('Alternate Distribution'!"B",'Alternate Distribution'!R1)))

After I get the message stating that there is an error in my formula, it goes to the "B" portion of the formula.  I am guessing it is an apostrophe issue, but the various things I have tried do not seem to work.
Thanks in advance!

A: Try this:
=SUM(INDIRECT("'Alternate Distribution'!B2:B"&'Alternate Distribution'!R1))

Or with a non Volatile function:
=SUM('Alternate Distribution'!B2:INDEX(Alternate Distribution'!B:B,'Alternate Distribution'!R1))

