Conditions in 3 cells show true or false.

**abjac** · 08-01-2020, 06:14 AM

Hi. I normally do macros so i have to admit formula is not my strong skill. I have posted for some if conditions which was not solved. It still make errors, so i was thinking maybe your formula experts could solve it for me. I know some times formula can do some which is hard in macros. I can run a macro if a cell show true example.
Here is my conditions i would like to solve.

In cell 1. If cell B21 have the word "unable to contact." and same time if cell B23 are empty show True.
In cell 2. If cell B21 have the word " Only want mail." and same time cell cell B 13 is empty show True.

And now the very hard one i think.

In cell 3. If cell B21 have the word "Lead to validate." and same time one or more of these are empty Cell B27, B29, D27,D29 or textbox1 show true
remember its the word "Lead to validate." and then one or more of the 4 cells and the textbox1 empty

This below little related conditions. To calculate in a cell.
If these words in cell B21

Only want mail. plus +10 to cell D23
Company decision abroad. Minus -20 to cell D23
Not interested. Minus -10 to cell D23
Outsourching. Minus -20 to cell D23
Lead to be nurtured. Plus +50 to cell D23
Lead to validate. Plus +100 to cell D23.

I hope some could solve this and as i said formulas are not what i am best to. Please have a look.
Sincerely
Abjac

**AliGW** · 08-01-2020, 07:01 AM

Does your user profile need updating?

There are instructions at the top of the page explaining how to attach your sample workbook.

**abjac** · 08-01-2020, 07:35 AM

Hi Sorry did not think it was necessary her with the formulas but of course i just uploaded a test sheet with relevant cell i talk about in the first message.-
Thanks
Sincerely
Abjac

**AliGW** · 08-01-2020, 07:47 AM

Sorry - I don't see a mock-up of expected results, only empty cells. Please provide at least ONE worked example in the sample workbook to show what you want. Thanks.

**abjac** · 08-01-2020, 08:01 AM

HI Ok i put the conditions in the sheet now so you can see what i would like to have of result and also the conditions for the formulas-. Hope its quite clear now.
Thanks
Abjac

**AliGW** · 08-01-2020, 08:10 AM

OK:

=AND(B21=" Unable to contact.",B23="")

=AND(B21=" Only want mail. ",D13="")

=AND(B21=" Lead to validate. ",OR(B27="",B27="",D27="",D29=""))

You cannot test the contents of a textbox in a formula - why can't this be a cell instead?

**abjac** · 08-01-2020, 08:35 AM

Hi AliGW.
The coded are working perfect just little error B27 2 times changed so they are working perfect.
I would like to have the textbox1 of course. I cant change it in the real workbook it is a textbox where you can write into.
But if it cant be done in formula then its like that of course.
The others are working brilliant. But wish i could have the textbox hehe
What about the calculation part in cell D23. Its also related to the different text in cell B21
Any idea how to do this. ?
Thanks
Abjac

**AliGW** · 08-01-2020, 08:50 AM

Using a lookup table (with leading and trailing spaces removed):

Excel 2016 (Windows) 32 bit

	R	S
5	Unable to contact.
6	Only want mail.	10
7	Account update.
8	Company decision abroad.	-20
9	Not interested.	-10
10	Outsourching.	-20
11	Account out of target.
12	Liquidation or Shut down.
13	Lead to be nurtured.	50
14	Lead to validate.	100

Sheet: Sheet1

=IF(VLOOKUP(B21,$R$5:$S$14,2,0)=0,"",VLOOKUP(B21,$R$5:$S$14,2,0))

Then:

=AND(B21="Unable to contact.",B23="")

=AND(B21="Only want mail.",D13="")

=AND(B21="Lead to validate.",OR(B27="",B27="",D27="",D29=""))

Again, you need to rethink the textbox, otherwise you are going to have to go down the VBA route.

**abjac** · 08-01-2020, 08:52 AM

Hi AliGW.
Thats was actually true i removed them and also the dot so it should be ok now. here is the test sheet with the updates.
Sincerely
Abjac

**AliGW** · 08-01-2020, 08:54 AM

See post #8.

**abjac** · 08-01-2020, 09:03 AM

Hi AliGW .
it seems to be working good but an important thing is it dont calculate in the cell . Maybe i did not tell that clearly but i need it like this.
So if cell have 100 before start and i go to lead to validate it should 200 after. If after that go to not interested. it should take -10 away so the number there now 190 and so on.
Sorry i think maybe not said this clearly enough.
But have a look please.
Sincerely
Abjac

**AliGW** · 08-01-2020, 09:11 AM

OK - in that case, you are going to need VBA. A cumulative total like that is NOT possible with formulae.

As this second query is very different to the original thread title (that bit is solved), I suggest you open a NEW thread in the VBA section with a title that reflects the second query and the cumulative total that you wish to instigate.

This thread should be marked as SOLVED.

Thanks.

**abjac** · 08-01-2020, 09:14 AM

Hi AliGW .
Ok i will do that . I just thougt was possible with formula to calculate like this in a cell. Thanks alot for your help. Really great
Sincerely
Abjac

**AliGW** · 08-01-2020, 09:19 AM

No, it isn't, I am afraid. A formula cannot remember previous entries in the same cell.

Conditions in 3 cells show true or false.

LinkBack

Thread Tools

Rate This Thread

Display

Conditions in 3 cells show true or false.

Re: Conditions in 3 cells show true or false.

Re: Conditions in 3 cells show true or false.

Re: Conditions in 3 cells show true or false.

Re: Conditions in 3 cells show true or false.

Re: Conditions in 3 cells show true or false.

Re: Conditions in 3 cells show true or false.

Re: Conditions in 3 cells show true or false.

Re: Conditions in 3 cells show true or false.

Re: Conditions in 3 cells show true or false.

Re: Conditions in 3 cells show true or false.

Re: Conditions in 3 cells show true or false.

Re: Conditions in 3 cells show true or false.

Re: Conditions in 3 cells show true or false.

Thread Information

Users Browsing this Thread

Similar Threads

Return TRUE or FALSE if conditions met

color conditions on xy scatter chart and show/hide series and legend name for true/false

[SOLVED] Check if cells An and Bn have specific content and show true/false in Cn

True/False for Multiple conditions and formats in a range of cells

What is best to use for helper cells 0 or 1, typed true or false or =true =false

[SOLVED] How to get TRUE or FALSE based on 2 conditions?

how to use true or false conditions

Bookmarks

Bookmarks

Posting Permissions