G
Guest
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?
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?