You could use the following formula:
=IF(SUMIF($B$1:$B1;$B1;$A$1:$A1)>5000-$A2;$B1-1;$B1)
just put it on the B column (row 2 and bellow) and in the B1 cell input your
desired date. Then copy the formula down...
2009/6/19 Paul Schreiner <schreiner_p...@att.net>
> Please explain the date.
>
> is it there already? or added? added by? purpose?
>
> Paul
>
> ------------------------------
> *From:* wongsol <wong...@rogers.com>
> *To:* MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> *Sent:* Friday, June 19, 2009 12:58:10 PM
> *Subject:* $$Excel-Macros$$ Re: Loop through count and add date
>
>
> Hi Paul...I did away with the sort in the macro and fixed the error (i
> use excel 2003) The other part worked great.
> i guess next would be to insert the date in the adjacent cell and then
> loop through those that do not contain "x" and do the same. (no idea
> how to do this)
>
> Thanks for your help thus far
>
>
> On Jun 19, 10:06 am, Paul Schreiner
> <schreiner_p...@att..net<schreiner_p...@att.net>>
> wrote:
> > I'm sorry, but there are too many confusing statements here...
> > I understand that you have a column of numbers and you
> > want to add them up until your reach (without exceeding) 5000.
> > What does "(doesn't matter which order)" mean?
> > meaning that the numbers are not sorted in any way?
> > or that you don't CARE if they are sorted?
> > And... it sounds like once you determine which numbers
> > are used to reach the 5000, you want to enter a DATE next to them.
> > What date? where does it come from?
> >
> > And...
> > "working backwards from a predetermined date".
> > what does that mean? where does THIS "predetermined" date come from?
> > Do you mean SPECIFIED date?
> >
> > I threw together a quick macro that did a PART of what you're looking
> for,
> > but I have no idea how close it is to what you want.
> >
> > Sub Macro1()
> > Dim I, Ttl
> > Range("A1").Select
> > ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
> > ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add
> Key:=Range("A2:A1001" _
> > ), SortOn:=xlSortOnValues, Order:=xlDescending,
> DataOption:=xlSortNormal
> > With ActiveWorkbook.Worksheets("Sheet1").Sort
> > .SetRange Range("A1:A10001")
> > .Header = xlYes
> > .MatchCase = False
> > .Orientation = xlTopToBottom
> > .SortMethod = xlPinYin
> > .Apply
> > End With
> > Range("B2:B65000").ClearContents
> > Ttl = 0
> > For I = 2 To 10001
> > If Ttl + Cells(I, 1) <= 5000 Then
> > Ttl = Ttl + Cells(I, 1)
> > Cells(I, 2) = "X"
> > If Ttl = 5000 Then Exit For
> > End If
> > Next I
> > MsgBox Ttl
> > End Sub
> >
> > Paul
> >
> > ________________________________
> > From: wongsol <wong...@rogers.com>
> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > Sent: Thursday, June 18, 2009 2:54:03 PM
> > Subject: $$Excel-Macros$$ Loop through count and add date
> >
> > Hello:
> >
> > I'll try to explain as best I can.
> >
> > I have a spreadsheet with a column that have numeric values. What I
> > need to do is loop through these values to a sum no greater than 5000
> > (doesn't matter in which order); the tricky part that I have no idea
> > where to start is to enter a date in the adjacent cell of all affected
> > cells that comprised this sum. This date would be working backwards
> > from a predetermined date; something like =MIN(range)-1.
> >
> > So a column with values would have the following dates associated as
> > the sum is as close to 5000 but no more than 5000
> >
> > 191 15-Sep
> > 235 15-Sep
> > 328 15-Sep
> > 534 15-Sep
> > 112 15-Sep
> > 155 15-Sep
> > 173 15-Sep
> > 203 15-Sep
> > 238 15-Sep
> > 1 15-Sep
> > 14 15-Sep
> > 57 15-Sep
> > 347 15-Sep
> > 48 15-Sep
> > 257 15-Sep
> > 301 15-Sep
> > 307 15-Sep
> > 513 15-Sep
> > 386 15-Sep
> > 401 15-Sep
> > 57 15-Sep
> > 150 14-Sep
> > 52 15-Sep
> > 187 14-Sep
> > 77 14-Sep
> > 82 15-Sep
> > 2 14-Sep
> > 156 14-Sep
> > 270 14-Sep
> > 474 14-Sep
> > 3 14-Sep
> > 156 14-Sep
> > 277 14-Sep
> > 280 14-Sep
> > 215 14-Sep
> > 445 14-Sep
> > 885 14-Sep
> > 457 14-Sep
> > 414 14-Sep
> > 277 14-Sep
> >
> > ...any ideas?
> >
> > TIA
>
>
>
>
> >
>
--
Fabio Lemos
e-mail: flnle...@gmail.com
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
