Cash forecast formula

[rehanahmad]

Hi
I got a workbook with two sheets. I want to populate one sheet automatically based on the figures (in a particular month column) entered on the other one . I also have the data with number of days of payment to be received/paid. So I want Excel to automatically enter the figures in relevant month taking account of the payment terms. Any help would be much appreciated.

 

[Robert Mika]

You may need VLOOKUP or INDEX/MATCH or maybe an array formula to pick up only instance of one month.

Could you post a sample of your data?

 

[rehanahmad]

Thanks, below is the sample data.
<TABLE style="WIDTH: 522pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=697 border=0><COLGROUP><COL style="WIDTH: 231pt; mso-width-source: userset; mso-width-alt: 11264" width=308><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 4681" width=128><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" span=3 width=87><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl93 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 231pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=308 height=20>A</TD><TD class=xl94 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 96pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=128>B</TD><TD class=xl95 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 65pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=87>C</TD><TD class=xl95 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 65pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=87>D</TD><TD class=xl95 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 65pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=87>E</TD></TR><TR style="HEIGHT: 21.75pt; mso-height-source: userset" height=29><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 231pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 21.75pt; BACKGROUND-COLOR: transparent" width=308 height=29></TD><TD class=xl88 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 96pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=128></TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=87>Forecast</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=87>Forecast</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=87>Forecast</TD></TR><TR style="HEIGHT: 26.25pt; mso-height-source: userset" height=35><TD class=xl75 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 231pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 26.25pt; BACKGROUND-COLOR: transparent" width=308 height=35></TD><TD class=xl89 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 96pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=128>Payment Terms</TD><TD class=xl78 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 65pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=87>Aug-11</TD><TD class=xl78 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=87>Sep-11</TD><TD class=xl78 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=87>Oct-11</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD class=xl79 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 19.5pt; BACKGROUND-COLOR: transparent" height=26></TD><TD class=xl89 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 96pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=128>(Days)</TD><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">£000s</TD><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">£000s</TD><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">£000s</TD></TR><TR style="HEIGHT: 21pt; mso-height-source: userset" height=28><TD class=xl84 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 21pt; BACKGROUND-COLOR: transparent" height=28>REVENUE</TD><TD class=xl88 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 96pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=128></TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl85 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>Applications Raised</TD><TD class=xl91 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 96pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=128>30</TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>350</TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>400</TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>550</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl85 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>Certified Applications</TD><TD class=xl88 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 96pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=128></TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a" align=right>0</TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a" align=right>0</TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a" align=right>0</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl86 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>TOTAL REVENUE</TD><TD class=xl88 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 96pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=128></TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>0</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD class=xl79 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 19.5pt; BACKGROUND-COLOR: transparent" height=26></TD><TD class=xl88 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 96pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=128></TD><TD class=xl87 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> </TD><TD class=xl87 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> </TD><TD class=xl87 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 21pt; mso-height-source: userset" height=28><TD class=xl84 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 21pt; BACKGROUND-COLOR: transparent" height=28>COSTS</TD><TD class=xl88 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 96pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=128></TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl85 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>Materials</TD><TD class=xl91 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 96pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=128>35</TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a" align=right>100</TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a" align=right>50</TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a" align=right>25</TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl85 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>Prelims</TD><TD class=xl91 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 96pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=128>35</TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a"> </TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a"> </TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a"> </TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl85 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>Other</TD><TD class=xl91 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 96pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=128>35</TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a"> </TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a"> </TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a"> </TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl85 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>Contingency</TD><TD class=xl91 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 96pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=128>35</TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a"> </TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a"> </TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a"> </TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl85 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>Sub-Contract</TD><TD class=xl91 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 96pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=128>35</TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a"> </TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a"> </TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a"> </TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl85 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>Hired Labour</TD><TD class=xl91 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 96pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=128>35</TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a"> </TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a"> </TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a"> </TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl85 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>Direct Labour Wages</TD><TD class=xl91 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 96pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=128>35</TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a"> </TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a"> </TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a"> </TD></TR><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD class=xl85 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" height=24>D&E Time Based</TD><TD class=xl91 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 96pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=128>35</TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a"> </TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a"> </TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #c2d69a"> </TD></TR></TBODY></TABLE>

above is input sheet and there is another sheet wehre I want automate the above data to appear in relevant month column based on payment terms in column B. Thanks very much for your help.

 

[Robert Mika]

How does the second spreadsheet look like?
Could you explain a little bit?

 

[rehanahmad]

The second sheet look exactly the same. The one i sent you I called input sheet and the second sheet I want to be automatcaaly populated in relevant column.
Thanks

 

[Robert Mika]

The second sheet look exactly the same. The one i sent you ...
Thanks

I have not received anything from you

 

[rehanahmad]

OK, let me explain it bit more.
I got a sheet which I sent you which has numbers manually punched in relevant month column.
The second spreadsheet I want a formula where I can pick up the number from first sheet based on which month the payment will fall taking accout of payment terms in Column B (e.g. 35). So if I enter a number say £100 in August 11 Column in sheet1, the formula in sheet 2 would place this £100 in September column by taking account of payment terms of 35days (i.e August+35 days= september). I hope it is much clearer.
I have been trying:
INDEX(sheet1l!$C19:$M19,,MATCH(MONTH(F$11-$B19),INDEX(MONTH(sheet1!$C$11:$M$11),),0)), but only problem is it picks up the wrong year at the end of the year.