
29 Oct 2015  #1 

excel if statement
we are wanting to see if we can create an if statement in excel. basically, we are looking to create an if statement. in the first picture below, you see we have a "3" under quanity. then you also see the prices as well. we're wanting to do something like this  if "A5" (the quanity) is greater than zero, we want it to display the whole row (the qty,size, net, list, and total) into another spreadsheet. basically, if the qty is greater than 0 in the first form, it will then display that whole row in the 2nd spreadsheet(second picture) is there anyway to do this?

My System Specs 
. 
29 Oct 2015  #2 

I have not done this in years, but I know you can link spreadsheets and workbooks to have one update itself when data in the other one changes. Try starting with a web search for Working with linking excel workbooks/spreadsheets. I did and there is plenty to learn from.

My System Specs 
29 Oct 2015  #3 

i did but i was hoping someone could help here..

My System Specs 
. 

29 Oct 2015  #4 

You should enter the formulas on the second sheet. Assuming that the 2 sheets are named Sheet1 & Sheet2, in your example, to have the Net column (cell C4) on Sheet2 automatically filled in, the formula (placed in cell C4) would be: =IF(Sheet1!A5 > 0, Sheet1!C5, ). This basically says if cell A5 on Sheet1 is greater than 0 then this cell gets the value from Sheet1 cell C5, otherwise do nothing. You should be able to figure out the formulas for the other cells (Qty, Size, List and Total) based on this formula.
Technically, you don't really need the IF statement. You could just use the formula: =Sheet1!C5. If you put a value into cell C5 on Sheet1 then the cell where this formula is placed will show that value. 
My System Specs 
29 Oct 2015  #5 

You should enter the formulas on the second sheet. Assuming that the 2 sheets are named Sheet1 & Sheet2, in your example, to have the Net column (cell C4) on Sheet2 automatically filled in, the formula (placed in cell C4) would be: =IF(Sheet1!A5 > 0, Sheet1!C5, ). This basically says if cell A5 on Sheet1 is greater than 0 then this cell gets the value from Sheet1 cell C5, otherwise do nothing. You should be able to figure out the formulas for the other cells (Qty, Size, List and Total) based on this formula.
Technically, you don't really need the IF statement. You could just use the formula: =Sheet1!C5. If you put a value into cell C5 on Sheet1 then the cell where this formula is placed will show that value. I interpreted the OP's request as not wanting rows with zeros in the second spreadsheet: Your solution puts rows with zeros in the second spreadsheet... ...or maybe I'm not understanding your proposed solution. The OP stated, "if the qty is greater than 0 in the first form, it will then display that whole row in the 2nd spreadsheet". I took that to also mean, "if the qty is 0 in the first form, it will then not display that whole row in the 2nd spreadsheet". 
My System Specs 
29 Oct 2015  #6 

So what's your solution?
If the OP doesn't want zeroes then the formula =IF(Sheet1!A5 > 0, Sheet1!C5, "") will blank out the field if a Qty isn't entered. 
My System Specs 
29 Oct 2015  #7 

If I had a solution  I would have posted it.
Yes, you can make the linked cell blank, and the entire row blank. My screenshot of the ORDER FORM (2nd spreadsheet) currently has info in row 4 ($3 row) and row 5 ($12 row). Imagine what that ORDER FORM would look like if there were 27 empty rows between the $3 row and the $12 row. I'm not much of an Excel guru. My post was only an attempt at clarifying what I think the OP wants. If I'm wrong and empty rows don't matter, then your solution is great. If empty rows cannot be on the ORDER FORM, then I think that we are looking at a combination of Excel's IF function and somehow include a range of cells. edit: It might have to involve a macro to hide rows where values in column A are 0. Hiding Rows Based on a Cell Value (Microsoft Excel) excel hide row based on cell value for many other ways to construct such macros. 
My System Specs 
29 Oct 2015  #8 

Is this any help. I don't usually do these sort of calculations in Excel, but I have copied this from Excel 2013 Help files. This formula is shown for working cells A2 & A3 in a vertical format, but it should be able to used in a horizontal format.
Display zeros as blanks or dashes Use the IF function to do this. Data in cells A2 and A3 in an Excel worksheet Use a formula like this to return a blank cell when the value is zero: =IF(A2A3=0,””,A2A3) Here’s how to read the formula. If 0 is the result of (A2A3), don’t display 0 – display nothing (indicated by double quotes “”). If that’s not true, display the result of A2A3. If you don’t want the cells blank but want to display something other than 0, put a dash ““ or other character between the double quotes. 
My System Specs 
30 Oct 2015  #9 

... My post was only an attempt at clarifying what I think the OP wants. If I'm wrong and empty rows don't matter, then your solution is great. If empty rows cannot be on the ORDER FORM, then I think that we are looking at a combination of Excel's IF function and somehow include a range of cells. ...
Personally, I would write a macro that would only transfer the qualifying records to the 2nd sheet. I was trying to do as simple a formula as possible for the OP as a formula is what was asked for. 
My System Specs 
02 Nov 2015  #10 

hey all
heres what we're wanting to do we have an excel sheet that is updated when people order things off our website. so lets say someone orders 2 pipes off the site. its transferred to an excel sheet like this (see first picture) we have two sheets inside the actual workbook. so if cell "A3" (qty) is greater than 0, we want it to display in sheet 2 does that make sense? 
My System Specs 
Thread Tools  Search this Thread 
Similar help and support threads  
Thread  Forum  
Microsoft has a new mission statement Read more: Microsoft has a new mission statement and is about to make a few tough choices 
News  
Is this a real Windows statement? Anecdotally I believe the following is the result of malware since after running ADWcleaner the problem is gone. But I want to be sure. The problem appears when rebooting. Before the blue screen that says shutting down there's a black screen with the words "Operations are in progress please wait... 
System Security  
Microsoft updating terms of use and privacy statement In an email today to its customers, Microsoft has announced new terms of use and privacy statement: Microsoft Services Privacy Statement: http://www.microsoft.com/privacystatement/engb/windowsservices/default.aspx Microsoft Services Agreement:... 
News  
Microsoft's new mission statement: No more computer on every desk Read more at: Microsoft's new mission statement: No more computer on every desk  ZDNet 
News  
Using Windows 7 Path Statement I have a couple a couple of older software programs that I was trying to get to communicate with each other and tried using the Windows 7 path statement to get that accomplished. So far, bad luck. This is the process I followed: From the desktop, rightclick My Computer and click... 
General Discussion  
Help with Batch file, if statement in a specific time. hi, I am working on a batch file, such as If this is not possible, then just simply, Thanks! 
General Discussion 
Our Sites 
Site Links 
About Us 
Find Us 
Windows 7 Forums is an independent web site and has not been authorized,
sponsored, or otherwise approved by Microsoft Corporation.
"Windows 7" and related materials are trademarks of Microsoft Corp.
© Designer Media Ltd All times are GMT 5. The time now is 16:11. 
