Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I show values of data points in a chart with the mouse poin | Charts and Charting in Excel | |||
function to show if two fields in a column have the same data. | Excel Worksheet Functions | |||
how do you prevent data from changing values when sorting linked . | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Newbie to charts question - projecting values between data points | Excel Discussion (Misc queries) |