Set Print Area using multiple named ranges that vary dynamically

[excelguru88]

Hey guys -

I need to be able to set print area using named ranges that vary dynamically.

For example, I have 4 named ranges and they are:

Range1
Range2
Range3
Range4


Now in one scenario, I want the PrintArea to be set to = Range1,Range3
In another scenario, I want the PrintArea to be set to = Range2,Range3
...and so on

Now, I can manually update the print area to the two scenarios above, but how can I do that using a formula or VBA?

Any thoughts would be helpful.

 

[pgc01]

Hi

You don't say how you choose which scenario and so I'll assume that you write the number of the scenario in A1. For A1=1 you print scenario1, etc.

I would define names for the 2 scenarios, for ex. Scenario1=Sheet1!Range1,Sheet1!Range2 and then use for the Print_Area

=CHOOSE(Sheet1!$A$1,Sheet1!Scenario1,Sheet1!Scenario2)

but you can also use directly your named ranges, like

=CHOOSE(Sheet1!$A$1,(Sheet1!Range1,Sheet1!Range2),(Sheet1!Range3,Sheet1!Range4))

 

[excelguru88]

Thanks PGC - I'll try this out.

 

[excelguru88]

thanks PGC - I'll try this out!

 

[excelguru88]

Magical! it works - much appreciated!

 

[pgc01]

I'm glad it helped. Cheers!