trick formula help please

L

Lansing9999

New Member
Joined
Feb 2, 2011
Messages
10
Here is what I am trying to do. I have test cases that I need to monitor for actual date action taken and the calculated date action needs to be taken. I have a summary tab that lists the test case and i have one column that will look at the actual dates work performed tab and return the last cell with data. What I need to do now is for the same test look to my calculated date completition tab and return the next value in after the last action date from the actual date page ( do if you will, the next action due based on the last entered action (actual tab). You will see that the columns have numbers on top that represent the days between actions. I need to return, as the next action date from the calculated date tab based on the last action date that had an entry on my actual date tab.

I hope this makes sense, but if not let me know and I will try to provide a more concrete example.

Excel Workbook
CDEFGHIJKLM
3Days to next action30302418521848
4Nametypeinv numdatedate enteredaction 1action 2action 3action 4action 5action 6
5test 1a40644813-Dec-201012-Jan-201111-Feb-20117-Mar-201125-Mar-201116-May-20113-Jun-201121-Jul-2011
6test 2b41325125-Feb-201127-Mar-201126-Apr-201120-May-20117-Jun-201129-Jul-201116-Aug-20113-Oct-2011
7test 3c40841011-Jul-201010-Aug-20109-Sep-20103-Oct-201021-Oct-201012-Dec-201030-Dec-201016-Feb-2011
8test 4d41360017-Jan-201116-Feb-201118-Mar-201111-Apr-201129-Apr-201120-Jun-20118-Jul-201125-Aug-2011
9test 5e41401710-Feb-201112-Mar-201111-Apr-20115-May-201123-May-201114-Jul-20111-Aug-201118-Sep-2011
Calculated due date
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Hi,

If i understood correctly, maybe this

Summary Sheet
I assumed you have already populated column B data in some way (Action Taked)

<TABLE style="WIDTH: 175pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=233><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #c5d9f1; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>Test</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 65pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=87>ActionTaked</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5d9f1; WIDTH: 62pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=82>NextAction</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>test 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>16-mai-2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>03-jun-2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>test 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>26-abr-2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>20-mai-2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>test 3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>16-fev-2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Finished</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>test 4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>29-abr-2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>20-jun-2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>test 5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>05-mai-2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>23-mai-2011</TD></TR></TBODY></TABLE>

Formula in C2
=IFERROR(INDEX(INDEX('Calculated Due Date'!$C$5:$M$9,MATCH(A2,'Calculated Due Date'!$C$5:$C$9,0),0),MATCH(B2,INDEX('Calculated Due Date'!$C$5:$M$9,MATCH(A2,'Calculated Due Date'!$C$5:$C$9,0),0),0)+1),"Finished")

HTH

M.
 
Upvote 0
Quick question. I have put an example below and I am not sure how to change the formula to make this happen

Sheet3

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 55px"><COL style="WIDTH: 77px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"><COL style="WIDTH: 79px"><COL style="WIDTH: 195px"><COL style="WIDTH: 82px"><COL style="WIDTH: 82px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff">Case</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff">265 Date</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff">160 Date</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff">Last action</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffff">Next action</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center">413251</TD><TD style="TEXT-ALIGN: center">16-Jan-2012</TD><TD style="TEXT-ALIGN: center">3-Oct-2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial Unicode MS; COLOR: #333333">3/25/11</TD><TD> </TD><TD>this lives on the summary sheet</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center">408410</TD><TD style="TEXT-ALIGN: center">1-Jun-2011</TD><TD style="TEXT-ALIGN: center">16-Feb-2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial Unicode MS; COLOR: #333333">6/7/11</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center">413600</TD><TD style="TEXT-ALIGN: center">8-Dec-2011</TD><TD style="TEXT-ALIGN: center">25-Aug-2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Arial Unicode MS; COLOR: #333333">10/21/10</TD><TD> </TD><TD>I need for the formula to look at the last action by line by case</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>and bring me the next scheduled due date from the calculated date tab</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">action</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">id</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">date</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">test1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">test2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">test3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">test4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">test5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">test6</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">test7</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: center">Action 1</TD><TD style="TEXT-ALIGN: center">406448</TD><TD style="TEXT-ALIGN: center">13-Dec-2010</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">12-Jan-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">11-Feb-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">7-Mar-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">25-Mar-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">16-May-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">3-Jun-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">21-Jul-2011</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: center">Action 2</TD><TD style="TEXT-ALIGN: center">413251</TD><TD style="TEXT-ALIGN: center">25-Feb-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">27-Mar-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">26-Apr-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">20-May-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">7-Jun-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">29-Jul-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">16-Aug-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">3-Oct-2011</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: center">Action 3</TD><TD style="TEXT-ALIGN: center">408410</TD><TD style="TEXT-ALIGN: center">11-Jul-2010</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">10-Aug-2010</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">9-Sep-2010</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">3-Oct-2010</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">21-Oct-2010</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">12-Dec-2010</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">30-Dec-2010</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">16-Feb-2011</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: center">Action 4</TD><TD style="TEXT-ALIGN: center">413600</TD><TD style="TEXT-ALIGN: center">17-Jan-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">16-Feb-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">18-Mar-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">11-Apr-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">29-Apr-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">20-Jun-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">8-Jul-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">25-Aug-2011</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: center">Action 5</TD><TD style="TEXT-ALIGN: center">414017</TD><TD style="TEXT-ALIGN: center">10-Feb-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">12-Mar-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">11-Apr-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">5-May-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">23-May-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">14-Jul-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">1-Aug-2011</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">18-Sep-2011</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD> </TD><TD> </TD><TD> </TD><TD>this lives on a sheet called calculated due date</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C4</TD><TD>=IF('Calculated due date'!E6="","",'Calculated due date'!E6)</TD></TR><TR><TD>D4</TD><TD>=IF('Calculated due date'!H6="","",'Calculated due date'!H6+265)</TD></TR><TR><TD>E4</TD><TD>=IF('Calculated due date'!H6="","",'Calculated due date'!H6+160)</TD></TR><TR><TD>C5</TD><TD>=IF('Calculated due date'!E7="","",'Calculated due date'!E7)</TD></TR><TR><TD>D5</TD><TD>=IF('Calculated due date'!H7="","",'Calculated due date'!H7+265)</TD></TR><TR><TD>E5</TD><TD>=IF('Calculated due date'!H7="","",'Calculated due date'!H7+160)</TD></TR><TR><TD>C6</TD><TD>=IF('Calculated due date'!E8="","",'Calculated due date'!E8)</TD></TR><TR><TD>D6</TD><TD>=IF('Calculated due date'!H8="","",'Calculated due date'!H8+265)</TD></TR><TR><TD>E6</TD><TD>=IF('Calculated due date'!H8="","",'Calculated due date'!H8+160)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
 
Upvote 0
Hi,

This is a different layout. Could you, please, explain what mean 265 Date and 160 Date and the expected result for each Case in your example?

I'm confused :confused:

M.
 
Upvote 0
Lansing9999

Not sure if you are aware that you can use the ‘Analyse range (Forum)’ field near the top left of the Excel jeanie screen to restrict the number of formulas generated. In that field, you can use Ctrl+Click/Drag to select multiple disjoint ranges if required. There is generally no need to display multiple formulas that are basically the same, it just fills up the board.
 
Upvote 0
Thank you both for you information. I was not aware of the restrict range option. I am new to the board and appreciate any information of how best to use it.

The 265 number and other numbers are milestones, or days from the start date that actions must be completed or considered late. That information is strictly for the user info only.

I hope this answers your question, but if not then please let me know.

Thanks again for the assistance.
 
Upvote 0
Hi Lansing9999,

What is the expected result for Case 413251 (lastAction = 3/25/11)?
27-Mar-2011 or 26-Apr-2001 or something different?

Same question for the others two Cases.

Please, clarify

M.
 
Upvote 0
Hello,
Here is what I am trying to do. In the example here the last action for case 1 was 2/11/11 for action 2. I want to return the date from the calculated date tab for the next action (action 3). The same will apply for each line.Actual date of action

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 85px"><COL style="WIDTH: 93px"><COL style="WIDTH: 80px"><COL style="WIDTH: 110px"><COL style="WIDTH: 97px"><COL style="WIDTH: 108px"><COL style="WIDTH: 134px"><COL style="WIDTH: 100px"><COL style="WIDTH: 87px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">Case ID</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">Discovery date</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">action 1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">action 2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">action 3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">action 4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">action 5</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">action 6</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc">action 7</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center">406448</TD><TD style="TEXT-ALIGN: center">13-Dec-2010</TD><TD style="TEXT-ALIGN: center">12-Jan-2011</TD><TD style="TEXT-ALIGN: center">11-Feb-2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center">413251</TD><TD style="TEXT-ALIGN: center">2/25/2011</TD><TD style="TEXT-ALIGN: center">27-Mar-2011</TD><TD style="TEXT-ALIGN: center">26-Apr-2011</TD><TD style="TEXT-ALIGN: center">20-May-2011</TD><TD style="TEXT-ALIGN: center">7-Jun-2011</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center">408410</TD><TD style="TEXT-ALIGN: center">7/11/2010</TD><TD style="TEXT-ALIGN: center">10-Aug-2010</TD><TD style="TEXT-ALIGN: center">9-Sep-2010</TD><TD style="TEXT-ALIGN: center">3-Oct-2010</TD><TD style="TEXT-ALIGN: center">21-Oct-2010</TD><TD style="TEXT-ALIGN: center">12-Dec-2010</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center">413600</TD><TD style="TEXT-ALIGN: center">1/17/2011</TD><TD style="TEXT-ALIGN: center">16-Feb-2011</TD><TD style="TEXT-ALIGN: center">18-Mar-2011</TD><TD style="TEXT-ALIGN: center">11-Apr-2011</TD><TD style="TEXT-ALIGN: center">29-Apr-2011</TD><TD style="TEXT-ALIGN: center">20-Jun-2011</TD><TD style="TEXT-ALIGN: center">8-Jul-2011</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center">414017</TD><TD style="TEXT-ALIGN: center">2/10/2011</TD><TD style="TEXT-ALIGN: center">3/12/11</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
AVERAGEIF TROUBLES
Replies
3
Views
78
AhoyNC
A
G
  • Question Question
Copy some specific columns whose header is merge
2 3
Replies
26
Views
671
Joe4
Joe4
D
  • Locked
  • Question Question
Could you please write a code for the following scenario?
Replies
1
Views
179
Fluff
Fluff
nburaq
  • Solved
Conditional Formatting last n rows as data entered
Replies
6
Views
357
Fluff
Fluff
J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
978
Cubist
Cubist

Forum statistics

Threads
1,224,609
Messages
6,179,879
Members
452,948
Latest member
Dupuhini

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top