dynamic print area based on cell value

[ritpkg]

I have researched the posts and found only a partial solution:

I want to use a cell value to dynamically determine the print area,
I found the following:
define name Print_area and use the formula:
=INDIRECT(IF(DCIR!$D$114<3,"$A$1:$I$179",IF(DCIR!$D$114<5,"$A$1:$I$241",IF(DCIR!$D$114<7,"$A$1:$I$303",
IF(DCIR!$D$114<9,"A1:I365", IF(DCIR!$D$114<11,"A1:I427",
IF(DCIR!$D$114<13,"A1:I489", IF(DCIR!$D$114<15,"A1:I551",))))))))

to identify the range.

The issue is that I need to run this string to up to 3-47, odd numbers,
but it it doesn't want to go beyond 15 without puking. Am I missing
something? Is there a better way?

Thanks
M

 

[Harlan Grove]

(e-mail address removed) wrote...

I have researched the posts and found only a partial solution:

I want to use a cell value to dynamically determine the print area,
I found the following:
define name Print_area and use the formula:
=INDIRECT(IF(DCIR!$D$114<3,"$A$1:$I$179",IF(DCIR!$D$114<5,"$A$1:$I$241",
IF(DCIR!$D$114<7,"$A$1:$I$303",IF(DCIR!$D$114<9,"A1:I365",
IF(DCIR!$D$114<11,"A1:I427",IF(DCIR!$D$114<13,"A1:I489",
IF(DCIR!$D$114<15,"A1:I551",))))))))

to identify the range.

The issue is that I need to run this string to up to 3-47, odd numbers,
but it it doesn't want to go beyond 15 without puking. Am I missing
something? Is there a better way?

The formula above isn't ideal. It should have been
=INDIRECT(LOOKUP(...)). Also, better to put the values and the
corresponding ranges into a table in some worksheet because Excel has a
limit on the length of formulas used to define names. Something like a
range named PAT looking like

-1E12 A1:I179
_3 A1:I241
_5 A1:I303
_7 A1:I365
_9 A1:I427
11 A1:I489
13 A1:I551
:

Then define Print_Area as

=INDIRECT(LOOKUP(DCIR!$D$114,PAT))

However, your ranges have a regular pattern, increasing by 62 rows each
time DCIR!$D$114 passes another odd integer value, so you could define
Print_Area as

=INDEX($A:$I,179+MAX(0,INT((DCIR!$D$114-1)/2)*62,9)

and not bother with the table.

 

[Dave Peterson]

Maybe you can use some arithmetic.

It looks like this may be equivalent:

=INDIRECT("a1:I"&117+62*(CEILING(DCIR!$A$114,2)/2))

And just a reminder...

You'll want to define Print_Area as a worksheet level name.

Like: 'Sheet 99'!Print_Area