Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula that will not return "0" from a blank line
Using excel 2003
I have a workbook that I will be using to calculate stumpage and payroll. To get the data moved from the load sheet to the stumpage sheet, I have =(cell address) and I copied that down. There are 30 lines of data but not all lines will be used. If there is no data in a line I get "0" on the stumpage sheet. For this cycle there are only 11 loads. On the stumpage sheet, I entered the formula =count(loads) (loads is the range name of the 30 lines of data in colA). I choose count because in that column I have ticket numbers. So I wanted to know how many cells had numbers in that column so I would know how many lines would be on the stumpage sheet. I need a formula that will look in the cell and return only the cells that have text. Any help would be appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula that will not return "0" from a blank line
=IF(Sheet1!A1="","",Sheet1!A1)
Gord Dibben MS Excel MVP On Thu, 14 Jan 2010 13:23:02 -0800, indyjojo wrote: Using excel 2003 I have a workbook that I will be using to calculate stumpage and payroll. To get the data moved from the load sheet to the stumpage sheet, I have =(cell address) and I copied that down. There are 30 lines of data but not all lines will be used. If there is no data in a line I get "0" on the stumpage sheet. For this cycle there are only 11 loads. On the stumpage sheet, I entered the formula =count(loads) (loads is the range name of the 30 lines of data in colA). I choose count because in that column I have ticket numbers. So I wanted to know how many cells had numbers in that column so I would know how many lines would be on the stumpage sheet. I need a formula that will look in the cell and return only the cells that have text. Any help would be appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula that will not return "0" from a blank line
Thank you for your reply. I tried that but I got a 0 which is not the result
I was looking for. Maybe if I give an example it will be easier to clarify what I need. A B C D E 1 TICKET # GRADE MILL DRIVER WEIGHT 2 123 ZONE 2 CLW WILSON 29.33 3 425 ZONE 2 CLW TROY 33.00 4 5 On the stumpage sheet, I need the ticket number, zone, and weight. What I would like the function to do is once the information on row three is moved over to the stumpage sheet row four will stay blank instead of showing a "0". Currently, I have on the stumpage sheet in cell a4 I have =a2 which will result in "123". Thanks in advance for your time and assistance. "Gord Dibben" wrote: =IF(Sheet1!A1="","",Sheet1!A1) Gord Dibben MS Excel MVP On Thu, 14 Jan 2010 13:23:02 -0800, indyjojo wrote: Using excel 2003 I have a workbook that I will be using to calculate stumpage and payroll. To get the data moved from the load sheet to the stumpage sheet, I have =(cell address) and I copied that down. There are 30 lines of data but not all lines will be used. If there is no data in a line I get "0" on the stumpage sheet. For this cycle there are only 11 loads. On the stumpage sheet, I entered the formula =count(loads) (loads is the range name of the 30 lines of data in colA). I choose count because in that column I have ticket numbers. So I wanted to know how many cells had numbers in that column so I would know how many lines would be on the stumpage sheet. I need a formula that will look in the cell and return only the cells that have text. Any help would be appreciated. . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula that will not return "0" from a blank line
Hi,
To count only text values, you could use =countif(loads,"*") -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "indyjojo" wrote in message ... Using excel 2003 I have a workbook that I will be using to calculate stumpage and payroll. To get the data moved from the load sheet to the stumpage sheet, I have =(cell address) and I copied that down. There are 30 lines of data but not all lines will be used. If there is no data in a line I get "0" on the stumpage sheet. For this cycle there are only 11 loads. On the stumpage sheet, I entered the formula =count(loads) (loads is the range name of the 30 lines of data in colA). I choose count because in that column I have ticket numbers. So I wanted to know how many cells had numbers in that column so I would know how many lines would be on the stumpage sheet. I need a formula that will look in the cell and return only the cells that have text. Any help would be appreciated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula that will not return "0" from a blank line
Not clear to me what you want and where.
Is the example data you show on Ticket sheet? How do you "move" data over to Stumpage sheet? What is in row 4 of the example data you show and why would it display 0 when row 3 data is "moved"? On Stumpage sheet if no data in A2 the formula =A2 in Stumpage A4 yopu will get a 0 Change that formula to =IF(A2="","",A2) If you want, you could send me the workbook via email with some explanation. gorddibbATshawDOTca change the obvious to get my address. Gord On Thu, 14 Jan 2010 15:15:01 -0800, indyjojo wrote: Thank you for your reply. I tried that but I got a 0 which is not the result I was looking for. Maybe if I give an example it will be easier to clarify what I need. A B C D E 1 TICKET # GRADE MILL DRIVER WEIGHT 2 123 ZONE 2 CLW WILSON 29.33 3 425 ZONE 2 CLW TROY 33.00 4 5 On the stumpage sheet, I need the ticket number, zone, and weight. What I would like the function to do is once the information on row three is moved over to the stumpage sheet row four will stay blank instead of showing a "0". Currently, I have on the stumpage sheet in cell a4 I have =a2 which will result in "123". Thanks in advance for your time and assistance. "Gord Dibben" wrote: =IF(Sheet1!A1="","",Sheet1!A1) Gord Dibben MS Excel MVP On Thu, 14 Jan 2010 13:23:02 -0800, indyjojo wrote: Using excel 2003 I have a workbook that I will be using to calculate stumpage and payroll. To get the data moved from the load sheet to the stumpage sheet, I have =(cell address) and I copied that down. There are 30 lines of data but not all lines will be used. If there is no data in a line I get "0" on the stumpage sheet. For this cycle there are only 11 loads. On the stumpage sheet, I entered the formula =count(loads) (loads is the range name of the 30 lines of data in colA). I choose count because in that column I have ticket numbers. So I wanted to know how many cells had numbers in that column so I would know how many lines would be on the stumpage sheet. I need a formula that will look in the cell and return only the cells that have text. Any help would be appreciated. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"(Blank)" return when importing from Access | Excel Discussion (Misc queries) | |||
Iserror - can it return a "blank" value? | Excel Discussion (Misc queries) | |||
How do I return "leave cell blank" using IF logic. | Excel Worksheet Functions | |||
Disabling "wrap text" neuters alt-return line feeds within cell | Excel Discussion (Misc queries) | |||
Return blank for true "if" statement when charting, not 0 | Charts and Charting in Excel |