SOLVED

Error with IFS transferring data from one spreadsheet to another if conditions met

%3CLINGO-SUB%20id%3D%22lingo-sub-1563476%22%20slang%3D%22en-US%22%3EError%20with%20IFS%20transferring%20data%20from%20one%20spreadsheet%20to%20another%20if%20conditions%20met%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1563476%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%20I%20could%20use%20some%20help%20with%20the%20IFS%20formula.%26nbsp%3B%20Thanks%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20Excel%20as%20part%20of%20Microsoft%20Office%20Professional%20Plus%202013.%26nbsp%3B%20I%20have%20two%20spreadsheets%20and%20I%20am%20trying%20to%20pull%20data%20from%20a%20tab%20on%20one%20spreadsheet%20(DailyRollup.xlsx%2C%20the%20%22DailyExtract%22%20tab)%20to%20be%20shown%20on%20a%20different%20spreadsheet%20(Forecast.xlsx)%20if%20one%20of%20four%20criteria%20are%20met%2C%20otherwise%20leaving%20the%20field%20blank.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20cell%20in%20Column%20E%20of%20the%20source%20file%20either%20has%20one%20of%20four%20codes%20in%20it%3A%26nbsp%3B%20P%2C%20H%2C%20T%2C%20or%20S%2C%20or%20the%20field%20is%20blank%20if%20none%20of%20those%20apply.%26nbsp%3B%20The%20source%20cells%20are%20hard-coded%20with%20those%20letter%20codes.%26nbsp%3B%20The%20data%20I%20need%20to%20show%20in%20my%20%22Forecast.xlsx%22%20spreadsheet%20starting%20in%20Cell%20D3%20comes%20from%20the%20source%20document%20in%20Cell%20E1380%20is%20the%20first%20instance%20of%20the%20codes%20I%20am%20trying%20to%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20use%20the%20formula%20shown%20below%20in%20Cell%20D3%20of%20my%20Forecast.xlsx%20document%2C%20I%20get%20the%20%23NAME%3F%20error.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFS('%5BDailyRollup.xlsx%5DDailyExtract'!%24E1380%3D%22P%22%2C%22P%22%2C'%5BDailyRollup.xlsx%5DDailyExtract'!%24E1380%3D%22H%22%2C%22H%22%2C'%5BDailyRollup.xlsx%5DDailyExtract'!%24E1380%3D%22T%22%2C%22T%22%2C'%5BDailyRollup.xlsx%5DDailyExtract'!%24E1380%3D%22S%22%2C%22S%22%2CTRUE%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1563476%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1563542%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20with%20IFS%20transferring%20data%20from%20one%20spreadsheet%20to%20another%20if%20conditions%20met%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1563542%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F748242%22%20target%3D%22_blank%22%3E%40LPax11%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20IFS%20function%20is%20not%20available%20in%20Excel%202013%2C%20only%20in%20Excel%202019%20and%20Excel%20if%20Microsoft%20365%20(aka%20Office%20365).%20So%20you'll%20have%20to%20use%20nested%20IF%20functions%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(%5BDailyRollup.xlsx%5DDailyExtract!%24E1380%3D%22P%22%2C%22P%22%2CIF(%5BDailyRollup.xlsx%5DDailyExtract!%24E1380%3D%22H%22%2C%22H%22%2CIF(%5BDailyRollup.xlsx%5DDailyExtract!%24E1380%3D%22T%22%2C%22T%22%2CIF(%5BDailyRollup.xlsx%5DDailyExtract!%24E1380%3D%22S%22%2C%22S%22%2C%22%22))))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1563568%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20with%20IFS%20transferring%20data%20from%20one%20spreadsheet%20to%20another%20if%20conditions%20met%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1563568%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BThank%20you%20for%20that%2C%20the%20nested%20IF%20function%20worked%20just%20fine.%26nbsp%3B%20Appreciate%20your%20help%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1563606%22%20slang%3D%22en-US%22%3ERe%3A%20Error%20with%20IFS%20transferring%20data%20from%20one%20spreadsheet%20to%20another%20if%20conditions%20met%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1563606%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F748242%22%20target%3D%22_blank%22%3E%40LPax11%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(SUM(COUNTIF(%5BDailyRollup.xlsx%5DDailyExtract!%24E1380%2C%7B%22P%22%2C%22H%22%2C%22T%22%2C%22S%22))%2C%5BDailyRollup.xlsx%5DDailyExtract!%24E1380%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

 

Hello, I could use some help with the IFS formula.  Thanks in advance!

 

I am using Excel as part of Microsoft Office Professional Plus 2013.  I have two spreadsheets and I am trying to pull data from a tab on one spreadsheet (DailyRollup.xlsx, the "DailyExtract" tab) to be shown on a different spreadsheet (Forecast.xlsx) if one of four criteria are met, otherwise leaving the field blank. 

 

Each cell in Column E of the source file either has one of four codes in it:  P, H, T, or S, or the field is blank if none of those apply.  The source cells are hard-coded with those letter codes.  The data I need to show in my "Forecast.xlsx" spreadsheet starting in Cell D3 comes from the source document in Cell E1380 is the first instance of the codes I am trying to 

 

When I use the formula shown below in Cell D3 of my Forecast.xlsx document, I get the #NAME? error.  

 

=IFS('[DailyRollup.xlsx]DailyExtract'!$E1380="P","P",'[DailyRollup.xlsx]DailyExtract'!$E1380="H","H",'[DailyRollup.xlsx]DailyExtract'!$E1380="T","T",'[DailyRollup.xlsx]DailyExtract'!$E1380="S","S",TRUE,"")

 

3 Replies
best response confirmed by LPax11 (New Contributor)
Solution

@LPax11 

The IFS function is not available in Excel 2013, only in Excel 2019 and Excel if Microsoft 365 (aka Office 365). So you'll have to use nested IF functions:

 

=IF([DailyRollup.xlsx]DailyExtract!$E1380="P","P",IF([DailyRollup.xlsx]DailyExtract!$E1380="H","H",IF([DailyRollup.xlsx]DailyExtract!$E1380="T","T",IF([DailyRollup.xlsx]DailyExtract!$E1380="S","S",""))))

@Hans Vogelaar Thank you for that, the nested IF function worked just fine.  Appreciate your help

 

@LPax11 

As variant

=IF(SUM(COUNTIF([DailyRollup.xlsx]DailyExtract!$E1380,{"P","H","T","S")),[DailyRollup.xlsx]DailyExtract!$E1380,"")