Ping D. Peterson.. for follow-up

[Pierre]

Dave,
This one is acting up on me. . .
Regarding setting the print area to adjust to the last column with
visible data.

I'm using a series of formulas to summarize data that appear on
different tabs.
In cell D4, is the place to place the tabs name, and then in cells
5-12 are formulas that appear something like this:

=IF(ISERROR(INDIRECT(D$4&"!$B$1")),"",INDIRECT(D$4&"!$B$1"))
this loads the contents from cell B1 from the tab entered in the
summary sheet (cell D4).
So far, so good.

I've also got conditional formatting that instructs excel to present
blank and empty cells in rows 5-12 if D$4 is blank.

Theoretically with the named ranges, the size of the page will adjust
to the number of tabs I'd like to see data on and that have a value
entered in the various columns in row 4.

(Rows 1-3 are just banner rows, with project data.)

So far, the print area still goes all the way out 52 columns(where
there are formulas but no data as row 4 is empty out there. . .)

Could you have a 2nd look at the named range references(below), and
recommend any adjustments?

Thanks for any and all. (Hope I didn't break any rules by posting a
mini-novel.)
Pierre



Lastcol
=LOOKUP(2,1/(Summary!$1:$1<>""),COLUMN(Summary!$1:$1))
lastrow
=LOOKUP(2,1/(Summary!$C$1:$C$2000<>""),ROW(Summary!$C$1:$C$2000))
summaryprintarea
=OFFSET(Summary!$A$1,0,0,lastrow,lastcol)

 

[Dave Peterson]

First, watch out for your formula:

=IF(ISERROR(INDIRECT(D$4&"!$B$1")),"",INDIRECT(D$4&"!$B$1"))

if the value in D4 is a name that requires surrounding apostrophes, you may be
in for some unforeseen errors.

=IF(ISERROR(INDIRECT("'"&D$4&"'!$B$1")),"",INDIRECT("'"&D$4&"'!$B$1"))

Second, those sheet names are very important--you want those names to be local
to the sheet--not global to the entire workbook.

Summary!Lastcol
=LOOKUP(2,1/(Summary!$1:$1<>""),COLUMN(Summary!$1:$1))
Summary!lastrow
=LOOKUP(2,1/(Summary!$C$1:$C$2000<>""),ROW(Summary!$C$1:$C$2000))
summary!print_area
=OFFSET(Summary!$A$1,0,0,lastrow,lastcol)

And Print_Area is a name that excel uses. You can't change it (by dropping the
underscore). If you do, then everything will break.

 

[Pierre]

First, watch out for your formula:

=IF(ISERROR(INDIRECT(D$4&"!$B$1")),"",INDIRECT(D$4&"!$B$1"))

if the value in D4 is a name that requires surrounding apostrophes, you may be
in for some unforeseen errors.

=IF(ISERROR(INDIRECT("'"&D$4&"'!$B$1")),"",INDIRECT("'"&D$4&"'!$B$1"))

Second, those sheet names are very important--you want those names to be local
to the sheet--not global to the entire workbook.


And Print_Area is a name that excel uses.  You can't change it (by dropping the
underscore).  If you do, then everything will break.

Dave, thank you for all the help. I'll keep working with it, and let
you know how it goes.

Pierre

 

[Pierre]

Good Morning Dave,

Stil not behaving as intended. The good news is, we can now enter in
row 4 and assign sheet-tab names that include spaces. However, it
insists on setting the print range to include cells with formulas, but
no data extracted.

Might you have a plan B?
Thanks again.
Pierre

 

[Dave Peterson]

I'd guess your formulas (in the cells) don't return zero length strings ="".
I'd change the formulas if that's true.

If it's not true, I don't have a guess.

You could modify your formulas in the names:

And just a reminder...
when you have sheet names that need surrounding apostrophes, the worksheet level
names would look like:
'Summary 1!LastCol

I'd still fix the offending formulas in the cells, though.

 

[Pierre]

I'd guess your formulas (in the cells) don't return zero length strings ="".
I'd change the formulas if that's true.

If it's not true, I don't have a guess.

You could modify your formulas in the names:


And just a reminder...
when you have sheet names that need surrounding apostrophes, the worksheetlevel
names would look like:
'Summary 1!LastCol

I'd still fix the offending formulas in the cells, though.

Will keep hammering away at it.

There's always a method.
Pierre

 

[Dave Peterson]

You may want to post more details on how it's failing, too.

ps. These Excel newsgroups are overwhelmingly used by people who top post. You
may want to join the club <bg>.

 

[Pierre]

Dave,
The sheet is now working fine! Changed the formulas in the cells to
the likes of
=IF(C4="","",(INDIRECT("'"&C$4&"'!$t$20"))). . .instead of the ISERROR
trigger; to return a zerro length string which might have been the
culprit.
Also changed lastrow range to:
=LOOKUP(1,1/(Summary!$B$1:$B$2000<>""),ROW(Summary!$B$1:$B$2000))

Seems that having $C$1:$C$2000 demanded at least 2 tabs be summarized
(in addition to the row headings in column A.

Where would you like that pizza sent?

Pierre

 

[Dave Peterson]

I'd keep an eye out on that LastRow name.

And glad you got it working!

hmmm. Pizza.