excel if statement

[transamraiser]

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?

 

[cyclops]

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.

 

[transamraiser]

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

 

[strollin]

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.

 

[UsernameIssues]

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.

It depends on the desired outcome.

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".

 

[strollin]

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.

 

[UsernameIssues]

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)

Google
excel hide row based on cell value
for many other ways to construct such macros.

 

[Ranger4]

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(A2-A3=0,””,A2-A3)

Here’s how to read the formula. If 0 is the result of (A2-A3), don’t display 0 – display nothing (indicated by double quotes “”). If that’s not true, display the result of A2-A3. 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.

 

[strollin]

... 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. ...

That is the problem. We don't really know exactly what the OP wants. What I posted meets the requirements that were given. Anything beyond that is just a guess.

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.

 

[transamraiser]

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?

 

[transamraiser]

basically were wanting to have whatever is in row a3 to copy over to row a3 on the second sheet if the qty is above 0