$$Excel-Macros$$ Data Validation and Automation
Dear all, Please go through the sheet attached, where there are 4 columns. Column 1 is ID which is used to differentiate the component or item. Column 2 is Stage, which is used to designate where the component / item is being processed (ie) stage 1 or 2. Column 3 is completion quantity of component against the stage mentioned. And Column 4 needs to be filled automatically, when ever new data is added as row detail.(validation at the entry stage of column 3 of any new row addition) For example, please refer to row 8, when I enter A001 in ID, and 2 as stage and Qty as 20, the macro/function should check the previous entries in the 7 rows, with respect to ID and Stage and update CL_stock in row 2 and row 8. That 20 nos of item A001 is passed from Stage 1 to Stage 2. The function / macro also should not allow the user to enter more than the qty 20 in Column C of row 8 and also Stage entry for any item should not get duplicated. In the above case item A001 is processed in toto. Where as for ID B001 the item is processed partially. How to achieve this using Macro/Functions. Regards Eugene -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. For more options, visit https://groups.google.com/groups/opt_out. Validate and Update.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Data Validation and Automation
Anybody tried this !!! Regards Eugene On Sat, May 18, 2013 at 8:38 PM, Eugene Bernard wrote: > Dear all, > > Please go through the sheet attached, where there are 4 columns. > > Column 1 is ID which is used to differentiate the component or item. > > Column 2 is Stage, which is used to designate where the component / item > is being processed (ie) stage 1 or 2. > > Column 3 is completion quantity of component against the stage mentioned. > > And Column 4 needs to be filled automatically, when ever new data is added > as row detail.(validation at the entry stage of column 3 of any new row > addition) > > For example, please refer to row 8, when I enter A001 in ID, and 2 as > stage and Qty as 20, the macro/function should check the previous entries > in the 7 rows, with respect to ID and Stage and update > > CL_stock in row 2 and row 8. That 20 nos of item A001 is passed from Stage > 1 to Stage 2. The function / macro also should not allow the user to enter > more than the qty 20 in Column C of row 8 and also > > Stage entry for any item should not get duplicated. > > In the above case item A001 is processed in toto. Where as for ID B001 the > item is processed partially. > > How to achieve this using Macro/Functions. > > Regards > > Eugene > > > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing any security > measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at http://groups.google.com/group/excel-macros?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: $$Excel-Macros$$ Data Validation and Automation
There's probably lots of reasons you haven't received a response. First, is the subject. The idea is to provide a subject that will attract someone with expertise dealing with the problem that you have. Excel has something called "Data Validation" I've used it, but not often. I'm certainly no "expert". So, when I saw your subject, I decided to let someone who knows a lot more about Data Validation than I do address your question. The problem is, your question has nothing to do with EXCEL's "Data Validation". So.. some people skipped it because it was something to do with Data Validation, and other skipped it because it has NOTHING to do with Data Validation! --- OK, so it actually sounds like you want a macro to calculate totals (of some sort) I would use a Change Event macro (do you know about these? come to think of it, do you know how to write macros?) Here's a start. I don't know exactly what you want to do with the qty and CL_Stock values. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim Targ As Range Dim RowNum As Integer '-- ' If multiple cells are changed, process each cell '-- For Each Targ In Target '-- 'Only process if columns A-C are modified '-- If (Targ.Column <= 3) Then '-- ' Check to see that each cell in A-C have values '-- If ((Cells(Targ.Row, "A").Value <> "") _ And (Cells(Targ.Row, "B").Value <> "") _ And (Cells(Targ.Row, "C").Value <> "")) Then '-- ' Look in previous rows for records with same "ID" '-- For RowNum = 1 To Targ.Row - 1 If (Cells(RowNum, "A").Value = _ Cells(Targ.Row, "A").Value) Then ' what to do ? End If Next RowNum End If End If Next Targ End Sub Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - From: Eugene Bernard To: excel-macros@googlegroups.com Sent: Sun, May 19, 2013 11:40:45 AM Subject: Re: $$Excel-Macros$$ Data Validation and Automation Anybody tried this !!! Regards Eugene On Sat, May 18, 2013 at 8:38 PM, Eugene Bernard wrote: Dear all, > > >Please go through the sheet attached, where there are 4 columns. >Column 1 is ID which is used to differentiate the component or item. >Column 2 is Stage, which is used to designate where the component / item is >being processed (ie) stage 1 or 2. >Column 3 is completion quantity of component against the stage mentioned. >And Column 4 needs to be filled automatically, when ever new data is added as >row detail.(validation at the entry stage of column 3 of any new row addition) > > >For example, please refer to row 8, when I enter A001 in ID, and 2 as stage >and >Qty as 20, the macro/function should check the previous entries in the 7 rows, >with respect to ID and Stage and update >CL_stock in row 2 and row 8. >That 20 nos of item A001 is passed from Stage 1 to Stage 2. The function / >macro >also should not allow the user to enter more than the qty 20 in Column C of >row >8 and also > >Stage entry for any item should not get duplicated. >In the above case item A001 is processed in toto. Where as for ID B001 the >item >is processed partially. >How to achieve this using Macro/Functions. >Regards >Eugene > >-- >Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >https://www.facebook.com/discussexcel > >FORUM RULES > >1) Use concise, accurate thread titles. Poor thread titles, like Please Help, >Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get >quick attention or may not be answered. >2) Don't post a question in the thread of another member. >3) Don't post questions re