Conditional formatting I think?

[Robertkeegan0610]

Hi all

Pretty new to excel really but keen to use it for my job and trying to test out the basics

I have excel with 4 questions, each question has the same 2 answers. Depending on the combination of answers selected will determine the overall answer. How do i get excell to automatically populate the overall answer for me based on the combination of answers? What formulas do i need and for th

[Pete_UK]

Your attachment doesn't make much sense to me. Please describe what you are trying to achieve a bit more fully. Do you want to generate all the combinations of those two words in four positions?

Pete

[Robertkeegan0610]

sorry for the poor explaining on my behalf. so the 4 questions. A, B, C and D all have the same 2 responses (buyer and Seller) so one possible cominbation could be A buyer B Seller C Buyer D Seller. hope that makes sense so far. With those responses AB, BS, CB, DS. In the Term box the final answer is for example is 1. If question A was seller instead of buyer the final answer would be 2. Sorry if im not explaining this well

[JeteMc]

Hello Robertkeegan0610 and Welcome to Excel Forum.
Like Pete_UK I am not sure what you want, however I will make a guess and see where it goes.
To get a value for Term, there would need to be a list of possible permutations, by the way there are 16, and I have put 5 possibilities in Q5:T9
The formula that then populates cell N5 is: =INDEX(P5:P9,AGGREGATE(15,6,(ROW(P5:P9)-ROW(P4))/(Q5:Q9=A5)/(R5:R9=C5)/(S5:S9=J5)/(T5:T9=L5),1))
Let us know if you have any questions.

[Robertkeegan0610]

Hello Robertkeegan0610 and Welcome to Excel Forum.
Like Pete_UK I am not sure what you want, however I will make a guess and see where it goes.
To get a value for Term, there would need to be a list of possible permutations, by the way there are 256, and I have put 5 possibilities in Q5:T9
The formula that then populates cell N5 is: =INDEX(P5:P9,AGGREGATE(15,6,(ROW(P5:P9)-ROW(P4))/(Q5:Q9=A5)/(R5:R9=C5)/(S5:S9=J5)/(T5:T9=L5),1))
Let us know if you have any questions.

Thank you so much! That is exactly what I was looking for I just didn't know how to word it best. I don't think I'll need all 256 possibilities as the way I want my questionnaire to work is that once the respondent switches from seller to buyer, then they cannot change back. I'm sure there's a way to lock in that but I'll master the INDEX and AGGREGATE functions first before I try anything else.

Once again thank you

EDIT

Spoke to soon , for some reason the possibilities I want that I've put in term possibilities section don't all work. I think its the option "Seller" that causes an issue but I dont know what I'm missing. Any help would be greatly appreciated

[JeteMc]

There is an extra space at the end of the word Seller in cells C2 and D2 on Sheet2.
Remove the extra space and then reselect Seller in cells J5 and L5 on Sheet1.
Let us know if you have any questions.

[Pete_UK]

By the way, you can generate all those combinations by using this formula in A5 of Sheet2:

=INDEX(A$1:A$2,MOD(INT((ROWS($1:1)-1)/2^(COLUMNS(A:$D)-1)),2)+1)

Copy across into B5:D5, then copy those four formulae down to row 20.

Hope this helps.

Pete

[AliGW]

Please update your forum profile to tell us specifically which version of Excel you are using.

In future, please make your thread titles more explicit: tell us what you are trying to do, not a guess at how it should be done.

[Robertkeegan0610]

There is an extra space at the end of the word Seller in cells C2 and D2 on Sheet2.
Remove the extra space and then reselect Seller in cells J5 and L5 on Sheet1.
Let us know if you have any questions.

Thank you that worked perfectly

Is there way to do the reverse, so if term possibilities has a dropdown list, changing the term would change the buyer seller combo?

[Robertkeegan0610]

By the way, you can generate all those combinations by using this formula in A5 of Sheet2:

=INDEX(A$1:A$2,MOD(INT((ROWS($1:1)-1)/2^(COLUMNS(A:$D)-1)),2)+1)

Copy across into B5:D5, then copy those four formulae down to row 20.

Hope this helps.

Pete

Yes that did help, I will use that when I expand the file with more options in my next version once ive cracked this

[JeteMc]

Is there way to do the reverse, so if term possibilities has a dropdown list, changing the term would change the buyer seller combo?

If the question is, would putting Seller (without a trailing space) into cell J5 on Sheet1 change cell C2 on Sheet2 then the answer is no, in fact if you try to type Seller (without a trailing space) into cell J5 (Sheet1) you'll get a popup error message.
If that isn't the question, then please restate.

[Robertkeegan0610]

Please update your forum profile to tell us specifically which version of Excel you are using.

In future, please make your thread titles more explicit: tell us what you are trying to do, not a guess at how it should be done.

Okay I will do, sorry still all a bit new

[Robertkeegan0610]

If the question is, would putting Seller (without a trailing space) into cell J5 on Sheet1 change cell C2 on Sheet2 then the answer is no, in fact if you try to type Seller (without a trailing space) into cell J5 (Sheet1) you'll get a popup error message.
If that isn't the question, then please restate.

Sorry my poor communication there, this isn't to do with the trailing space anymore. It is back to the original function of the spreadsheet.
So as I change the options between buyer and seller. The term box changes, 1 through to 5. Is it possible to change the term box via a drop down list and the buyer and seller boxes change due the change in the term box. So pretty much the reverse of the original function. So if someone didn't know there term they would use the buyer seller questions. But if the knew there term but not how that term was worked out they selected a term to reveal the buyer seller combination. I hope that explains it a bit more clearly

[JeteMc]

As long as the term isn't typed into cell N5 as that would overwrite the formula in that cell. Also data validation will overwrite a formula if one is placed in A5:L5.
You could type or use data validation to choose a term in another cell, as modeled in cell N6.
At that point you could use the following formula (modeled in A6:D6) to display the buyer/seller arrangement: =INDEX(Q5:Q9,MATCH($N6,$P5:$P9,0))
Let us know if you have any questions.