ExcelBanter - View Single Post

Sky[_2_]

Thanks Bernard.

My email is .
If there is blank in my speadsheet is there a way to capture the month.

Thanks inadvance

Jan-09 Feb-09 Mar-09 Apr-09 May-09 Qty Ans
100 100 100 100 300 May Mar Feb
100 100 100 100 250 May Apr Feb
100 100 100 100 100 500 May Apr Mar Jan
100 100 100 100 100 Apr
100 100 100 50 Mar

"Bernard Liengme" wrote:

Send me an email (remove TRUENORTH.) and I will send a sample file
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Sky" wrote in message
...
Thanks Bernard.

Yes,I got the ans.
Could the ans also input in the year.

May I also know how to use Solver?

Thanks a lot

"Bernard Liengme" wrote:

With only the month names (Jan, Feb..) in row 1 (ie not year)
In G2 I used
=$E$1&IF(E2<F2,", "&$D$1,"")&IF(E2+D2<F2,", "&$C$1,"")&IF(E2+D2+C2<F2,",
"&$B$1,"")&IF(E2+D2+C2+B2<F2,", "&$A$1,"")

Another method would be to use Solver
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Sky" wrote in message
...
Is there a way or formula in the excel that could help to add the
nearest
month to get the figure for Column F.
The month added up can be greater than or equal to Column F.
Column A to E is the month.
Column F is the Qty.
Column G is the column that I wish to get the month.

Jan-09 Feb-09 Mar-09 Apr-09 May-09 Qty Ans
100 100 100 100 100 300 Mar,Apr and May-3
mth
to
get 300
100 100 100 100 100 250 Mar,Apr and May-3
mth
to
have 250
100 100 100 100 100 500 Jan to May-5 month
to
get
500
100 100 100 100 100 100 May-1 month to get
100
100 100 100 100 100 50 May-1 month to get
50