Testing Alternative Input Worksheet

[Gregg Wolin]

I have a model where all the inputs (assumptions) are on a single page (all input cells are named ranges). The calculations and reports are on different worksheets that all make reference to the ranges named on the input page (no inputs on those sheets).

I'm experimenting with alternative layouts of my input page (each in a separate workbook that has only the input sheet). My thought was that if I name all the ranges in the alternative layout workbook to match those in the current version of the input sheet within the model, that I could delete the input page of the model, then copy the alternative input sheet into the model.

Would that work? Is there another / better way do to it?

 

[NARAYANK991]

Hi ,

It would have been more helpful if you could have named all the workbooks and worksheets.

I will assume that your model workbook is named ModelBook.

Let us assume that this workbook has 3 worksheets named InputsPage , Calculations and Reports.

Let us now assume that you have another workbook named AlternativeLayout1. This also has a worksheet named InputsPage which has identical named ranges to the named ranges in the tab named InputsPage in the workbook named ModelBook.

Now you say :

... I could delete the input page of the model

The moment you do this , all the formulae in the worksheets named Calculations and Reports would turn into #REF! error formulae.

Narayan

 

[Gregg Wolin]

I don't understand. What if I had named the workbooks and worksheets as you suggested? Would it work then? Is there an alternative method to accomplish this?

 

[Chihiro]

Would it work then?

No, I believe what Narayank meant is that it'll be much easier for us to understand how you've set up your workbook and follow your logic, had you named each and referred to it with name in your description.

I'd recommend that you upload sample workbook to demonstrate your current set up and your desired outcome.

 

[Peter Bartholomew]

If you are planning to move a worksheet between workbooks, I would recommend limiting yourself to moving Names scoped to the worksheet

Name: Sheet1!localName
refers to: Sheet1!Reference

in order to avoid generating unwanted links between workbooks. Once the sheet is in place (the one-sheet workbook will disappear once its sheet is moved) then is the time to reassign workbook names to refer to the local sheet names (like subroutines or objects have a local variables that are set to use and return values to the calling procedure). Once all links are reassigned, delete the names that refer to the old worksheet and then delete the sheet.

An alternative strategy at the experimentation stage might be to use CHOOSE. For example 'globalNameA' and 'globalNameB' that refer to:

= CHOOSE( method, Sheet1!localNameA, Sheet2!localNameA, ... )
= CHOOSE( method, Sheet1!localNameB, Sheet2!localNameB, ... )

It is only too easy to create a mess!