Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default Show zero values only when data is entered in other fields

I am currently working on a spreadsheet that contains min. information but
will expand over time. I have formatted a column for number and would like
the zero value to show only in fields where there is other information
showing (all the rest of the rows should show no zeros. I also found that
because I applied conditional formatting to one of the columns from row
2-2000, when I want to print the whole spreadsheet rows 1-2000 are selected
to print (pages 1-56) not just the page(s) with data on them. How do I get it
to auto select only the rows with data.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,696
Default Show zero values only when data is entered in other fields

if the column in question is A, and if, say, column B will have data:

In cell A2:

=if(B2 = "","",0)

and paste down.

Unfortunately, don't have the answer to the second part.

"gevew" wrote:

I am currently working on a spreadsheet that contains min. information but
will expand over time. I have formatted a column for number and would like
the zero value to show only in fields where there is other information
showing (all the rest of the rows should show no zeros. I also found that
because I applied conditional formatting to one of the columns from row
2-2000, when I want to print the whole spreadsheet rows 1-2000 are selected
to print (pages 1-56) not just the page(s) with data on them. How do I get it
to auto select only the rows with data.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,696
Default Show zero values only when data is entered in other fields

Or better yet,

if(counta(B2:IV2)=0,"",0)
This will check all cells before entering a 0.

"Sean Timmons" wrote:

if the column in question is A, and if, say, column B will have data:

In cell A2:

=if(B2 = "","",0)

and paste down.

Unfortunately, don't have the answer to the second part.

"gevew" wrote:

I am currently working on a spreadsheet that contains min. information but
will expand over time. I have formatted a column for number and would like
the zero value to show only in fields where there is other information
showing (all the rest of the rows should show no zeros. I also found that
because I applied conditional formatting to one of the columns from row
2-2000, when I want to print the whole spreadsheet rows 1-2000 are selected
to print (pages 1-56) not just the page(s) with data on them. How do I get it
to auto select only the rows with data.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,365
Default Show zero values only when data is entered in other fields

Perhaps this will help with the 2nd part of your problem. This code goes
into the Workbook's event handling module. To get into that easily,
Right-click on the Excel icon next to the [File] entry in the menu bar and
choose [View Code] from the list. Then cut and paste and test this code into
that code module. To test it, just try printing the sheet or entire
workbook. The Cancel = True statement at the end of it will prevent actually
printing and wasting paper while you test. Remove that line of code when you
decide it's working properly.

Change the name of the sheet in the code and change the value of
TargetColumn to a column identifier that you would expect the most data in
(would go down the sheet farthest), and change the value of LastColumnUsed to
be the column identifier for the right-most column you want printed.


Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Identify column that will always go farthest down the sheet
Const TargetColumn = "D" ' change D as required
'Identify the last column to the right that is used
Const LastColumnUsed = "F" ' change as required

Sheets("Sheet1").PageSetup.PrintArea = "$A$1:" & LastColumnUsed & _
Sheets("Sheet1").Range(TargetColumn & Rows.Count).End(xlUp).Row

Cancel = True ' delete for real world use after testing
End Sub


"gevew" wrote:

I am currently working on a spreadsheet that contains min. information but
will expand over time. I have formatted a column for number and would like
the zero value to show only in fields where there is other information
showing (all the rest of the rows should show no zeros. I also found that
because I applied conditional formatting to one of the columns from row
2-2000, when I want to print the whole spreadsheet rows 1-2000 are selected
to print (pages 1-56) not just the page(s) with data on them. How do I get it
to auto select only the rows with data.

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default Show zero values only when data is entered in other fields

Thanks, this is a little over my head but I will give it a try and let you
know how it works. :)

"JLatham" wrote:

Perhaps this will help with the 2nd part of your problem. This code goes
into the Workbook's event handling module. To get into that easily,
Right-click on the Excel icon next to the [File] entry in the menu bar and
choose [View Code] from the list. Then cut and paste and test this code into
that code module. To test it, just try printing the sheet or entire
workbook. The Cancel = True statement at the end of it will prevent actually
printing and wasting paper while you test. Remove that line of code when you
decide it's working properly.

Change the name of the sheet in the code and change the value of
TargetColumn to a column identifier that you would expect the most data in
(would go down the sheet farthest), and change the value of LastColumnUsed to
be the column identifier for the right-most column you want printed.


Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Identify column that will always go farthest down the sheet
Const TargetColumn = "D" ' change D as required
'Identify the last column to the right that is used
Const LastColumnUsed = "F" ' change as required

Sheets("Sheet1").PageSetup.PrintArea = "$A$1:" & LastColumnUsed & _
Sheets("Sheet1").Range(TargetColumn & Rows.Count).End(xlUp).Row

Cancel = True ' delete for real world use after testing
End Sub


"gevew" wrote:

I am currently working on a spreadsheet that contains min. information but
will expand over time. I have formatted a column for number and would like
the zero value to show only in fields where there is other information
showing (all the rest of the rows should show no zeros. I also found that
because I applied conditional formatting to one of the columns from row
2-2000, when I want to print the whole spreadsheet rows 1-2000 are selected
to print (pages 1-56) not just the page(s) with data on them. How do I get it
to auto select only the rows with data.



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,365
Default Show zero values only when data is entered in other fields

If it gets too messy or problematic, you can always reach me at (remove spaces)
HelpFrom @ jlathamsite.com

"gevew" wrote:

Thanks, this is a little over my head but I will give it a try and let you
know how it works. :)

"JLatham" wrote:

Perhaps this will help with the 2nd part of your problem. This code goes
into the Workbook's event handling module. To get into that easily,
Right-click on the Excel icon next to the [File] entry in the menu bar and
choose [View Code] from the list. Then cut and paste and test this code into
that code module. To test it, just try printing the sheet or entire
workbook. The Cancel = True statement at the end of it will prevent actually
printing and wasting paper while you test. Remove that line of code when you
decide it's working properly.

Change the name of the sheet in the code and change the value of
TargetColumn to a column identifier that you would expect the most data in
(would go down the sheet farthest), and change the value of LastColumnUsed to
be the column identifier for the right-most column you want printed.


Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Identify column that will always go farthest down the sheet
Const TargetColumn = "D" ' change D as required
'Identify the last column to the right that is used
Const LastColumnUsed = "F" ' change as required

Sheets("Sheet1").PageSetup.PrintArea = "$A$1:" & LastColumnUsed & _
Sheets("Sheet1").Range(TargetColumn & Rows.Count).End(xlUp).Row

Cancel = True ' delete for real world use after testing
End Sub


"gevew" wrote:

I am currently working on a spreadsheet that contains min. information but
will expand over time. I have formatted a column for number and would like
the zero value to show only in fields where there is other information
showing (all the rest of the rows should show no zeros. I also found that
because I applied conditional formatting to one of the columns from row
2-2000, when I want to print the whole spreadsheet rows 1-2000 are selected
to print (pages 1-56) not just the page(s) with data on them. How do I get it
to auto select only the rows with data.

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
How do I show values of data points in a chart with the mouse poin warndt Charts and Charting in Excel 0 March 3rd 06 06:58 PM
function to show if two fields in a column have the same data. j-p-c Excel Worksheet Functions 1 June 22nd 05 10:55 AM
how do you prevent data from changing values when sorting linked . Cassie Excel Discussion (Misc queries) 0 March 4th 05 11:45 AM
Pulling data from 1 sheet to another Dave1155 Excel Worksheet Functions 1 January 12th 05 06:55 PM
Newbie to charts question - projecting values between data points 38N90W Excel Discussion (Misc queries) 3 January 6th 05 06:15 AM


All times are GMT +1. The time now is 02:38 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"