UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Formula Help Please, Office 2010    
 
   
armitageshanks
post Nov 15 2017, 05:11 AM
Post#1



Posts: 436
Joined: 9-September 03
From: UK


Hi,

I'm trying to do the following in one formula but getting confused....

If A1 contains 'Agreed' or B1 contains 'Agreed', return 'Agreed'. Both A1 and B1 can be blank, but not together.
If A1 contains 'Not Agreed' or B1 Contains 'Not Agreed', return 'Not Agreed'. Both A1 and B1 can be blank, but not together.

A1 can contain 'Agreed' and B1 contains 'not agreed', I need to return 'not agreed'.
If A1 contains 'Not agreed', B1 will be blank. So I need to return 'not agreed'

And another one....

On the above premise the use then enters a reason for 'not agreed' in column C.
In Column D the user then selects the department, 'Team A' and 'Team B'

I then need to count the amounts of 'not agreed from in Column A, Column B, then how this relates to Team A and Team B, and then summarise the reasons entered in Column C.

So it will look like:

Team A -
Agreed 5

Not Agreed 6
Missing documents - 2
Missing label - 1
Incomplete order - 3

Then repeat for Team B
Go to the top of the page
 
nuclear_nick
post Nov 15 2017, 06:30 AM
Post#2



Posts: 1,391
Joined: 5-February 06
From: Ohio, USA


As a "programmer", I tend to think in terms of the "true/false"...

So...

QUOTE
If A1 contains 'Agreed' or B1 contains 'Agreed', return 'Agreed'. Both A1 and B1 can be blank, but not together.
If A1 contains 'Not Agreed' or B1 Contains 'Not Agreed', return 'Not Agreed'. Both A1 and B1 can be blank, but not together.

A1 can contain 'Agreed' and B1 contains 'not agreed', I need to return 'not agreed'.
If A1 contains 'Not agreed', B1 will be blank. So I need to return 'not agreed'


becomes...

QUOTE
If A1 is True or B1 is True, return True.
If A1 is False or B1 is False, return False.

(If) A1 is True and B1 is False, I need to return False.
If A1 contains False, B1 will be blank. So I need to return False


Twice mentioned is that both can be blank. Assuming that is an incomplete record, when we are checking for A1 and B1, we can omit checking records where both A1 and B1 are blank, correct?

Ponder that, and let us know if you need more help.

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
armitageshanks
post Nov 15 2017, 06:36 AM
Post#3



Posts: 436
Joined: 9-September 03
From: UK


Hi

Yes you are right,

So what I'm not sure is how to do an IF statement when I'm checking whether A or B contains False.
Column A if True, can be overridden by Column B being false, as column B is the final decision.

Thanks
Go to the top of the page
 
nuclear_nick
post Nov 15 2017, 06:48 AM
Post#4



Posts: 1,391
Joined: 5-February 06
From: Ohio, USA


Here's one way... it's a start...

CODE
If A1 = False Then
  Return False     'B1 will be blank when A1 is False
ElseIf A1 = True Then
  If B1 = True Then
    Return True
  ElseIf B1 = False Then
    Return False
  End If
End If


All 'air code' of course...

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 04:22 AM