Tricky formula structure

[fireslguk]

Hi the following is beyond my capability

5 seperate tabs

A drop down on each tab same cell location

Can be Tea Coffee or mix

So formula being

If any of the 5 cells have “Tea” then the result is Tea
If any of the 5 cells have “Coffee” then result Coffee
if any of the cells have a mix of Tea and Coffee makes result Mix

 

[jkpieterse]

Suppose the sheets are Sheet1 to Sheet5 and we're looking at cell A1. Then this formula should work (if I understood your rules correctly):

=LET(all,Sheet1!A1&Sheet2!A1&Sheet3!A1&Sheet4!A1&Sheet5!A1,hasCoffee,NOT(ISERROR(SEARCH("coffee",all))),hasTea,NOT(ISERROR(SEARCH("tea",all))),IF(LEN(all)=0,"Neither",IF(AND(hasTea,hasCoffee),"mix",IF(hasTea,"Tea","Coffee"))))

 

[fireslguk]

Didn’t work for me - I get error where says there’s a problem with this formula

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[fireslguk]

Suppose the sheets are Sheet1 to Sheet5 and we're looking at cell A1. Then this formula should work (if I understood your rules correctly):

=LET(all,Sheet1!A1&Sheet2!A1&Sheet3!A1&Sheet4!A1&Sheet5!A1,hasCoffee,NOT(ISERROR(SEARCH("coffee",all))),hasTea,NOT(ISERROR(SEARCH("tea",all))),IF(LEN(all)=0,"Neither",IF(AND(hasTea,hasCoffee),"mix",IF(hasTea,"Tea","Coffee"))))

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Apologies both - typo it works

 

[Fluff]

Can you please update your profile as requested. It helps us to help you.