Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Formula with Dynamic Address | Excel Worksheet Functions | |||
Dynamic chart generation: Run time Error '1004: Method 'Cells' of object'_Global' fai | Charts and Charting in Excel | |||
Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Charts and Charting in Excel | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Dynamic Chart Generation | Charts and Charting in Excel |