Jump to content


Photo

Data from multiple col to a cell in different sheet

IF OR SUM unhide

  • Please log in to reply
6 replies to this topic

#1 Scouter702

Scouter702

    Member

  • Members
  • PipPip
  • 11 posts

Posted 02 October 2016 - 11:08 PM

In a sheet named "Prizes", I enter, "=IF('Individual Sales')!D11>0,OR(E11>0),1).  I don't get any error but the results are wrong.

It works fine if I remove the OR(E11>0).  I'm lost



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 845 posts

Posted 03 October 2016 - 12:12 PM

Hi Scouter,

 

I think the logic you are trying to model is this:

 

IF D11=0 OR E11=0, return 1

 

If this is the case, the formula should look like this:

 

=IF(OR(D11=0,E11=0),1)

 

The OR function needs to have at least two arguments in order to do anything useful at all, so you can always tell that you've made a mistake if you end up with an OR function that has only one argument.

 

You can see a complete walkthrough of using the IF function in the video lesson: Expert Skills Lesson 3-16 Use the AND and OR functions to construct complex Boolean criteria.

 

I hope this is the solution you were looking for, but please feel free to reply if you need any more help with this. Note that you can attach a copy of your workbook if you'd like me to take a look at it (instructions here).


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 Scouter702

Scouter702

    Member

  • Members
  • PipPip
  • 11 posts

Posted 03 October 2016 - 07:05 PM

In sheet "individual Prizes", I want Col J to show a 1 if any number appears in Col "D" OR "E" of Sheet "Individual Sales".

I've got =IF(OR('Individual Sales'!D11>0,E11>0),1)

that doesn't work.

Attached Files



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 845 posts

Posted 04 October 2016 - 11:34 AM

Hi Scouter,

 

Apologies, I didn't realize that the formula was referring to cells on a different worksheet. The correct formula looks like this:

 

=IF(OR('Individual Sales'!D11>0,'Individual Sales'!E11>0),1,"")

 

You can see more about cross-worksheet formulas in the video lesson: Essential Skills Lesson 6-6 Create cross worksheet formulas.

 

...however, you could also simplify this formula to:

 

=IF(SUM('Individual Sales'!D11:E11)>0,1,"")

 

This works by adding up any numbers found in columns D and E and then checking if the total is greater than zero. It should have exactly the same effect as the OR formula but it's simpler and easier to understand.

 

The IF function used here is covered in the video tutorial: Expert Skills Lesson 3-5 Use the IF logic function.

 

I have attached a copy of your workbook with the formulas applied:

 

Attached File  Popcorn_Sales v4.0.xlsx   78.74KB   42 downloads

PopcornSales.png

 

I hope this solves the problem, but please feel free to reply if it's still not quite right.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 Scouter702

Scouter702

    Member

  • Members
  • PipPip
  • 11 posts

Posted 04 October 2016 - 04:03 PM

Beautiful - thankyou..  That's exactly what I need.



#6 Scouter702

Scouter702

    Member

  • Members
  • PipPip
  • 11 posts

Posted 05 October 2016 - 04:52 PM

Before you leave - How do I find the missing columns in sheet "Individual Prizes", Col BL to CV?  They have data from Col. A & B but I can't find them.



#7 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 845 posts

Posted 05 October 2016 - 09:51 PM

Hi Scouter,

 

Those columns have been hidden, but you can bring them back by un-hiding them.

 

To do this, select the columns either side of BL and CV, then right-click and click Unhide from the shortcut menu.

 

You can see a full walkthrough of hiding and un-hiding sheets, columns and rows in the video lesson: Expert Skills Lesson 7-7 Hide and unhide worksheets, columns and rows.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users