Text label and value if value in other worksheet is > 0

Solved/Closed
fixradio
Posts
4
Registration date
Friday December 19, 2014
Status
Member
Last seen
December 23, 2014
- Dec 22, 2014 at 09:58 AM
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
- Jan 5, 2015 at 11:39 AM
Hello,

can someone please help me with a formula for this.
Text label and value, if value in other worksheet from 2 different cells is > 0, label based on which cell is > 0.
Examples
IF sheet1 A1 is blank, cell B1 is blank then sheet2 cell E1= blank, no value or text
IF sheet1 A1=$2.00, cell B1 is blank then sheet2 cell E1= Discount $2.00
IF sheet1 A1 is blank, cell B1=$2.00 then sheet2 cell E1= Rebate $2.00

Thanks in advance for any help.

4 replies

TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
515
Dec 23, 2014 at 10:57 AM
Hi Fizradio,

Here is your requested formula:
=IF(AND(Sheet1!A1="",Sheet1!B1=""),"",IF(AND(Sheet1!A1>0,Sheet1!B1=""),"Discount $"&Sheet1!A1,IF(AND(Sheet1!A1="",Sheet1!B1>0),"Rebate $"&Sheet1!B1,"")))

Best regards,
Trowa
1
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
515
Dec 22, 2014 at 12:07 PM
Hi Fixradio,

Did you tried the IF formula?

What exactly do you mean by "Discount $2.00" and "Rebate $2.00". Are those text values or do you want "-$2.00" and "+$2.00"?

Don't know how to refer to a different sheet? Let Excel figure it out for you by selecting a cell on a different sheet while entering your formula.

Let us know where you get stuck.

Best regards,
Trowa
0
fixradio
Posts
4
Registration date
Friday December 19, 2014
Status
Member
Last seen
December 23, 2014

Dec 22, 2014 at 06:06 PM
HI trowaD

Thanks for responding to my question.

If sheet 1 has nothing in cells A1 or B1 then sheet2 E1 displays nothing.

IF sheet1 A1=any numeric value greater than 0, and cell B1 is blank then sheet2 cell E1= displays the text "Discount $"and the value from Sheet 1 A1.

IF sheet1 A1 is blank, and cell B1= any numeric value greater than 0 then sheet2 cell E1= displays the text "Rebate $"and the value from Sheet 1 B1.

Hope that helps to clarify my question.

I have used some simple IF statements including referring to a different sheet, but do not know how to nest or craft the needed formula.

Thanks again for your time and help.

Fixradio
0
fixradio
Posts
4
Registration date
Friday December 19, 2014
Status
Member
Last seen
December 23, 2014

Dec 23, 2014 at 03:10 PM
Trowa,

Thanks!

Do you or anyone else have a recommended book on Excel or Site for further studies?
I would like to improve my skills in formula construction.

Thanks again.

Fixradio
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
515
Jan 5, 2015 at 11:39 AM
Hi Fixradio,

I mainly use google searches, but since that is not an answer to your question, I would suggest creating a separate thread.

Best regards,
Trowa
0