Windows 7 Forums

Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: excel if statement

29 Oct 2015   #1
transamraiser

Ohio
 
 
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?




Attached Images
excel if statement-excel-1.png excel if statement-capture2.png 
My System SpecsSystem Spec
.
29 Oct 2015   #2
cyclops

Microsoft Windows 7 Ultimate 64-bit 7601 Multiprocessor Free Service Pack 1
 
 

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 SpecsSystem Spec
29 Oct 2015   #3
transamraiser

Ohio
 
 

i did but i was hoping someone could help here..
My System SpecsSystem Spec
.

29 Oct 2015   #4
strollin

W10 Pro desktop, W10 laptop, W10 laptop, W10 Pro tablet (all 64-bit)
 
 

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 SpecsSystem Spec
29 Oct 2015   #5
UsernameIssues

W7 Pro SP1 64bit
 
 

Quote   Quote: Originally Posted by strollin View Post
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:

excel if statement-1.png

excel if statement-2.png

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 SpecsSystem Spec
29 Oct 2015   #6
strollin

W10 Pro desktop, W10 laptop, W10 laptop, W10 Pro tablet (all 64-bit)
 
 

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 SpecsSystem Spec
29 Oct 2015   #7
UsernameIssues

W7 Pro SP1 64bit
 
 

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.
My System SpecsSystem Spec
29 Oct 2015   #8
Ranger4

Windows 7 Home Premium 64 bit sp1
 
 

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.
My System SpecsSystem Spec
30 Oct 2015   #9
strollin

W10 Pro desktop, W10 laptop, W10 laptop, W10 Pro tablet (all 64-bit)
 
 

Quote   Quote: Originally Posted by UsernameIssues View Post
... 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.
My System SpecsSystem Spec
02 Nov 2015   #10
transamraiser

Ohio
 
 

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?


Attached Thumbnails
excel if statement-capture2.png  
Attached Images
excel if statement-capture.png 
My System SpecsSystem Spec
Reply

 excel if statement




Thread Tools Search this Thread
Search this Thread:

Advanced Search




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/en-gb/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, right-click 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.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App