Excel 2007 Nested IF Formula

[stef]

Hello All,
I am trying to get a formula in which the results are all dates plus a certain number of days. I am getting an error while trying to add the days. This is the formula

=IF(R3=CECs!,"10+CECs!F3",IF(CECs!R3=CECs!B4,"30+CECs!F3",IF(CECs!R3=CECs!B5,"30+CECs!F3",IF(R3=CECs!B6,"30+CECs!F3",IF(R3=CECs!B7,"30+CECs!F3",IF(R3=CECs!B8,"30+CECs!F3",IF(R3=CECs!B9,"21+CECs!F3",IF(R3=CECs!B10,"AWAITING",IF(R3=CECs!B11,"10+CECs!F3",IF(R3=CECs!B12,"AWAITING",IF(R3=CECs!B13,"80+CECs!F3")))))))))))

If the cell has a value that is equal to a cell in another spreadsheet in the same workbook then it will add a certain number of days to a cell with a date. The bold section is where I am starting to get the error. Here I am trying to add 10 days to a date which is in the cell CECs!F3. Most of the other returns are a certain number of days added to a date value in a specific cell. So again the main problem is how do I add a number of days to a fixed date. I am using Excel 2007 btw. If anyone can help me that would be great I've been trying to figure this out for a week now

 

[NARAYANK991]

Hi ,

The error is earlier than the point you have highlighted :

=IF(R3=CECs!,"10+CECs!F3",IF(CECs!R3=CECs!B4,"30+CECs!F3",IF(CECs!R3=CECs!B5,"30+CECs!F3",IF(R3=CECs!B6,"30+CECs!F3",IF(R3=CECs!B7,"30+CECs!F3",IF(R3=CECs!B8,"30+CECs!F3",IF(R3=CECs!B9,"21+CECs!F3",IF(R3=CECs!B10,"AWAITING",IF(R3=CECs!B11,"10+CECs!F3",IF(R3=CECs!B12,"AWAITING",IF(R3=CECs!B13,"80+CECs!F3")))))))))))

CECs is a worksheet tab name ; it has to be followed by a cell / range reference ; for example , in the following :

CECs!F3

F3 is the cell reference.

Narayan

 

[NARAYANK991]

Hi ,

Probably you meant to enter :

=IF(R3=CECs!B3,"10+CECs!F3",IF(R3=CECs!B4,"30+CECs!F3",IF(R3=CECs!B5,"30+CECs!F3",IF(R3=CECs!B6,"30+CECs!F3",IF(R3=CECs!B7,"30+CECs!F3",IF(R3=CECs!B8,"30+CECs!F3",IF(R3=CECs!B9,"21+CECs!F3",IF(R3=CECs!B10,"AWAITING",IF(R3=CECs!B11,"10+CECs!F3",IF(R3=CECs!B12,"AWAITING",IF(R3=CECs!B13,"80+CECs!F3")))))))))))

Narayan

 

[stef]

Neither worked. I got a circular reference error. I am wondering if there is probably is simpler formula solution.

So basically here's a breakdown of the scenario. If a cell from sheet A is equal to any cell in list of cell values on sheet 2 ( stipulated deadline time line eg 10 days from received date) ( which is part of a vlookup drop down menu for sheet 1) so there would be a range of deadlines that may be possible, then a certain number of days will be added to a cell which contains a date (received date) in sheet 1. The formula will be placed on sheet 1.

These are the deadlines on sheet 2 (vlookup dropdown menu for sheet 1)
So if the cell contains any of these values then the amount f days will be added to the received date on Sheet 1.
-
Ack. Letter due 10 working days from stamped received date
Notification of decision due 30 working days from dispatch of ack. Letter
Notification of decision due30 working days from stamped received date of latest FI response
Notification of decision due30 working days from stamped received date of latest FI response
Notification of decision due in 30 working days
Notification of decision due in 30 working days
Notification of draft TOR due in 21 working days
Awaiting agreement /modification request
Issue Final TOR within 10 days
Awaiting EIA submission
Notification of decision due in 80 working days


