Multiple If's, 3 Criteria's

horsey

New Member
Joined
Dec 13, 2004
Messages
9
I wanted to do a multiple if statement, where if cell B1 contains within the phrase "report", then for cell C1 to say "report". If B1 contains blank, for the C1 cell to default to say whatever value is in the previous cell. And the 3rd criteria is if the cell B1 doesn't contain "report" or is not blank to say whatever is in cell A1 into C1. I hope I didn't lose anyone through the verbage ramblings.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

JPM

Active Member
Joined
Aug 1, 2002
Messages
406
Welcome to the board,
This is actually a very straight forward problem, except what do you mean with "previous cell"?

JPM
 

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
You would want something like
=IF(ISBLANK(B1),"previous cell value",CHOOSE(1+ISNUMBER(SEARCH("report",B1)),A1,B1))

where the question is on previous cell value. If you mean, if b1 is blank, leave whatever was in cell c1 the same, you will need code over formula (unless the c1 when can be derived from other cells)
 

horsey

New Member
Joined
Dec 13, 2004
Messages
9
The column C is suppose to change as seen in the screen shot. Many thanks to the board.

screen.JPG
 

horsey

New Member
Joined
Dec 13, 2004
Messages
9
I think I got it now:

=IF(ISBLANK(B2),E1,CHOOSE(1+ISNUMBER(SEARCH("report",B2)),A2,"report"))

I skipped the first cell because I needed the resulting formula cell to read data from the previous if needed.

Thanks you guys!
 

JPM

Active Member
Joined
Aug 1, 2002
Messages
406
Actually IML's formula has a little hickup in the case B1 has i.e. the word "Expense Report" if will show everything that is in B1, not just the word "report".

You can make this minor adjustment:
=IF(ISBLANK(B1),"previous cell value",IF(ISNUMBER(SEARCH("report",B1))=TRUE,"report",A1))

JPM

Did not see your latest response.
 

Forum statistics

Threads
1,147,690
Messages
5,742,638
Members
423,746
Latest member
Joaogomes

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
Top