Combining two if statements and printing

farmertml

Board Regular
Joined
Jun 16, 2005
Messages
62
Hi guys,

I have a spreadsheet which when full will have about 20 landscape pages of fomuli ~1000rows.

I have forumli running through all the 1000 rows and they are all just printing 0's and errors (#value).

I have two IF statements which will remove them individually but I would like to combine the two if it is possible.

Formula 1
=IF(ISERROR($K71),"",($K71))

Formula 2
=IF('Calculation sheet'!K71=0,"",'Calculation sheet'!K71)

I presume there is just an & sign or something simple but I can't work it out.

Also, if this hides all the 0's and errors will it reduce the total pages to print to only where there is actuall data ? (Currently there are only 2 pages of data inputted and it wants to print 9 pages of 0's / #value errors.

Regards
Paul
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
I can possibly suggest something else.

In page set-up go to the 'Sheet' tab.

Change the 'Cell errors as:' drop down to <blank>.

To hide the zero's on the menu bar go Tools > Options, click the 'View' tab and uncheck 'Zero Values'

Hope this helps...
 
Upvote 0
Hi there thanks for the info!

As for the zeros, the sheet has alot of other 0 cell Values which I need to remain but the error one is helpful for another page!

Ideally I would really like to be able to merge two IF statements.
 
Upvote 0
OK I have managed to resolve the problem but I still need to try and make it so if there is no data (blank cells) on the other 19+ pages it will not print them.

Any ideas?
 
Upvote 0
Do you have a name in your worksheet called Print_Area? Have you set the Print_Area. We can make this dynamic. What columns does your sheet occupy? Is there any single column that can be used to identify the last row with data in it?
 
Upvote 0
OK..

The print area is on one sheet only (Final Results).

Column's A -> J

Rows B, C and D will be where data is inputted and it would need to dynamically expand based on one of any of these columns.
 
Upvote 0
Sorry one more question:

What is in columns B, C, D. Numeric or text values? Or both?
 
Upvote 0
Here's a macro to set the print area. Add this to ThisWorkbook.


<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforePrint(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br><br><SPAN style="color:#00007F">Dim</SPAN> rPrintArea <SPAN style="color:#00007F">As</SPAN> Range, lRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br>lRow = Sheets("Final Results").UsedRange. _<br>    Find("*", LookIn:=xlValues, searchorder:=xlByRows, searchdirection:=xlPrevious).Row<br><br><SPAN style="color:#00007F">With</SPAN> Sheets("Final Results")<br>    <SPAN style="color:#00007F">Set</SPAN> rPrintArea = .Range("A1:J" & lRow)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>ThisWorkbook.Names.Add Name:="Print_Area", RefersTo:=rPrintArea<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Great that works perfectly, thank you very much Jon I really appreciate your time!!
By the way, awesome avatar picture :)

Regards,
Paul
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top