Did I make it clearer or more confusing. I was wondering if I should just type out the cell values in the formula instead of using the cell address in sheet2. It will make the formula quite lengthy but its worth a try?

 

[bosco_yip]

Maybe,

=IFERROR(INDEX({10;30;30;30;30;30;21;"NA";10;"NA";80},MATCH(R3,CECs!B3:B13,0))+CECs!F3,"AWAITING")

or,

=IFERROR(INDEX({10;30;30;30;30;30;21;"NA";10;"NA";80},MATCH(CECs!R3,CECs!B3:B13,0))+CECs!F3,"AWAITING")

Regards

 

[stef]

didn't work

 

[stef]

I tried

=IF(Q3=Ack. Letter due 10 working days from stamped received date,B3+10,IF(Q3=Notification of decision due 30 working days from dispatch of ack. Letter,F3+30,IF(Q3=Awaiting FI,Awaiting FI,IF(Q3=Notification of decision due30 working days from stamped received date of latest FI response,F3+30,IF(Q3=Notification of decision due in 30 working days,F3+30,IF(Q3=Notification of decision due in 30 working days,F3+30,IF(Q3=Notification of draft TOR due in 21 working days,F3+21,IF(Q3=Awaiting agreement /modification request,Awaiting agreement /modification request,IF(Q3=Issue Final TOR within 10 days,F3+10,IF(Q3=Awaiting EIA submission,Awaiting EIA submission,IF(Q3=Notification of decision due in 80 working days,F3+80)))))))))))

Also not working Probably I have something a bit off here...any suggestions?

 

[NARAYANK991]

Hi ,

Please upload your workbook.

Narayan

 

[stef]

Hi,
I have uploaded the worksheet.
So again i'm trying to figure out a formula for days left column in the CEC worksheet. You would have to use the Statutory deadline and respective status from the settings worksheet and date of status changed in the CEC spreadsheet to make the formula. I hope we can get something to work.

 

[NARAYANK991]

Hi ,

If we can add the days value to the lists already present in the Settings tab , then a simple VLOOKUP can retrieve the days figure based on the description given in column E or column Q.

My doubt is over when the dates in column B are to be used , and when the dates in column F are to be used. Can you clarify ?

Narayan

 

[stef]

Hi ,

If we can add the days value to the lists already present in the Settings tab , then a simple VLOOKUP can retrieve the days figure based on the description given in column E or column Q.

My doubt is over when the dates in column B are to be used , and when the dates in column F are to be used. Can you clarify ?

Hi Narayan,
It's only for cell B3
(Ack. Letter due 10 working days from stamped received date) on the settings sheet that the date in column B in the CEC sheet would be applicable. All the rest of options use the F column date.
I saw you just listed dates in the settings sheet in column C. I guess you'll be plugging that in to a formula to add to either the date in B or F to get the respective days left?
I realised you changed the extremely long IF function I had before for the Statutory deadline in column Q, Thats awesome! I guess this was an easier way thank you! I think I spent an entire week trying to figure out that formula as I am very new to Excel. Thanks again and looking foward to your response :D

 

[NARAYANK991]

Hi ,

See if this is acceptable.

Narayan

 

[stef]

Looks great but i forgot to mention that its workdays that we're counting, so not including Saturday and sundays in the days left calculation.

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[stef]

WONDERFUL!Looks great. Thank you so much!

 

[stef]

Hi,
Me againis there a way to populate another sheet (Timelines) in the same workbook, with data from the CEC spreadsheet automatically?
eg Every time I change the status of the file on the CEC spreadsheet, I would also need to change the date. Is there a way that this can be reflected on the Timeline spreadsheet with all the respective statuses and changes in dates? I opted for this second spreadsheet Timelines because I didn't want to put it in the CEC spreadsheet and make it look like a ton of information to confuse people.

