is this even possible?
in [General]
|
Prev: Excel file slow
Next: excell
From: Otto Moehrbach on 14 Mar 2010 13:34 You didn't say anything about what Excel should/could use as the trigger to fire the automation. For now I'll assume it's any numerical entry made in Column B below row 1. Excel can do lots for you with this but it must be told every little detail so I have a question. Question: Is every entry in Column B ALWAYS 1000 or greater? If not, and the sum of that entry and the "leftover" (53 in your 3/10 example) is less than 1000, what do you want to happen? Another question: What version of Excel are you using? Otto "Bowmanator" <Bowmanator(a)discussions.microsoft.com> wrote in message news:39C62505-FCCE-4767-8D78-90481D639285(a)microsoft.com... > > what i'm doing here is trying to keep track of how many full truck loads i > can ship before i run out of product that was ran on a certain day. then > the > next load will be a split load. > > ex: on 3/10 we ran 2053 cases, that allows me to ship 2 full loads of that > days run leaving 53 cases left, > on 3/11 we run 1019 so in order to ship a full load of 1,000 i'll need the > 53 cases left over from 3/10 plus 947 caes from 3/11 to equal 1000. then > it > shows a balance of 72 let over from 3/11 > > hope this sounds better > > thanks > "Otto Moehrbach" wrote: > >> What is the sequence of events? You say you want to automate this. What >> comes first? Detail what you enter and then what you want Excel to do >> (the >> automate part). Then what happens next? The next date row maybe? >> In short, Excel is dumb and it needs to know what is the trigger to make >> it >> automate something. Maybe any entry in Column B? It might help if you >> provide the series of steps you must do when you do this manually. >> Remember >> that you are talking to people who know nothing about what you have or >> want >> to have. HTH Otto >> >> "Bowmanator" <Bowmanator(a)discussions.microsoft.com> wrote in message >> news:66821ED8-2133-4A80-B286-702D0F01558B(a)microsoft.com... >> > i'm working on a shipping log that i do now by hand and would like to >> > automate if possible. every load equals 1,000 and alot of the loads are >> > split >> > loads. >> > >> > a b c d e >> > 1 date amount previous current total >> > 2 3/10 2053 1000 >> > 3 >> > 1000 >> > 4 >> > 53 >> > 5 3/11 1019 53 947 1000 >> > 6 72 >> > 7 3/12 3000 72 928 1000 >> > 8 >> > 1000 >> > 9 >> > 1000 >> > 10 72 >> > >> > in this example it shows what i need to equal 1,000 and if there's over >> > 1,000 left of that run i'd like the next line to show 1,000 and so on >> > until i >> > can't make a full load of 1,000. >> > >> > hope this sounds right >> > thanks >> >> . >>
From: Bowmanator on 14 Mar 2010 21:20 you assumed everything correct, and everything entered in b2 and below will be greater then 1,000. "Otto Moehrbach" wrote: > You didn't say anything about what Excel should/could use as the trigger to > fire the automation. For now I'll assume it's any numerical entry made in > Column B below row 1. Excel can do lots for you with this but it must be > told every little detail so I have a question. > Question: Is every entry in Column B ALWAYS 1000 or greater? If not, and > the sum of that entry and the "leftover" (53 in your 3/10 example) is less > than 1000, what do you want to happen? > Another question: What version of Excel are you using? Otto > > "Bowmanator" <Bowmanator(a)discussions.microsoft.com> wrote in message > news:39C62505-FCCE-4767-8D78-90481D639285(a)microsoft.com... > > > > what i'm doing here is trying to keep track of how many full truck loads i > > can ship before i run out of product that was ran on a certain day. then > > the > > next load will be a split load. > > > > ex: on 3/10 we ran 2053 cases, that allows me to ship 2 full loads of that > > days run leaving 53 cases left, > > on 3/11 we run 1019 so in order to ship a full load of 1,000 i'll need the > > 53 cases left over from 3/10 plus 947 caes from 3/11 to equal 1000. then > > it > > shows a balance of 72 let over from 3/11 > > > > hope this sounds better > > > > thanks > > "Otto Moehrbach" wrote: > > > >> What is the sequence of events? You say you want to automate this. What > >> comes first? Detail what you enter and then what you want Excel to do > >> (the > >> automate part). Then what happens next? The next date row maybe? > >> In short, Excel is dumb and it needs to know what is the trigger to make > >> it > >> automate something. Maybe any entry in Column B? It might help if you > >> provide the series of steps you must do when you do this manually. > >> Remember > >> that you are talking to people who know nothing about what you have or > >> want > >> to have. HTH Otto > >> > >> "Bowmanator" <Bowmanator(a)discussions.microsoft.com> wrote in message > >> news:66821ED8-2133-4A80-B286-702D0F01558B(a)microsoft.com... > >> > i'm working on a shipping log that i do now by hand and would like to > >> > automate if possible. every load equals 1,000 and alot of the loads are > >> > split > >> > loads. > >> > > >> > a b c d e > >> > 1 date amount previous current total > >> > 2 3/10 2053 1000 > >> > 3 > >> > 1000 > >> > 4 > >> > 53 > >> > 5 3/11 1019 53 947 1000 > >> > 6 72 > >> > 7 3/12 3000 72 928 1000 > >> > 8 > >> > 1000 > >> > 9 > >> > 1000 > >> > 10 72 > >> > > >> > in this example it shows what i need to equal 1,000 and if there's over > >> > 1,000 left of that run i'd like the next line to show 1,000 and so on > >> > until i > >> > can't make a full load of 1,000. > >> > > >> > hope this sounds right > >> > thanks > >> > >> . > >> > . >
From: Bowmanator on 14 Mar 2010 21:27 forgot, i'm using office xp pro "Bowmanator" wrote: > you assumed everything correct, and everything entered in b2 and below will > be greater then 1,000. > > "Otto Moehrbach" wrote: > > > You didn't say anything about what Excel should/could use as the trigger to > > fire the automation. For now I'll assume it's any numerical entry made in > > Column B below row 1. Excel can do lots for you with this but it must be > > told every little detail so I have a question. > > Question: Is every entry in Column B ALWAYS 1000 or greater? If not, and > > the sum of that entry and the "leftover" (53 in your 3/10 example) is less > > than 1000, what do you want to happen? > > Another question: What version of Excel are you using? Otto > > > > "Bowmanator" <Bowmanator(a)discussions.microsoft.com> wrote in message > > news:39C62505-FCCE-4767-8D78-90481D639285(a)microsoft.com... > > > > > > what i'm doing here is trying to keep track of how many full truck loads i > > > can ship before i run out of product that was ran on a certain day. then > > > the > > > next load will be a split load. > > > > > > ex: on 3/10 we ran 2053 cases, that allows me to ship 2 full loads of that > > > days run leaving 53 cases left, > > > on 3/11 we run 1019 so in order to ship a full load of 1,000 i'll need the > > > 53 cases left over from 3/10 plus 947 caes from 3/11 to equal 1000. then > > > it > > > shows a balance of 72 let over from 3/11 > > > > > > hope this sounds better > > > > > > thanks > > > "Otto Moehrbach" wrote: > > > > > >> What is the sequence of events? You say you want to automate this. What > > >> comes first? Detail what you enter and then what you want Excel to do > > >> (the > > >> automate part). Then what happens next? The next date row maybe? > > >> In short, Excel is dumb and it needs to know what is the trigger to make > > >> it > > >> automate something. Maybe any entry in Column B? It might help if you > > >> provide the series of steps you must do when you do this manually. > > >> Remember > > >> that you are talking to people who know nothing about what you have or > > >> want > > >> to have. HTH Otto > > >> > > >> "Bowmanator" <Bowmanator(a)discussions.microsoft.com> wrote in message > > >> news:66821ED8-2133-4A80-B286-702D0F01558B(a)microsoft.com... > > >> > i'm working on a shipping log that i do now by hand and would like to > > >> > automate if possible. every load equals 1,000 and alot of the loads are > > >> > split > > >> > loads. > > >> > > > >> > a b c d e > > >> > 1 date amount previous current total > > >> > 2 3/10 2053 1000 > > >> > 3 > > >> > 1000 > > >> > 4 > > >> > 53 > > >> > 5 3/11 1019 53 947 1000 > > >> > 6 72 > > >> > 7 3/12 3000 72 928 1000 > > >> > 8 > > >> > 1000 > > >> > 9 > > >> > 1000 > > >> > 10 72 > > >> > > > >> > in this example it shows what i need to equal 1,000 and if there's over > > >> > 1,000 left of that run i'd like the next line to show 1,000 and so on > > >> > until i > > >> > can't make a full load of 1,000. > > >> > > > >> > hope this sounds right > > >> > thanks > > >> > > >> . > > >> > > . > >
From: John on 15 Mar 2010 12:25 Hi Bowmanator I've attached a link to a sample file. Download it and check if that's what you want. http://cjoint.com/?dprqqVSBia Take note of its limitation. HTH John "Bowmanator" <Bowmanator(a)discussions.microsoft.com> wrote in message news:66821ED8-2133-4A80-B286-702D0F01558B(a)microsoft.com... > i'm working on a shipping log that i do now by hand and would like to > automate if possible. every load equals 1,000 and alot of the loads are split > loads. > > a b c d e > 1 date amount previous current total > 2 3/10 2053 1000 > 3 1000 > 4 53 > 5 3/11 1019 53 947 1000 > 6 72 > 7 3/12 3000 72 928 1000 > 8 1000 > 9 1000 > 10 72 > > in this example it shows what i need to equal 1,000 and if there's over > 1,000 left of that run i'd like the next line to show 1,000 and so on until i > can't make a full load of 1,000. > > hope this sounds right > thanks
From: Otto Moehrbach on 15 Mar 2010 16:41
The macros below do what you want. The first macro is an event macro that fires whenever an entry is made in Column B. This macro must be placed in the sheet module of your sheet. To access that module, right-click on the sheet tab and select View Code. Paste this first macro into that module, "X" out of the module to return to your sheet. The other 2 macros including everything from "Option Explicit" and below should be placed in a regular module. Come back if you need more. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Target.Column = 2 Then If Target.Row = 2 Then _ Call FirstEntry(Target) If Target.Row > 2 Then _ Call TheRestEntry(Target) End If End Sub Option Explicit Dim c As Long Dim WhatsLeft As Long Sub FirstEntry(TheCell As Range) Dim cc As Long Application.EnableEvents = False cc = Int(TheCell / 1000) For c = 0 To cc - 1 TheCell.Offset(c, 3) = 1000 Next c WhatsLeft = TheCell.Value - cc * 1000 TheCell.Offset(cc, 3) = WhatsLeft Application.EnableEvents = True End Sub Sub TheRestEntry(TheCell As Range) Application.EnableEvents = False TheCell.Offset(, 1) = TheCell.Offset(-1, 3) TheCell.Offset(, 2) = 1000 - TheCell.Offset(, 1) TheCell.Offset(, 3) = 1000 WhatsLeft = TheCell - TheCell.Offset(, 2) For c = 1 To 100 If WhatsLeft >= 1000 Then TheCell.Offset(c, 3) = 1000 WhatsLeft = WhatsLeft - 1000 Else TheCell.Offset(c, 3) = WhatsLeft Exit For End If Next c Application.EnableEvents = True End Sub "Bowmanator" <Bowmanator(a)discussions.microsoft.com> wrote in message news:609E2B26-4D9B-464C-9120-29EC8FF146C2(a)microsoft.com... > forgot, i'm using office xp pro > > "Bowmanator" wrote: > >> you assumed everything correct, and everything entered in b2 and below >> will >> be greater then 1,000. >> >> "Otto Moehrbach" wrote: >> >> > You didn't say anything about what Excel should/could use as the >> > trigger to >> > fire the automation. For now I'll assume it's any numerical entry made >> > in >> > Column B below row 1. Excel can do lots for you with this but it must >> > be >> > told every little detail so I have a question. >> > Question: Is every entry in Column B ALWAYS 1000 or greater? If not, >> > and >> > the sum of that entry and the "leftover" (53 in your 3/10 example) is >> > less >> > than 1000, what do you want to happen? >> > Another question: What version of Excel are you using? Otto >> > >> > "Bowmanator" <Bowmanator(a)discussions.microsoft.com> wrote in message >> > news:39C62505-FCCE-4767-8D78-90481D639285(a)microsoft.com... >> > > >> > > what i'm doing here is trying to keep track of how many full truck >> > > loads i >> > > can ship before i run out of product that was ran on a certain day. >> > > then >> > > the >> > > next load will be a split load. >> > > >> > > ex: on 3/10 we ran 2053 cases, that allows me to ship 2 full loads of >> > > that >> > > days run leaving 53 cases left, >> > > on 3/11 we run 1019 so in order to ship a full load of 1,000 i'll >> > > need the >> > > 53 cases left over from 3/10 plus 947 caes from 3/11 to equal 1000. >> > > then >> > > it >> > > shows a balance of 72 let over from 3/11 >> > > >> > > hope this sounds better >> > > >> > > thanks >> > > "Otto Moehrbach" wrote: >> > > >> > >> What is the sequence of events? You say you want to automate this. >> > >> What >> > >> comes first? Detail what you enter and then what you want Excel to >> > >> do >> > >> (the >> > >> automate part). Then what happens next? The next date row maybe? >> > >> In short, Excel is dumb and it needs to know what is the trigger to >> > >> make >> > >> it >> > >> automate something. Maybe any entry in Column B? It might help if >> > >> you >> > >> provide the series of steps you must do when you do this manually. >> > >> Remember >> > >> that you are talking to people who know nothing about what you have >> > >> or >> > >> want >> > >> to have. HTH Otto >> > >> >> > >> "Bowmanator" <Bowmanator(a)discussions.microsoft.com> wrote in message >> > >> news:66821ED8-2133-4A80-B286-702D0F01558B(a)microsoft.com... >> > >> > i'm working on a shipping log that i do now by hand and would like >> > >> > to >> > >> > automate if possible. every load equals 1,000 and alot of the >> > >> > loads are >> > >> > split >> > >> > loads. >> > >> > >> > >> > a b c d >> > >> > e >> > >> > 1 date amount previous current total >> > >> > 2 3/10 2053 >> > >> > 1000 >> > >> > 3 >> > >> > 1000 >> > >> > 4 >> > >> > 53 >> > >> > 5 3/11 1019 53 947 1000 >> > >> > 6 >> > >> > 72 >> > >> > 7 3/12 3000 72 928 1000 >> > >> > 8 >> > >> > 1000 >> > >> > 9 >> > >> > 1000 >> > >> > 10 >> > >> > 72 >> > >> > >> > >> > in this example it shows what i need to equal 1,000 and if there's >> > >> > over >> > >> > 1,000 left of that run i'd like the next line to show 1,000 and so >> > >> > on >> > >> > until i >> > >> > can't make a full load of 1,000. >> > >> > >> > >> > hope this sounds right >> > >> > thanks >> > >> >> > >> . >> > >> >> > . >> > |