IF Statement FORMULA Issue

%3CLINGO-SUB%20id%3D%22lingo-sub-2149730%22%20slang%3D%22en-US%22%3EIF%20Statement%20FORMULA%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2149730%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20hoping%20that%20someone%20here%20might%20be%20able%20to%20help%20me%20as%20I%20can%20not%20get%20my%20IF%20formula%20to%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20have%20two%20separate%20work%20books.%20I%20want%20to%20take%20data%20from%20first%20workbook%20and%20put%20it%20into%20the%20second%20workbook.%20Problem%20is%20that%20each%20month%20the%20amount%20I%20am%20bringing%20over%20changes.%20It%20allows%20me%20to%20add%20the%20first%20IF%20statement%20below%20but%20then%20when%20I%20try%20to%20add%20the%20next%20argument%20with%20%22AUG%22%20it%20will%20not%20accept%20it%20it%20keeps%20telling%20me%20the%20argument%20is%20incorrect%20but%20I%20have%20everything%20the%20same%20as%20the%20first%20statement.%20Tried%20putting%20an%20apostrophe%20before%20the%20%3D%20in%20the%20statement%20but%20that%20did%20not%20work.%20Also%20not%20sure%20I%20can%20nest%2012%20IF%20statements%20anyway.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20hoping%20to%20go%20into%20the%20workbook%20monthly%20and%20change%20the%20Month%20(say%20from%20Jan%20to%20Feb%20and%20then%20the%20sheet%20would%20automatically%20go%20to%20the%20other%20workbook%20and%20pick%20up%20the%20correct%20info%20from%20the%20correct%20cell%20(12%20months).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyone%20know%20what%20I%20am%20doing%20wrong%20or%20if%20there%20is%20an%20different%20formula%20to%20use%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(%24BB%2411%3D%22JULY%22%2C'%5BFY2021%20-%20Cash%20Flow%20Master%20schedule%20-%20WIP.xls%5DFY21%20BCEC%20Proj%20CF'!K14%2C0)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2151105%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20FORMULA%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2151105%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F972451%22%20target%3D%22_blank%22%3E%40Claire6061%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20you%20looking%20to%20get%20just%20one%20cell%2C%20one%20piece%20of%20data%2C%20from%20that%20second%20sheet%3F%20Does%20the%20second%20sheet%20get%20renewed%20each%20month%2C%20with%20just%20one%20month's%20data%2C%20or%20is%20it%20YTD%2C%20or%20something%20else%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20may%20be%20other%20questions%20I%20should%20be%20asking%3B%20essentially%20I'm%20hoping%20you%20can%20give%20a%20more%20complete%20picture.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20sheet%20that%20I%20connect%20with%20another%20(two%20others%20actually%2C%20on%20a%20daily%20basis%3B%20many%20more%20on%20an%20ad%20hoc%20basis).%20I%20generally%20use%20the%20FILTER%20function%20(a%20new%20function%20only%20available%20in%20the%20most%20recent%20versions%20of%20Excel)%2C%20and%20retrieve%20essentially%20the%20entirety%20of%20those%20secondary%20sheets.%20I%20can%20then%20do%20within%20the%20primary%20sheet%20whatever%20I%20want%20with%20the%20data%20from%20the%20secondary.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhether%20that%20would%20work%20with%20yours%2C%20I%20don't%20know.%20Frankly%20it's%20hard%20to%20tell%20exactly%20what%20you're%20trying%20to%20do.%20So%20if%20you%20could%20give%20a%20more%20complete%20description%2C%20that%20would%20be%20helpful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20could%20attach%20copies%20of%20the%20spreadsheets%20in%20question%20(devoid%20of%20confidential%20data)%20that%20would%20be%20even%20more%20helpful.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2160283%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Statement%20FORMULA%20Issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2160283%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20reply%20it%20took%20me%20a%20while%20but%20I%20finally%20figured%20it%20out.%20I%20had%20to%20nestle%2060%20IF%20statements%20into%20some%20of%20the%20cells%20to%20make%20them%20work%20which%20seems%20a%20bit%20more%20complicated%20than%20it%20should%20have%20been%20but%20it%20works.%3C%2FP%3E%3CP%3EYes%20the%20data%20changes%20each%20month%20by%20a%20cell%20which%20was%20the%20main%20cause%20of%20the%20problem.%3C%2FP%3E%3CP%3EAgain%20thanks%20so%20much%20for%20offering%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EClaire%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am hoping that someone here might be able to help me as I can not get my IF formula to work.

 

So I have two separate work books. I want to take data from first workbook and put it into the second workbook. Problem is that each month the amount I am bringing over changes. It allows me to add the first IF statement below but then when I try to add the next argument with "AUG" it will not accept it it keeps telling me the argument is incorrect but I have everything the same as the first statement. Tried putting an apostrophe before the = in the statement but that did not work. Also not sure I can nest 12 IF statements anyway.

 

I was hoping to go into the workbook monthly and change the Month (say from Jan to Feb and then the sheet would automatically go to the other workbook and pick up the correct info from the correct cell (12 months).

 

Anyone know what I am doing wrong or if there is an different formula to use 

 

=IF($BB$11="JULY",'[FY2021 - Cash Flow Master schedule - WIP.xls]FY21 BCEC Proj CF'!K14,0)

3 Replies

@Claire6061 

 

Are you looking to get just one cell, one piece of data, from that second sheet? From the formula you've posted, it would appear that you're trying to retrieve whatever is in cell K14 of the secondary sheet. Does the second sheet get renewed each month, with just one month's data, or is it YTD, or something else?

 

There may be other questions I should be asking; essentially I'm hoping you can give a more complete picture.

 

I have a sheet that I connect with another (two others actually, on a daily basis; many more on an ad hoc basis). I generally use the FILTER function (a new function only available in the most recent versions of Excel), and retrieve essentially the entirety of those secondary sheets. I can then do within the primary sheet whatever I want with the data from the secondary.

 

Whether that would work with yours, I don't know. Frankly it's hard to tell exactly what you're trying to do. So if you could give a more complete description, that would be helpful.

 

If you could attach copies of the spreadsheets in question (devoid of confidential data) that would be even more helpful.

@mathetes 

 

Thanks for your reply it took me a while but I finally figured it out. I had to nestle 60 IF statements into some of the cells to make them work which seems a bit more complicated than it should have been but it works.

Yes the data changes each month by a cell which was the main cause of the problem.

Again thanks so much for offering help.

 

Claire

@Claire6061    wrote: I finally figured it out. I had to nestle 60 IF statements into some of the cells to make them work which seems a bit more complicated than it should have been 

 

It is absolutely more complicated than it needs to be (I say that confidently, without even seeing it); especially if you're saying, as you seem to be saying, that you have 60 IFs nested in multiple cells. That's the kind of convoluted statement that the textbooks warn against. Fun to write when they work but nightmares to maintain, horrendous when passed on to somebody else.

 

So if you'd like to look into methods to simplify, let this site know. One thing you might want to investigate is the IFS statement. https://exceljet.net/excel-functions/excel-ifs-function

 

And depending on what conditions are being tested, the new LET function might help shorted and make more intelligible.