Feb 19 2021 08:55 AM
Feb 19 2021 08:55 AM
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)
Feb 19 2021 06:41 PM - edited Feb 20 2021 05:35 AM
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.
Feb 23 2021 12:36 PM
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.
Feb 23 2021 02:14 PM - edited Feb 23 2021 02:15 PM
@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.