Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Excel 2000 Dynamically Set Print Area

I have used the following solution several times successfully to
dynamically set a print area, but am having a problem with a sheet
that has formulas to populate data from another sheet. Is there a way
to adjust this offset to disregard the cells with forulas and set the
print area to just imported/filled data?



Insert=Name=Define
in the name box
Sheet2!Print_Area

in the refers to box:
=OFFSET(SHEET2!$A$1,0,0,COUNTIF(SHEET2!$A:$A,"<") ,COUNTIF(SHEET2!$1:$1,"<"))


TIA
Tom Farrell
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 473
Default Excel 2000 Dynamically Set Print Area

Tom Farrell wrote:
Is there a way
to adjust this offset to disregard the cells with forulas and set the
print area to just imported/filled data?


Try replacing
COUNTIF(Sheet2!$A:$A,"<")
with
COUNTA(Sheet2!$A:$A)-COUNTIF(Sheet2!$A:$A,"")

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
set area in excel not giving me option to set print area? J Littlebear Excel Discussion (Misc queries) 4 April 23rd 23 09:04 PM
Excel 2000 formula to dynamically sum adjacent rows and columns whererows inserted/deleted sga[_2_] Excel Worksheet Functions 3 April 12th 11 01:23 PM
split print area in excel - One Page Print Cyhill Excel Worksheet Functions 3 November 12th 08 09:39 PM
How do you turn off a print area for a page? (no print area) Grunen Excel Discussion (Misc queries) 4 October 8th 05 07:46 PM
How to Dynamically Set Print Area Jerry B Excel Discussion (Misc queries) 2 February 2nd 05 12:21 AM


All times are GMT +1. The time now is 10:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"