Dynamic Formula Generation
 

I am trying to dynamically change a formula based on a cell on a worksheet.

I have 2 different workbooks I am working with. One is a data workbook and
has a worksheet for each month (January through December).

The second workbook is used for reporting. The reporting is only going back
12 months. The reporting is set up in a table that has the last 12 months as
column headings. In this workbook, I use array formulas to pull the data that
I need from the appropriate worksheet in the data workbook (for the January
report, I reference the January tab in the data workbook, etc...).

I am trying to automate these reports. I want to have a cell where the user
enters the current month of reporting. When the user does that, I need the
formulas to change.

So, lets assume this month is August. When I open the reports, the last
column in the report is July. In my new model the user would enter the month
"August" in a cell and the old formulas that referenced the July worksheet in
the data workbook would now automatically change to August.

Does anyone know how I can accomplish this?

Dynamic Formula Generation
 

You could use Indirect and replace the sheet name with a reference to a cell.
For example, where cell A1 is "August"

=INDIRECT("[Book1]"&A1&"!C5")


"dbaucom" wrote:

I am trying to dynamically change a formula based on a cell on a worksheet.

I have 2 different workbooks I am working with. One is a data workbook and
has a worksheet for each month (January through December).

The second workbook is used for reporting. The reporting is only going back
12 months. The reporting is set up in a table that has the last 12 months as
column headings. In this workbook, I use array formulas to pull the data that
I need from the appropriate worksheet in the data workbook (for the January
report, I reference the January tab in the data workbook, etc...).

I am trying to automate these reports. I want to have a cell where the user
enters the current month of reporting. When the user does that, I need the
formulas to change.

So, lets assume this month is August. When I open the reports, the last
column in the report is July. In my new model the user would enter the month
"August" in a cell and the old formulas that referenced the July worksheet in
the data workbook would now automatically change to August.

Does anyone know how I can accomplish this?