I was also looking for an option to add a drop down calendar to select dates for the 'date of change status' column in the CEC spreadsheet but apparently I need to have Microsoft Date and Time Picker Control 6.0 installed. This might cause some problems as other people may not have that application and making the worksheet inaccessible to date changes I guess. Any other suggestions? I'm still looking to see what I could do just to make it easier. Thanks again. I've attached the spreadsheet for you.

 

[NARAYANK991]

Hi ,

I am not clear on which data from the tab named CECs will reflect in the tab named Timelines.

Can you fill up at least one row with data ?

Narayan

 

[stef]

Hello,
I have attached a new spreadsheet with the important columns highlighted.

So sorry if i was not clear. Let me elaborate. On the CEC sheet the colums that will be changing manually are highlighted in yellow. (CEC reference, Application current status, date of change in status and final determination. Note that not Not every CEC reference number will have all the time lines listed on the 'Timeline Status sheet" associated with it. Bearing this in mind I would like to have some way of tracking when the application current status changes on the CEC sheet for every CEC refernce number with the associated date of status change and final determination.
So basically the timeline status sheet is just an expansion of the dropdown list of the application current status on the CEC sheet and i would like to keep track of all the associated dates when the status changes.

Is this clearer?

 

[NARAYANK991]

Hi ,

Sorry , but things are not yet clear ; since you have filled in only 5 cells in the Timelines tab , I am not able to understand what will happen as far as the other cells are concerned.

The best thing would be if you could clarify the following :

1. Will CECs tab have only one row of data per CEC ?

2. What is the event based on which data is to be transferred to the Timelines tab ?

3. Will every entry in the CECs tab ultimately have a corresponding entry in the Timelines tab ?

4. What is the correspondence between each cell in the Timelines tab and a cell in the CECs tab ?

Timelines tab - Column A <----> CECs tab - Column C

----------------- Column B <----> ------------ Column D

----------------- Column C <----> ------------ Column B

----------------- Column D <----> ------------ Column U not equal to 0

You need to fill in this correspondence table for all the relevant columns in the Timelines tab.

Narayan

 

[stef]

okay let's figure this out.
1 Yes. Every CEC will only have one row of data.

2. Based on the change in 'application current status' on the CEC tab then this data along with the date in status change will be transferred to the TIMELINE tab.

Okay so to make things simpler we can just have data trasfers based on three columns in the TIMELINE tab. we can just use the

• CEC #
• Change in status ( which are the tabs listed out on the timeline sheet)
• Date of change in status.

So only when the status of the CEC and date changes column E and F on the CEC spreadsheet, then another date will be inputed into the TIMELINE tab with the associated date and status change.

3 No not all entries on the CEC tab will have a corresponding entry on the timeline tab only the three columns listed above

4 Timelines tab - Column A <----> CECs tab - Column C

----------------- Column B-M <----> ------------ Column E

 

[stef]

So I just thought about this.
Every CEC does not necessarily have all those status changes listed in the TIMELINE tab. And basically what I am trying to get is a representation of all the status changes and date changes per CEC so if there is an easier way to extract this data into the TIMELINE tab that would be great. So the TIMELINE tab does not have to have all those column headings. It could be populated in whichever way you think is easier once all the dates and status changes are accounted for. Easier? Or did I confuse you?

 

[stef]

Any luck?

 

[NARAYANK991]

Hi ,

Your workbook already has a Worksheet_Change event procedure in the CECs tab.

The data transfer from the CECs tab to the Timelines tab will need to make use of this.

Can you explain why this has been written , and whether I can overwrite it with new code ?

Narayan

 

[stef]

I believe that Worksheet_change was placed in there so I could select multiple entries in the Officer Assigned column in the CEC sheet.

 

[stef]

If there is no other option than to overwrite it for the data transfer to work then I guess you can go ahead. I will just make a note of it separately. Let me know. :D