Home

IFS function with sheet link

%3CLINGO-SUB%20id%3D%22lingo-sub-1069982%22%20slang%3D%22en-US%22%3EIFS%20function%20with%20sheet%20link%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1069982%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%3C%2FP%3E%3CP%3EIn%20an%20IFS%20function%20I%20want%20to%20have%20the%20return%20values%20from%20a%20different%20worksheet%20-%20but%20don't%20know%20whether%20this%20is%20not%20possible%20(no%20examples%20show%20this)%20or%20there%20is%20a%20simple%20novices%20error%20in%20my%20current%20attempt%20(which%20returns%20NAME%3F)%3C%2FP%3E%3CP%3E%3DIFS(G3%3D%E2%80%9CMale%20Hip%E2%80%9D%2CSheet3!%24R%2428%2CG3%3D%E2%80%9CMale%20Any%E2%80%9D%2CSheet3!%24P%2428%2CG3%3D%E2%80%9CFemale%20Hip%E2%80%9D%2CSheet3!%24Q%2428%2CG3%3D%E2%80%9CFemale%20Any%E2%80%9D%2CSheet3!%24O%2428)%3C%2FP%3E%3CP%3EThanks%20for%20help...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1069982%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1070049%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20function%20with%20sheet%20link%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1070049%22%20slang%3D%22en-US%22%3ENot%20sure%20which%20version%20of%20Excel%20you%20are%20using%2C%20I%20use%20office%202016%20on%20my%20Mac%20and%20the%20IFS%20function%20is%20not%20supported%20(and%20returns%20NAME%3F)%2C%20you%20will%20need%20to%20do%20nested%20IF%20statement%20to%20get%20it%20to%20work.%3CBR%20%2F%3E%3CBR%20%2F%3ERichard%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1070060%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20function%20with%20sheet%20link%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1070060%22%20slang%3D%22en-US%22%3EMany%20thanks%20Richard%2C%20didn't%20see%20that%20stated%20anywhere%2C%20so%20will%20try%20Nested%20IF%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1070062%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20function%20with%20sheet%20link%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1070062%22%20slang%3D%22en-US%22%3EJust%20checked%20the%20Microsoft%20web%20site%20where%20they%20have%20the%20following%20note%20under%20the%20IFS%20description%2C%3CBR%20%2F%3ENote%3A%20This%20feature%20is%20available%20on%20Windows%20or%20Mac%20if%20you%20have%20Office%202019%2C%20or%20if%20you%20have%20an%20Office%20365%20subscription.%20If%20you%20are%20an%20Office%20365%20subscriber%2C%20make%20sure%20you%20have%20the%20latest%20version%20of%20Office.%3CBR%20%2F%3E%3CBR%20%2F%3ERichard%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1070064%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20function%20with%20sheet%20link%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1070064%22%20slang%3D%22en-US%22%3EThanks%20I%20am%20using%20my%20work%20Office%20365%20which%20is%202016%2C%20so%20that's%20that.%20My%20first%20attempted%20nested%20IF%20doesn't%20work%20either%3CBR%20%2F%3E%3DIF(G3%3D%E2%80%9CMale%20Hip%E2%80%9D%2CSheet3!%24R%2428%2CIF(G3%3D%E2%80%9CMale%20Any%E2%80%9D%2CSheet3!%24P%2428%2CIF(G3%3D%E2%80%9CFemale%20Hip%E2%80%9D%2CSheet3!%24Q%2428%2CIF(G3%3D%E2%80%9CFemale%20Any%E2%80%9D%2CSheet3!%24O%2428))))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1070103%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20function%20with%20sheet%20link%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1070103%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F491426%22%20target%3D%22_blank%22%3E%401orlando%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20too%20had%20the%20same%20problem%20with%20your%20IF%20statement%2C%20ended%20up%20redoing%20it%20(but%20looks%20the%20same)%2C%20have%20attached%20what%20I%20did%20for%20your%20info.%20I%20expect%20someone%20smarter%20than%20us%20will%20have%20an%20answer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERichard%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1070114%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20function%20with%20sheet%20link%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1070114%22%20slang%3D%22en-US%22%3EAgain%20many%20thanks%20richard.%20It%20won't%20be%20difficult%20to%20find%20someone%20smarter%20than%20me!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1070127%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20function%20with%20sheet%20link%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1070127%22%20slang%3D%22en-US%22%3EJust%20been%20playing%20around%20with%20your%20IF%20statement%2C%20Appears%20that%20if%20you%20retype%20all%20of%20the%20G3%3D%22...%22%20references%20then%20the%20formula%20works.%20For%20some%20reason%20it%20just%20didn't%20like%20that%20part%20of%20the%20formula.%3CBR%20%2F%3E%3CBR%20%2F%3ERichard%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1070224%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20function%20with%20sheet%20link%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1070224%22%20slang%3D%22en-US%22%3EYes%20tried%20that%20and%20then%20accepted%20a%20suggestion%20though%20i%20couldn't%20see%20the%20difference%20to%20what%20i%20had%20!%20and%20it%20works!%20many%20thanks%20for%20your%20effort.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1070226%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20function%20with%20sheet%20link%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1070226%22%20slang%3D%22en-US%22%3Efor%20the%20record%3CBR%20%2F%3E%3DIF(G3%3D%22Male%20Hip%22%2CSheet3!%24R%2428%2CIF(G3%3D%22Male%20Any%22%2CSheet3!P28%2CIF(G3%3D%22Female%20Hip%22%2CSheet3!%24Q%2428%2CIF(G3%3D%22Female%20Any%22%2CSheet3!O28))))%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1070890%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20function%20with%20sheet%20link%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1070890%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F491426%22%20target%3D%22_blank%22%3E%401orlando%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20a%20comment%2C%20IFS()%20shall%20work%20on%20Mac%20same%20way%20as%20on%20Windows%2C%20i.e.%20only%20for%20Office%20365%20subscribers%20or%20on%20Excel%202019%20for%20Mac.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23NAME!%20error%20could%20be%20not%20only%20since%20function%20doesn't%20exist%2C%20but%20as%20well%20if%20within%20formula%20appears%20curly%20quote%20character%26nbsp%3B%3CSPAN%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3E%E2%80%9D%3C%2FFONT%3E%3C%2FSTRONG%3E%20instead%20of%20straight%20quote%20%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E%22%3C%2FSTRONG%3E%3C%2FFONT%3E.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1070916%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20function%20with%20sheet%20link%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1070916%22%20slang%3D%22en-US%22%3ESergei%20..wow%20i%20had%20no%20idea%20that%20the%20quote%20characters%20were%20different%20and%20have%20never%20seen%20this%20mentioned%20but%20now%20it%20explains%20why%20i%20couldn't%20see%20how%20the%20suggestion%20differed%20from%20my%20now..%20many%20thanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1070933%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20function%20with%20sheet%20link%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1070933%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F491426%22%20target%3D%22_blank%22%3E%401orlando%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20they%20have%20different%20meaning%20and%20quite%20often%20straight%20quote%20is%20converted%20into%20curly%20one%20if%20copy%2Fpaste%20formula%20from%20the%20web.%20Just%20be%20careful%20with%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1070972%22%20slang%3D%22en-US%22%3ERe%3A%20IFS%20function%20with%20sheet%20link%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1070972%22%20slang%3D%22en-US%22%3Eexactly%20what%20i%20had%20done!%20merci%20beaucoup!%3C%2FLINGO-BODY%3E
1orlando
Occasional Contributor

