Indirect Alternatives

[Brad]

Using XL 2007 - with 2003 compatibility

the below equation works (where Model_Info!$E$25 is truly the only "variable")

=INDIRECT(Model_info!$E$25&"!R"&ROW()&"C"&COLUMN(),FALSE)

The workbook has multiple assumption pages based on dates. The user selects
a date and the workbook accesses the correct sheet.

I have many of these - what options do I have if I don't want to use
indirect and to insure that I'm grabbing the right cell.

 

[Charles Williams]

Assuming your assumption data is in a reasonably contiguous block then

Some alternatives to look at:
- move all the multiple assumption pages onto a single worksheet and use
something like an OFFSET or INDEX to return the appropriate block
- or use the CHOOSE() function (its nonvolatile but max 30 arguments) to
choose between Named Ranges, which can be on different sheets
- or write a VBA UDF to return the correct block as a multi-cell array

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Roger Govier]

see later posting