Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding to a range of cells for validation from a cell
I am an Excel novice so please be patient!
My problem is this- I have a drop down list set up in D2 in worksheet 1 which references a list of customers contained in column A in worksheet 2. When the selection is made in D1, sheet 1 it returns a contract number in E2 from column B in sheet 2 using VLOOKUP funtion (and so on down the sheet). As the enteries in sheet 2 Col A are not a definitive list of possible customers, nor are the contract number in Col B a definitive list of job numbers that may be associated with a particular customer, the user can enter their own values into Sheet 1, D2 and E2. Pretty staight forward so far! What I would like to happen would be 1 of 2 possibilities 1- If the user enters a customers name and job number that does not appear in sheet 2 col A and B, the entries will be added to the relavant columns for selection in the future. 2- If the user enters a customers name that does exist in Col A sheet 2, but ammends the job number (we use 1 type of job number for a contract and another for one off specific jobs) that number is some how added to a list of job numbers for that customer and when that customer is select in the future, further down the sheet, all associated numbers appear in another drop down list. A solution to 1 would be great, but a solution to 2 would be superb( I appreciate it may be way out of my depth) and score me loads of points with the boss. I've noticed, whilst trawling through loads of postings seraching for an answer, that people have posted specific solution based on the posteractual worksheet, so if any one kind enough to profer any solutions needs more info I will do my best to come up with the goods. Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding to a range of cells for validation from a cell
"Mark Dullingham" wrote in message ... I am an Excel novice so please be patient! My problem is this- I have a drop down list set up in D2 in worksheet 1 which references a list of customers contained in column A in worksheet 2. When the selection is made in D1, sheet 1 it returns a contract number in E2 from column B in sheet 2 using VLOOKUP funtion (and so on down the sheet). As the enteries in sheet 2 Col A are not a definitive list of possible customers, nor are the contract number in Col B a definitive list of job numbers that may be associated with a particular customer, the user can enter their own values into Sheet 1, D2 and E2. Pretty staight forward so far! What I would like to happen would be 1 of 2 possibilities 1- If the user enters a customers name and job number that does not appear in sheet 2 col A and B, the entries will be added to the relavant columns for selection in the future. 2- If the user enters a customers name that does exist in Col A sheet 2, but ammends the job number (we use 1 type of job number for a contract and another for one off specific jobs) that number is some how added to a list of job numbers for that customer and when that customer is select in the future, further down the sheet, all associated numbers appear in another drop down list. Changing the DV to update the list if a new entry is made is pretty trivial with VBA event code, but the job number is much different. If you get the job number via a VLOOKUP formula, if you allow the user to over-type it, the formula is gone, so that is a no-no. What other way would you envisage the user specifying/amending the job number? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding to a range of cells for validation from a cell
Thanks for your reply Bob. Boy that was fast!
You say that updating the DV is trivial, but I haven't a clue where to start. What I know about VBA could fit on a postage stamp. As for the job numbers I've had a thought that might work. My original idea obviously a no go so instead The customers name could be entered manually with some reference to the one off job say 'Customer 1 - Job xxx' using a VBA event solution as you mentioned, this would enter a unique entry to to the validation list. Could the VLOOKUP function in E2 be entered into an IF and ISTEXT statement combination. The current formula in E2 is - =IF(ISERROR(VLOOKUP(F9,'Contract Details'!A:F,6,FALSE)),"",VLOOKUP(F9,'Contract Details'!A:F,6,FALSE)) What i was thinking was something like - If the VLOOKUP would return a blank value, due to the new unique customer name, then another dropdown box would appear in E3 so a number could be choosen or added using the event code. If carried out at the same time this would then make the formula in E2 return the job number just entered in the appearing drop down box ssuming that if the new customer name went ito the next available row so would the new job number. I thought the first part of the formula would be along the lines of - =IF(ISTEXT(F9,'Contract Details'!A:F,6,FALSE),(F9,'Contract Details'!A:F,6,FALSE),'HERE IS WHERE I DON'T HAVE A CLUE' But I don't know how to make another drop down box appear in E3 if the ISTEXT returns as FALSE. If this is possible could the end reseult be wrapped in the ISERROR function as the VLOOKUP is currently, to stop N/A result if no customer is selected in D1. I hope this makes sense Thank once again - Mark "Bob Phillips" wrote: "Mark Dullingham" wrote in message ... I am an Excel novice so please be patient! My problem is this- I have a drop down list set up in D2 in worksheet 1 which references a listof customers contained in column A in worksheet 2. When the selection is madein D1, sheet 1 it returns a contract number in E2 from column B in sheet 2using VLOOKUP funtion (and so on down the sheet). As the enteries in sheet 2 Col A are not a definitive list of possible customers, nor are the contract number in Col B a definitive list of job numbers that may be associated with a particular customer, the user can enter their own values into Sheet 1, D2 and E2. Pretty staight forward so far! What I would like to happen would be 1 of 2 possibilities 1- If the user enters a customers name and job number that does not appear in sheet 2 col A and B, the entries will be added to the relavant columns for selection in the future. 2- If the user enters a customers name that does exist in Col A sheet 2, but ammends the job number (we use 1 type of job number for a contract and another for one off specific jobs) that number is some how added to a list of job numbers for that customer and when that customer is select in the future, further down the sheet, all associated numbers appear in another drop down list. Changing the DV to update the list if a new entry is made is pretty trivial with VBA event code, but the job number is much different. If you get the job number via a VLOOKUP formula, if you allow the user to over-type it, the formula is gone, so that is a no-no. What other way would you envisage the user specifying/amending the job number? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding to a range of cells for validation from a cell
Mark,
That sounds good. On the DV cell, F9, change the Show error alert on the Error Alert tab to not checked, and then add this event code Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "F9" Dim oWSDetails As Worksheet Dim rng As Range Dim iRow As Long Dim iPos As Long On Error GoTo ws_exit: Application.EnableEvents = False Set oWSDetails = Worksheets("Contract Details") Set rng = oWSDetails.Range("A:A") If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target iPos = InStr(1, .Value, " Job") If Application.CountIf(rng, .Value) = 0 Then If iPos = 0 Then MsgBox "Invalid value" Else iRow = oWSDetails.Cells(oWSDetails.Rows.Count, "A").End(xlUp).Row + 1 oWSDetails.Cells(iRow, "A").Value = Left(.Value, iPos - 1) oWSDetails.Cells(iRow, "F").Value = Right(.Value, Len(.Value) - iPos - 4) .Value = Left(.Value, iPos - 1) End If End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. Then, when you want to add a bew item, input Customer - Job xxx as you say, exactly that format, and it should add as required. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Mark Dullingham" wrote in message ... Thanks for your reply Bob. Boy that was fast! You say that updating the DV is trivial, but I haven't a clue where to start. What I know about VBA could fit on a postage stamp. As for the job numbers I've had a thought that might work. My original idea obviously a no go so instead The customers name could be entered manually with some reference to the one off job say 'Customer 1 - Job xxx' using a VBA event solution as you mentioned, this would enter a unique entry to to the validation list. Could the VLOOKUP function in E2 be entered into an IF and ISTEXT statement combination. The current formula in E2 is - =IF(ISERROR(VLOOKUP(F9,'Contract Details'!A:F,6,FALSE)),"",VLOOKUP(F9,'Contract Details'!A:F,6,FALSE)) What i was thinking was something like - If the VLOOKUP would return a blank value, due to the new unique customer name, then another dropdown box would appear in E3 so a number could be choosen or added using the event code. If carried out at the same time this would then make the formula in E2 return the job number just entered in the appearing drop down box ssuming that if the new customer name went ito the next available row so would the new job number. I thought the first part of the formula would be along the lines of - =IF(ISTEXT(F9,'Contract Details'!A:F,6,FALSE),(F9,'Contract Details'!A:F,6,FALSE),'HERE IS WHERE I DON'T HAVE A CLUE' But I don't know how to make another drop down box appear in E3 if the ISTEXT returns as FALSE. If this is possible could the end reseult be wrapped in the ISERROR function as the VLOOKUP is currently, to stop N/A result if no customer is selected in D1. I hope this makes sense Thank once again - Mark "Bob Phillips" wrote: "Mark Dullingham" wrote in message ... I am an Excel novice so please be patient! My problem is this- I have a drop down list set up in D2 in worksheet 1 which references a listof customers contained in column A in worksheet 2. When the selection is madein D1, sheet 1 it returns a contract number in E2 from column B in sheet 2using VLOOKUP funtion (and so on down the sheet). As the enteries in sheet 2 Col A are not a definitive list of possible customers, nor are the contract number in Col B a definitive list of job numbers that may be associated with a particular customer, the user can enter their own values into Sheet 1, D2 and E2. Pretty staight forward so far! What I would like to happen would be 1 of 2 possibilities 1- If the user enters a customers name and job number that does not appear in sheet 2 col A and B, the entries will be added to the relavant columns for selection in the future. 2- If the user enters a customers name that does exist in Col A sheet 2, but ammends the job number (we use 1 type of job number for a contract and another for one off specific jobs) that number is some how added to a list of job numbers for that customer and when that customer is select in the future, further down the sheet, all associated numbers appear in another drop down list. Changing the DV to update the list if a new entry is made is pretty trivial with VBA event code, but the job number is much different. If you get the job number via a VLOOKUP formula, if you allow the user to over-type it, the formula is gone, so that is a no-no. What other way would you envisage the user specifying/amending the job number? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding to a range of cells for validation from a cell
Thanks for the reply Bob,
I ran into a spot of bother! I followed your instructions on pasting in the code. Idon't know if this has any bearing, but when I pasted the code the following section was in red- iRow = oWSDetails.Cells(oWSDetails.Rows.Count, "A").End(xlUp).Row + 1 oWSDetails.Cells(iRow, "A").Value = Left(.Value, iPos - 1) oWSDetails.Cells(iRow, "F").Value = Right(.Value, Len(.Value) - iPos - 4) and when I type a test message in the DD an error occurred.if it helps the ext details of the Sheets are - Week 1 Week 2 and so on to- Week 5 and Contract Details In the 'Week#' sheets the VD is in F9:F36 using a list called Contracts ("Contract Details"!$A$2:$A$70). There is also a VLOOKUP in E9:E36 =IF(ISERROR(VLOOKUP(F9,'Contract Details'!A:F,6,FALSE)),"",VLOOKUP(F9,'Contract Details'!A:F,6,FALSE)). Hope this info helps and thanks once again for all your help, it's much appreciated. "Bob Phillips" wrote: Mark, That sounds good. On the DV cell, F9, change the Show error alert on the Error Alert tab to not checked, and then add this event code Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "F9" Dim oWSDetails As Worksheet Dim rng As Range Dim iRow As Long Dim iPos As Long On Error GoTo ws_exit: Application.EnableEvents = False Set oWSDetails = Worksheets("Contract Details") Set rng = oWSDetails.Range("A:A") If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target iPos = InStr(1, .Value, " Job") If Application.CountIf(rng, .Value) = 0 Then If iPos = 0 Then MsgBox "Invalid value" Else iRow = oWSDetails.Cells(oWSDetails.Rows.Count, "A").End(xlUp).Row + 1 oWSDetails.Cells(iRow, "A").Value = Left(.Value, iPos - 1) oWSDetails.Cells(iRow, "F").Value = Right(.Value, Len(.Value) - iPos - 4) .Value = Left(.Value, iPos - 1) End If End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. Then, when you want to add a bew item, input Customer - Job xxx as you say, exactly that format, and it should add as required. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Mark Dullingham" wrote in message ... Thanks for your reply Bob. Boy that was fast! You say that updating the DV is trivial, but I haven't a clue where to start. What I know about VBA could fit on a postage stamp. As for the job numbers I've had a thought that might work. My original idea obviously a no go so instead The customers name could be entered manually with some reference to the one off job say 'Customer 1 - Job xxx' using a VBA event solution as you mentioned, this would enter a unique entry to to the validation list. Could the VLOOKUP function in E2 be entered into an IF and ISTEXT statement combination. The current formula in E2 is - =IF(ISERROR(VLOOKUP(F9,'Contract Details'!A:F,6,FALSE)),"",VLOOKUP(F9,'Contract Details'!A:F,6,FALSE)) What i was thinking was something like - If the VLOOKUP would return a blank value, due to the new unique customer name, then another dropdown box would appear in E3 so a number could be choosen or added using the event code. If carried out at the same time this would then make the formula in E2 return the job number just entered in the appearing drop down box ssuming that if the new customer name went ito the next available row so would the new job number. I thought the first part of the formula would be along the lines of - =IF(ISTEXT(F9,'Contract Details'!A:F,6,FALSE),(F9,'Contract Details'!A:F,6,FALSE),'HERE IS WHERE I DON'T HAVE A CLUE' But I don't know how to make another drop down box appear in E3 if the ISTEXT returns as FALSE. If this is possible could the end reseult be wrapped in the ISERROR function as the VLOOKUP is currently, to stop N/A result if no customer is selected in D1. I hope this makes sense Thank once again - Mark "Bob Phillips" wrote: "Mark Dullingham" wrote in message ... I am an Excel novice so please be patient! My problem is this- I have a drop down list set up in D2 in worksheet 1 which references a listof customers contained in column A in worksheet 2. When the selection is madein D1, sheet 1 it returns a contract number in E2 from column B in sheet 2using VLOOKUP funtion (and so on down the sheet). As the enteries in sheet 2 Col A are not a definitive list of possible customers, nor are the contract number in Col B a definitive list of job numbers that may be associated with a particular customer, the user can enter their own values into Sheet 1, D2 and E2. Pretty staight forward so far! What I would like to happen would be 1 of 2 possibilities 1- If the user enters a customers name and job number that does not appear in sheet 2 col A and B, the entries will be added to the relavant columns for selection in the future. 2- If the user enters a customers name that does exist in Col A sheet 2, but ammends the job number (we use 1 type of job number for a contract and another for one off specific jobs) that number is some how added to a list of job numbers for that customer and when that customer is select in the future, further down the sheet, all associated numbers appear in another drop down list. Changing the DV to update the list if a new entry is made is pretty trivial with VBA event code, but the job number is much different. If you get the job number via a VLOOKUP formula, if you allow the user to over-type it, the formula is gone, so that is a no-no. What other way would you envisage the user specifying/amending the job number? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding to a range of cells for validation from a cell
Bob
I've just made a few changes (for house keeping puposes) and the validation list called 'Contracts' now lives in "Lookup Sheet" A15:A83 with thw VLOOKUP array being A15:B83 where B15:B83 are the contract numbers. "Mark Dullingham" wrote: Thanks for the reply Bob, I ran into a spot of bother! I followed your instructions on pasting in the code. Idon't know if this has any bearing, but when I pasted the code the following section was in red- iRow = oWSDetails.Cells(oWSDetails.Rows.Count, "A").End(xlUp).Row + 1 oWSDetails.Cells(iRow, "A").Value = Left(.Value, iPos - 1) oWSDetails.Cells(iRow, "F").Value = Right(.Value, Len(.Value) - iPos - 4) and when I type a test message in the DD an error occurred.if it helps the ext details of the Sheets are - Week 1 Week 2 and so on to- Week 5 and Contract Details In the 'Week#' sheets the VD is in F9:F36 using a list called Contracts ("Contract Details"!$A$2:$A$70). There is also a VLOOKUP in E9:E36 =IF(ISERROR(VLOOKUP(F9,'Contract Details'!A:F,6,FALSE)),"",VLOOKUP(F9,'Contract Details'!A:F,6,FALSE)). Hope this info helps and thanks once again for all your help, it's much appreciated. "Bob Phillips" wrote: Mark, That sounds good. On the DV cell, F9, change the Show error alert on the Error Alert tab to not checked, and then add this event code Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "F9" Dim oWSDetails As Worksheet Dim rng As Range Dim iRow As Long Dim iPos As Long On Error GoTo ws_exit: Application.EnableEvents = False Set oWSDetails = Worksheets("Contract Details") Set rng = oWSDetails.Range("A:A") If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target iPos = InStr(1, .Value, " Job") If Application.CountIf(rng, .Value) = 0 Then If iPos = 0 Then MsgBox "Invalid value" Else iRow = oWSDetails.Cells(oWSDetails.Rows.Count, "A").End(xlUp).Row + 1 oWSDetails.Cells(iRow, "A").Value = Left(.Value, iPos - 1) oWSDetails.Cells(iRow, "F").Value = Right(.Value, Len(.Value) - iPos - 4) .Value = Left(.Value, iPos - 1) End If End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. Then, when you want to add a bew item, input Customer - Job xxx as you say, exactly that format, and it should add as required. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Mark Dullingham" wrote in message ... Thanks for your reply Bob. Boy that was fast! You say that updating the DV is trivial, but I haven't a clue where to start. What I know about VBA could fit on a postage stamp. As for the job numbers I've had a thought that might work. My original idea obviously a no go so instead The customers name could be entered manually with some reference to the one off job say 'Customer 1 - Job xxx' using a VBA event solution as you mentioned, this would enter a unique entry to to the validation list. Could the VLOOKUP function in E2 be entered into an IF and ISTEXT statement combination. The current formula in E2 is - =IF(ISERROR(VLOOKUP(F9,'Contract Details'!A:F,6,FALSE)),"",VLOOKUP(F9,'Contract Details'!A:F,6,FALSE)) What i was thinking was something like - If the VLOOKUP would return a blank value, due to the new unique customer name, then another dropdown box would appear in E3 so a number could be choosen or added using the event code. If carried out at the same time this would then make the formula in E2 return the job number just entered in the appearing drop down box ssuming that if the new customer name went ito the next available row so would the new job number. I thought the first part of the formula would be along the lines of - =IF(ISTEXT(F9,'Contract Details'!A:F,6,FALSE),(F9,'Contract Details'!A:F,6,FALSE),'HERE IS WHERE I DON'T HAVE A CLUE' But I don't know how to make another drop down box appear in E3 if the ISTEXT returns as FALSE. If this is possible could the end reseult be wrapped in the ISERROR function as the VLOOKUP is currently, to stop N/A result if no customer is selected in D1. I hope this makes sense Thank once again - Mark "Bob Phillips" wrote: "Mark Dullingham" wrote in message ... I am an Excel novice so please be patient! My problem is this- I have a drop down list set up in D2 in worksheet 1 which references a listof customers contained in column A in worksheet 2. When the selection is madein D1, sheet 1 it returns a contract number in E2 from column B in sheet 2using VLOOKUP funtion (and so on down the sheet). As the enteries in sheet 2 Col A are not a definitive list of possible customers, nor are the contract number in Col B a definitive list of job numbers that may be associated with a particular customer, the user can enter their own values into Sheet 1, D2 and E2. Pretty staight forward so far! What I would like to happen would be 1 of 2 possibilities 1- If the user enters a customers name and job number that does not appear in sheet 2 col A and B, the entries will be added to the relavant columns for selection in the future. 2- If the user enters a customers name that does exist in Col A sheet 2, but ammends the job number (we use 1 type of job number for a contract and another for one off specific jobs) that number is some how added to a list of job numbers for that customer and when that customer is select in the future, further down the sheet, all associated numbers appear in another drop down list. Changing the DV to update the list if a new entry is made is pretty trivial with VBA event code, but the job number is much different. If you get the job number via a VLOOKUP formula, if you allow the user to over-type it, the formula is gone, so that is a no-no. What other way would you envisage the user specifying/amending the job number? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding to a range of cells for validation from a cell
That looks like NG wrap-around Mark. Try changing it to
iRow = oWSDetails.Cells(oWSDetails.Rows.Count,"A") _ .End(xlUp).Row + 1 oWSDetails.Cells(iRow, "A").Value = Left(.Value, _ iPos -1) oWSDetails.Cells(iRow, "F").Value = _ Right(.Value, Len(.Value) - iPos - 4) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Mark Dullingham" wrote in message ... Thanks for the reply Bob, I ran into a spot of bother! I followed your instructions on pasting in the code. Idon't know if this has any bearing, but when I pasted the code the following section was in red- iRow = oWSDetails.Cells(oWSDetails.Rows.Count, "A").End(xlUp).Row + 1 oWSDetails.Cells(iRow, "A").Value = Left(.Value, iPos - 1) oWSDetails.Cells(iRow, "F").Value = Right(.Value, Len(.Value) - iPos - 4) and when I type a test message in the DD an error occurred.if it helps the ext details of the Sheets are - Week 1 Week 2 and so on to- Week 5 and Contract Details In the 'Week#' sheets the VD is in F9:F36 using a list called Contracts ("Contract Details"!$A$2:$A$70). There is also a VLOOKUP in E9:E36 =IF(ISERROR(VLOOKUP(F9,'Contract Details'!A:F,6,FALSE)),"",VLOOKUP(F9,'Contract Details'!A:F,6,FALSE)). Hope this info helps and thanks once again for all your help, it's much appreciated. "Bob Phillips" wrote: Mark, That sounds good. On the DV cell, F9, change the Show error alert on the Error Alert tab to not checked, and then add this event code Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "F9" Dim oWSDetails As Worksheet Dim rng As Range Dim iRow As Long Dim iPos As Long On Error GoTo ws_exit: Application.EnableEvents = False Set oWSDetails = Worksheets("Contract Details") Set rng = oWSDetails.Range("A:A") If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target iPos = InStr(1, .Value, " Job") If Application.CountIf(rng, .Value) = 0 Then If iPos = 0 Then MsgBox "Invalid value" Else iRow = oWSDetails.Cells(oWSDetails.Rows.Count, "A").End(xlUp).Row + 1 oWSDetails.Cells(iRow, "A").Value = Left(.Value, iPos - 1) oWSDetails.Cells(iRow, "F").Value = Right(.Value, Len(.Value) - iPos - 4) .Value = Left(.Value, iPos - 1) End If End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. Then, when you want to add a bew item, input Customer - Job xxx as you say, exactly that format, and it should add as required. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Mark Dullingham" wrote in message ... Thanks for your reply Bob. Boy that was fast! You say that updating the DV is trivial, but I haven't a clue where to start. What I know about VBA could fit on a postage stamp. As for the job numbers I've had a thought that might work. My original idea obviously a no go so instead The customers name could be entered manually with some reference to the one off job say 'Customer 1 - Job xxx' using a VBA event solution as you mentioned, this would enter a unique entry to to the validation list. Could the VLOOKUP function in E2 be entered into an IF and ISTEXT statement combination. The current formula in E2 is - =IF(ISERROR(VLOOKUP(F9,'Contract Details'!A:F,6,FALSE)),"",VLOOKUP(F9,'Contract Details'!A:F,6,FALSE)) What i was thinking was something like - If the VLOOKUP would return a blank value, due to the new unique customer name, then another dropdown box would appear in E3 so a number could be choosen or added using the event code. If carried out at the same time this would then make the formula in E2 return the job number just entered in the appearing drop down box ssuming that if the new customer name went ito the next available row so would the new job number. I thought the first part of the formula would be along the lines of - =IF(ISTEXT(F9,'Contract Details'!A:F,6,FALSE),(F9,'Contract Details'!A:F,6,FALSE),'HERE IS WHERE I DON'T HAVE A CLUE' But I don't know how to make another drop down box appear in E3 if the ISTEXT returns as FALSE. If this is possible could the end reseult be wrapped in the ISERROR function as the VLOOKUP is currently, to stop N/A result if no customer is selected in D1. I hope this makes sense Thank once again - Mark "Bob Phillips" wrote: "Mark Dullingham" wrote in message ... I am an Excel novice so please be patient! My problem is this- I have a drop down list set up in D2 in worksheet 1 which references a listof customers contained in column A in worksheet 2. When the selection is madein D1, sheet 1 it returns a contract number in E2 from column B in sheet 2using VLOOKUP funtion (and so on down the sheet). As the enteries in sheet 2 Col A are not a definitive list of possible customers, nor are the contract number in Col B a definitive list of job numbers that may be associated with a particular customer, the user can enter their own values into Sheet 1, D2 and E2. Pretty staight forward so far! What I would like to happen would be 1 of 2 possibilities 1- If the user enters a customers name and job number that does not appear in sheet 2 col A and B, the entries will be added to the relavant columns for selection in the future. 2- If the user enters a customers name that does exist in Col A sheet 2, but ammends the job number (we use 1 type of job number for a contract and another for one off specific jobs) that number is some how added to a list of job numbers for that customer and when that customer is select in the future, further down the sheet, all associated numbers appear in another drop down list. Changing the DV to update the list if a new entry is made is pretty trivial with VBA event code, but the job number is much different. If you get the job number via a VLOOKUP formula, if you allow the user to over-type it, the formula is gone, so that is a no-no. What other way would you envisage the user specifying/amending the job number? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding to a range of cells for validation from a cell
Hi Bob
Edited as below but the last 4 lines stayed red. blindly, played around with the spacing (basically ending each line with a bracket. Scientific or what) and got all the text black. it ended up like this- On Error GoTo ws_exit: Application.EnableEvents = False Set oWSDetails = Worksheets("Contract Details") Set rng = oWSDetails.Range("A:A") If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target iPos = InStr(1, .Value, " Job") If Application.CountIf(rng, .Value) = 0 Then If iPos = 0 Then MsgBox "Invalid value" Else iRow = oWSDetails.Cells(oWSDetails.Rows.Count, "A") .End(xlUp).Row 1 oWSDetails.Cells(iRow, "A").Value = Left(.Value, iPos - 1) oWSDetails.Cells(iRow, "F").Value = Right(.Value, Len(.Value) - iPos - 4) ****this bit is on the end of the line above***** .Value = Left(.Value, iPos - 1) End If End If End With End If when I tested it I got this error Compile error - Invalid use of Property and when the code window popped up the '.Row' in the following line was highlighted. .End(xlUp).Row 1 Sorry to keep throwing this back at you. Many thanks Mark "Bob Phillips" wrote: That looks like NG wrap-around Mark. Try changing it to iRow = oWSDetails.Cells(oWSDetails.Rows.Count,"A") _ .End(xlUp).Row + 1 oWSDetails.Cells(iRow, "A").Value = Left(.Value, _ iPos -1) oWSDetails.Cells(iRow, "F").Value = _ Right(.Value, Len(.Value) - iPos - 4) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Mark Dullingham" wrote in message ... Thanks for the reply Bob, I ran into a spot of bother! I followed your instructions on pasting in the code. Idon't know if this has any bearing, but when I pasted the code the following section was in red- iRow = oWSDetails.Cells(oWSDetails.Rows.Count, "A").End(xlUp).Row + 1 oWSDetails.Cells(iRow, "A").Value = Left(.Value, iPos - 1) oWSDetails.Cells(iRow, "F").Value = Right(.Value, Len(.Value) - iPos - 4) and when I type a test message in the DD an error occurred.if it helps the ext details of the Sheets are - Week 1 Week 2 and so on to- Week 5 and Contract Details In the 'Week#' sheets the VD is in F9:F36 using a list called Contracts ("Contract Details"!$A$2:$A$70). There is also a VLOOKUP in E9:E36 =IF(ISERROR(VLOOKUP(F9,'Contract Details'!A:F,6,FALSE)),"",VLOOKUP(F9,'Contract Details'!A:F,6,FALSE)). Hope this info helps and thanks once again for all your help, it's much appreciated. "Bob Phillips" wrote: Mark, That sounds good. On the DV cell, F9, change the Show error alert on the Error Alert tab to not checked, and then add this event code Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "F9" Dim oWSDetails As Worksheet Dim rng As Range Dim iRow As Long Dim iPos As Long On Error GoTo ws_exit: Application.EnableEvents = False Set oWSDetails = Worksheets("Contract Details") Set rng = oWSDetails.Range("A:A") If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target iPos = InStr(1, .Value, " Job") If Application.CountIf(rng, .Value) = 0 Then If iPos = 0 Then MsgBox "Invalid value" Else iRow = oWSDetails.Cells(oWSDetails.Rows.Count, "A").End(xlUp).Row + 1 oWSDetails.Cells(iRow, "A").Value = Left(.Value, iPos - 1) oWSDetails.Cells(iRow, "F").Value = Right(.Value, Len(.Value) - iPos - 4) .Value = Left(.Value, iPos - 1) End If End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. Then, when you want to add a bew item, input Customer - Job xxx as you say, exactly that format, and it should add as required. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Mark Dullingham" wrote in message ... Thanks for your reply Bob. Boy that was fast! You say that updating the DV is trivial, but I haven't a clue where to start. What I know about VBA could fit on a postage stamp. As for the job numbers I've had a thought that might work. My original idea obviously a no go so instead The customers name could be entered manually with some reference to the one off job say 'Customer 1 - Job xxx' using a VBA event solution as you mentioned, this would enter a unique entry to to the validation list. Could the VLOOKUP function in E2 be entered into an IF and ISTEXT statement combination. The current formula in E2 is - =IF(ISERROR(VLOOKUP(F9,'Contract Details'!A:F,6,FALSE)),"",VLOOKUP(F9,'Contract Details'!A:F,6,FALSE)) What i was thinking was something like - If the VLOOKUP would return a blank value, due to the new unique customer name, then another dropdown box would appear in E3 so a number could be choosen or added using the event code. If carried out at the same time this would then make the formula in E2 return the job number just entered in the appearing drop down box ssuming that if the new customer name went ito the next available row so would the new job number. I thought the first part of the formula would be along the lines of - =IF(ISTEXT(F9,'Contract Details'!A:F,6,FALSE),(F9,'Contract Details'!A:F,6,FALSE),'HERE IS WHERE I DON'T HAVE A CLUE' But I don't know how to make another drop down box appear in E3 if the ISTEXT returns as FALSE. If this is possible could the end reseult be wrapped in the ISERROR function as the VLOOKUP is currently, to stop N/A result if no customer is selected in D1. I hope this makes sense Thank once again - Mark "Bob Phillips" wrote: "Mark Dullingham" wrote in message ... I am an Excel novice so please be patient! My problem is this- I have a drop down list set up in D2 in worksheet 1 which references a listof customers contained in column A in worksheet 2. When the selection is madein D1, sheet 1 it returns a contract number in E2 from column B in sheet 2using VLOOKUP funtion (and so on down the sheet). As the enteries in sheet 2 Col A are not a definitive list of possible customers, nor are the contract number in Col B a definitive list of job numbers that may be associated with a particular customer, the user can enter their own values into Sheet 1, D2 and E2. Pretty staight forward so far! What I would like to happen would be 1 of 2 possibilities 1- If the user enters a customers name and job number that does not appear in sheet 2 col A and B, the entries will be added to the relavant columns for selection in the future. 2- If the user enters a customers name that does exist in Col A sheet 2, but ammends the job number (we use 1 type of job number for a contract and another for one off specific jobs) that number is some how added to a list of job numbers for that customer and when that customer is select in the future, further down the sheet, all associated numbers appear in another drop down list. Changing the DV to update the list if a new entry is made is pretty trivial with VBA event code, but the job number is much different. If you get the job number via a VLOOKUP formula, if you allow the user to over-type it, the formula is gone, so that is a no-no. What other way would you envisage the user specifying/amending the job number? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
Adding contents of one cell to a range of cells. | Excel Worksheet Functions | |||
Dynamically adding an in-cell drop-down list (i.e. Validation Object) to a cell? | Excel Programming | |||
sheets.range and adding cells that are out of the range | Excel Programming | |||
adding a list validation to a cell | Excel Programming |