Help with populating multiple IF functions

[Closed]
Report
Posts
3
Registration date
Wednesday November 21, 2012
Status
Member
Last seen
November 23, 2012
-
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
-
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 replies

Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
46
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
46
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.
Posts
3
Registration date
Wednesday November 21, 2012
Status
Member
Last seen
November 23, 2012

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?