Ping D. Peterson.. for follow-up

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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)
.

Relevant Pages

  • Re: Ping D. Peterson.. for follow-up
    ... I'm using a series of formulas to summarize data that appear on ... In cell D4, is the place to place the tabs name, and then in cells ... the size of the page will adjust ...
    (microsoft.public.excel.worksheet.functions)
  • RE: How do i set tabs within a cell in Excell
    ... The closest you're going to get to tabs is to select the cell and indent the ... the delimiter then you can adjust the column widths to make your sheet look ...
    (microsoft.public.excel.misc)
  • Re: countif multiple conditions
    ... if your sheet names or cell locations are different, ... location they don't adjust. ... John word ... Ann excel ...
    (microsoft.public.excel.worksheet.functions)
  • RE: Excel project, need help
    ... LotsOfTabs.xls is the name of the "different spreadsheet ... When you are talking about a tab, you really mean a worksheet ... The part#s start in cell C2 and go down to cell C10 ... Sheet1 should be changed to the name of the first of the 30 tabs of data ...
    (microsoft.public.excel.misc)
  • Re: Help with VBA for document formatting.
    ... character in the cell to do its thing. ... Dim sTabs As String ...     With rCell ... (the number of tabs). ...
    (microsoft.public.mac.office.word)