Call instance of Excel worksheets

[davidcormie]

Is there any way of calling an instance of a worksheet or range of cells? What I mean is that I have a complicated spreadsheet that is doing successive numerical integration operations based on input data supplied by the user. The output calculated by the spreadsheet varies depending on some boundary conditions to the problem (set in Cell A1 using a flag 1, 2, 3 and 4). For subsequent calculations I want to use the output returned in the range R2257:T2257.

Is there any way writing a formula that returns the values in the range R2257:T2257 assuming A1=1, 2, 3 or 4 for use in my subsequent calcs? My problem is that I need to combine the values from each case. I am aware I could return the values using a macro which sets A1=1, A1=2 etc, but for various reasons I need the calculations to be open rather than being coded. For efficiency and ease of auditing/editing the formulae I would rather not replicate the spreadsheet four times hard-coding the different values of A1 just so that all the values are available as direct results.

Any comments/ideas would be welcome.

David

 

[Andrew Poulsom]

Have you considered using Scenarios?

http://office.microsoft.com/en-us/e...mission-with-excel-scenarios-HA001111987.aspx

 

[davidcormie]

I hadn't, but it seems this runs some what-if analysis and produces an offline report. What I need is similar but giving live/online what-if results calculated automatically and updated automatically if the data input is changed.

 

[Andrew Poulsom]

I don't know what you mean by "offline report". The summary or pivot table is on a worksheet in your workbook.

 

[davidcormie]

Andrew,
Sorry - what I mean is that, in the MS example linked from your post above, if (say) the base unit price is changed, the user needs to run the reporting again to evaluate the what-if analysis. I'm looking for a way of coding the spreadsheet so that the results given in the what-if table are automatically updated if the unit price is changed.

 

[davidcormie]

(Could also be described as dynamically updating the results)