Formula based on variable number of non-adjacent columns

**abousetta** · 07-17-2010, 05:09 AM

Hello,

I have an excel sheet that contains decisions made by different people. They are all given then the same information is the same columns and then their decisions are imported into excel. Now I am tring to formulate decision rules but am struggling since not everyone is given the same questions and I don't know how many people will be involved (and therefore the number of columns) but I do know that only two people will be given the same identical questions to answer. I have attached a dummy workbook.

Can someone help create decision rules for column F?

Thanks.

abousetta

**DonkeyOte** · 07-17-2010, 05:44 AM

If the idea is to replicate the results in yellow on the Survey_with_decisions sheet then one approach might be:

Please Login or Register  to view this content.

**abousetta** · 07-17-2010, 06:30 AM

Thanks for this formula. I will give it a go and let you know.

abousetta

**abousetta** · 07-19-2010, 04:05 AM

Hi DonkeyOte,

This formula was very helpful. It was really refreshing because I didn't know that Lookup could be used like and after reading more about the function I began to understand your reasoning. Thank you again.

I have attempted to go a few steps forward with this but have hit a snag which I hope you can help me with.

I have added and IF statement and CountA to determine how many cells in the range have responses and to make sure that only two have been filed in. That worked fine. But then I tried to automatically determine what the last column of data in each range is but this failed.

The code I have so far is:

Please Login or Register  to view this content.

Could you please help me modify this so it can work?

Thanks again.

abousetta

**DonkeyOte** · 07-19-2010, 04:12 AM

Might be simpler to simply return the column number rather than the range .

Please Login or Register  to view this content.

Remember you can apply the formula to multiple rows simultaneously if that's the intention (ie no need to iterate using ActiveCell).

**abousetta** · 07-19-2010, 04:22 AM

Excellent... This works great. Thanks for reminding me that I can count the column number rather than using a range. Things are still new for me and so I code with what I am familar with.

Thanks again.

abousetta