Hi all

In an IFS function I want to have the return values from a different worksheet - but don't know whether this is not possible (no examples show this) or there is a simple novices error in my current attempt (which returns NAME?)

=IFS(G3=“Male Hip”,Sheet3!$R$28,G3=“Male Any”,Sheet3!$P$28,G3=“Female Hip”,Sheet3!$Q$28,G3=“Female Any”,Sheet3!$O$28)

Thanks for help...

13 Replies
Highlighted
Not sure which version of Excel you are using, I use office 2016 on my Mac and the IFS function is not supported (and returns NAME?), you will need to do nested IF statement to get it to work.

Richard
Highlighted
Many thanks Richard, didn't see that stated anywhere, so will try Nested IF
Highlighted
Just checked the Microsoft web site where they have the following note under the IFS description,
Note: This feature is available on Windows or Mac if you have Office 2019, or if you have an Office 365 subscription. If you are an Office 365 subscriber, make sure you have the latest version of Office.

Richard
Highlighted
Thanks I am using my work Office 365 which is 2016, so that's that. My first attempted nested IF doesn't work either
=IF(G3=“Male Hip”,Sheet3!$R$28,IF(G3=“Male Any”,Sheet3!$P$28,IF(G3=“Female Hip”,Sheet3!$Q$28,IF(G3=“Female Any”,Sheet3!$O$28))))
Highlighted

Hi @1orlando 

 

I too had the same problem with your IF statement, ended up redoing it (but looks the same), have attached what I did for your info. I expect someone smarter than us will have an answer.

 

Richard

Highlighted
Again many thanks richard. It won't be difficult to find someone smarter than me!
Highlighted
Just been playing around with your IF statement, Appears that if you retype all of the G3="..." references then the formula works. For some reason it just didn't like that part of the formula.

Richard
Highlighted
Yes tried that and then accepted a suggestion though i couldn't see the difference to what i had ! and it works! many thanks for your effort.
Highlighted
for the record
=IF(G3="Male Hip",Sheet3!$R$28,IF(G3="Male Any",Sheet3!P28,IF(G3="Female Hip",Sheet3!$Q$28,IF(G3="Female Any",Sheet3!O28))))
Highlighted

@1orlando 

As a comment, IFS() shall work on Mac same way as on Windows, i.e. only for Office 365 subscribers or on Excel 2019 for Mac.

 

#NAME! error could be not only since function doesn't exist, but as well if within formula appears curly quote character  instead of straight quote ".

Highlighted
Sergei ..wow i had no idea that the quote characters were different and have never seen this mentioned but now it explains why i couldn't see how the suggestion differed from my now.. many thanks
Highlighted

@1orlando 

Yes, they have different meaning and quite often straight quote is converted into curly one if copy/paste formula from the web. Just be careful with this.

Highlighted
exactly what i had done! merci beaucoup!
Related Conversations
Make Share function in Edge more useful
HotCakeX in Discussions on
2 Replies
Outlook meeting hyperlink issue
Kate Bowling in Outlook on
0 Replies
All New Microsoft Launcher (Preview)
HotCakeX in Discussions on
3 Replies
function talking to table storage
donquijote in Compute on
0 Replies