Previous Topic Next Topic

Help with populating multiple IF functions

Closed
MelyElly Posts 3 Registration date Wednesday November 21, 2012 Status Member Last seen November 23, 2012 - Nov 21, 2012 at 07:49 PM
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 - Nov 28, 2012 at 11:55 AM
Hello,
I'm working in an excel workbook with several sheets.

I want information from the "Income" sheet to populate to the "Discounts" sheet when an income cell equals a certain amount. I have figured out the formula cell by cell, but how do I make it automatically work for income that I have yet to plug in?

For instance, D1 in my "Discounts" sheet is formulated correctly like this:
=IF(ISNUMBER(SEARCH("13.99",Income!C1)),"1")
Now how do I paste that down into the whole D column so that D2, D3, etc. each have that formula? Also, how do I make each subsequent D cell match with a corresponding cell from the "Income" sheet that also moves down (C2, C3, etc.)?

One final question: How do I make excel not leave a blank line when the IF query returns false?

Thanks so much for any and all help!
Excel sure is fun, isn't it? And helpful, when you can get it right!

2 responses

Answer 1 / 2
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Nov 28, 2012 at 11:55 AM
Hi MelyElly,

I am sorry for the delayed response. I used Microsoft Excel's fill handle to drag and the formula copied as below, don't need to enter it manually:

=IF(ISNUMBER(SEARCH("13.99",Income!C1)),"1","")
=IF(ISNUMBER(SEARCH("13.99",Income!C2)),"1","")
=IF(ISNUMBER(SEARCH("13.99",Income!C3)),"1","")
=IF(ISNUMBER(SEARCH("13.99",Income!C4)),"1","")

To use Microsoft Excel's fill handle copy a cell or range of cells by holding down CTRL while you position the mouse pointer on a cell or cell range border so that it changes to a copy pointer, and then dragging the cell or range of cells to another location.

Please revert for clarification.
1
Answer 2 / 2
Zohaib R Posts 2368 Registration date Sunday September 23, 2012 Status Member Last seen December 13, 2018 69
Nov 23, 2012 at 12:04 PM
Hi MelyElly,

Your formula is close to what you want to do. To match the subsequent D cell with C replace:

Income!C1

With:

C1

And to return an empty cell when the value returned by the formula is FALSE just add "" to the end of the formula. The formula should finally look like this:

=IF(ISNUMBER(SEARCH("13.99",C1)),"1","")

Do reply with result.
0
MelyElly Posts 3 Registration date Wednesday November 21, 2012 Status Member Last seen November 23, 2012
Nov 23, 2012 at 03:45 PM
Thanks for trying to help. The C cell is referencing the first (Income) sheet, so that's why it has to stay "Income!C1." I'm just wondering how to make it automaticaly change to "Income!C2," etc. on the discounts sheet.
Do you know how?
0

Similar discussions

Excel - IF function with multiple words
Nick -
user -

14 responses
Spreadsheet data
ankharm -
TrowaD -

9 responses
Excel multiple functions in one cell
todd -
rizvisa1 -

1 response
Can’t Log into Instagram from Multiple Devices
Isabella -
Abhishek0025 -

5 responses