another formula need hopfuly someone can help!

tonylpcs@yahoo.co.uk

Active Member
Joined
Dec 19, 2007
Messages
379
Hi Everyone,

Ok Got one More i hope this is it so thanks to everyone for your help!

i have three colums K, M, & O, and i need the data from another cell to be input into one of them depending on the situation,

so first of all i only want the data to apear if sheet2 R1 = "NO" otherwise i want a blank line, however if sheet2 R1 does ="No" then i want the following to happen

in O1 i want if sheet2 Q1 = "no" then input the data from I1, if not blank
in M1 i want if sheet2 Q1 = "yes" and sheet2 J = "Renewal / Other" then input the data from I1, if not blank
and in K1 i want if sheet2 Q1 = "yes" and sheet2 J = "New Business" then input the data from I1, if not blank

i think i will need 3 different formulars but if you can let me have what i need for
O1
M1
K1
this would help me a great deal thank you

Tony:(
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Here you go:

O1 : =IF(Sheet2!$R$1="NO","",IF(Sheet2!$Q$1="no",$I$1,""))
M1 : =IF(Sheet2!$R$1="NO","",IF(AND(Sheet2!$Q$1="yes",Sheet2!$J$1="Renewal / Other"),$I$1,""))
K1 : =IF(Sheet2!$R$1="NO","",IF(AND(Sheet2!$Q$1="yes",Sheet2!$J$1="New Business"),$I$1,""))

Does this help?
 
Upvote 0
Hi i got O1 to work cant get M1 to work,
had to change the formula slightly colums and lines have changed since i posted it, this is what ive done can you see anything wrong

=IF(Sheet2!$R2="NO","",IF(AND(Sheet2!$Q2="yes",Sheet2!$J2="Renewal / Other"),Sheet2!I2,""))
 
Upvote 0
Do you want $I$2 from Sheet2 or from the sheet on which you are entering the formula? If it's from the sheet on which you are entering the formula, then you need to get rid of Sheet2! from in front of I2.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top