Excel formula from text

[swtrader]

I will have 1 spreadsheet for actual cash and 1 for projected cash for each month going forward.

(The actual will pull data from a list of actual cash transactions through "yesterday"; data will be pulled from the projection spreadsheet (into the actual cash spreadsheet) for "today" and all future dates).

I am trying to create the reference in each cell of the actual cash spreadsheet. (I've written the logical IF statement that determines whether to use actual cash or projected cash based on the current date. Am missing this piece...) A cell in the "Sept_Actual_ 2009" actual cash spreadsheet has (for example) text that reads: PROJ_2009_9 which is the name of the spdsht containing the projections.

So for each "future" cell in the actual cash SS, I want to use a logical statement that says if the cell is within the future date range, it will populate with corresponding cell value in the PROJ_2009_9 SS. I've tried: =$A$2&"!"&F14 to try to get the cell reference PROJ_2009_9!F14 (on the actual cash spreadsheet) -- but the active cell shows only the text PROJ_2009_9!F14 rather the value of PROJ_2009_9!F14.

 

[swtrader]

=INDIRECT(""&$A$11&"!"&"F17")

 

[SkipVought]

Hi,

I will have 1 spreadsheet for actual cash and 1 for projected cash [red]for each month going forward[/red].

If you 'will have', does that mena that this is a new design...I really hope it is, because it is [red]NOT a good design[/red]. Best and accepted practice would have a design for ONE table (sheet) for actual cash and ONE table (sheet) for projected cash, each having an appropriate REAL DATE column.

Please reconsider and save yourself much unnecessary blood sweat and tears.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[swtrader]

Thanks for the heads-up and advice. In the model I'm building, it's probably a toss-up. Since each column represents a day, the static 2003 spreadsheet provides for only 8.5 months of data. Shortly after each month-end, using my current model, the most recent month (both actual and projected) will be archived to an Archive workbook. I realize that a "month" can be extracted from the active workbook and archived and the next successive month added to the end of the active spreadsheet.

Also, in a 1-sheet-1-month format, the reporting is easier since there are no columns to hide, unhide, etc. After running the 1-month-1-sheet design for a few months, I might decide to do the bit of extra coding to have a 'continuous' cashflow model. For now, in-production is the urgency so I'm trying to get there as quickly as possible.

As always, your advice is 'right-on' and most welcome.