Microsoft Excel: Adding to a range of cells for validation from a cell ,Microsoft Excel.

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

Keywords & Tags: adding, range, cells, validation, cell, microsoft, excel

URL: http://www.developertags.com/microsoft-excel/15216/

«« Prev - Next »» 7 helpful answers below.

"Mark Dullingham" <MarkDullingham...discussions.microsoft.com> wrote in
message news:467DD3C6-C401-45E8-BAA3-672857CB9EB1...microsoft.com...
> 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?

bob | Fri, 23 May 2008 03:38:00 GMT |

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" <MarkDullingham...discussions.microsoft.com> wrote in
message news:467DD3C6-C401-45E8-BAA3-672857CB9EB1...microsoft.com...
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?
>
>

markdullingham | Fri, 23 May 2008 03:39:00 GMT |

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" <MarkDullingham...discussions.microsoft.com> wrote in
message news:668A029F-D91D-4AD0-9BAF-5FA2DC0794C8...microsoft.com...
> 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" <MarkDullingham...discussions.microsoft.com> wrote in
> message news:467DD3C6-C401-45E8-BAA3-672857CB9EB1...microsoft.com...
> 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?
> >
> >
> >

bob | Fri, 23 May 2008 03:40:00 GMT |

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" <MarkDullingham...discussions.microsoft.com> wrote in
> message news:668A029F-D91D-4AD0-9BAF-5FA2DC0794C8...microsoft.com...
> > 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" <MarkDullingham...discussions.microsoft.com> wrote in
> > message news:467DD3C6-C401-45E8-BAA3-672857CB9EB1...microsoft.com...
> > 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?
> > >
> > >
> > >
>
>

markdullingham | Fri, 23 May 2008 03:41:00 GMT |

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" <MarkDullingham...discussions.microsoft.com> wrote in
> > message news:668A029F-D91D-4AD0-9BAF-5FA2DC0794C8...microsoft.com...
> > > 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" <MarkDullingham...discussions.microsoft.com> wrote in
> > > message news:467DD3C6-C401-45E8-BAA3-672857CB9EB1...microsoft.com...
> > > 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?
> > > >
> > > >
> > > >
> >
> >
> >

markdullingham | Fri, 23 May 2008 03:42:00 GMT |

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" <MarkDullingham...discussions.microsoft.com> wrote in
message news:A43474EC-A3A4-4429-A8ED-D6A79C954581...microsoft.com...
> 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" <MarkDullingham...discussions.microsoft.com> wrote in
> > message news:668A029F-D91D-4AD0-9BAF-5FA2DC0794C8...microsoft.com...
> > > 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" <MarkDullingham...discussions.microsoft.com> wrote in
> > > message news:467DD3C6-C401-45E8-BAA3-672857CB9EB1...microsoft.com...
> > > 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?
> > > >
> > > >
> > > >
> >
> >
> >

bob | Fri, 23 May 2008 03:43:00 GMT |

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" <MarkDullingham...discussions.microsoft.com> wrote in
> message news:A43474EC-A3A4-4429-A8ED-D6A79C954581...microsoft.com...
> > 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" <MarkDullingham...discussions.microsoft.com> wrote in
> > > message news:668A029F-D91D-4AD0-9BAF-5FA2DC0794C8...microsoft.com...
> > > > 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" <MarkDullingham...discussions.microsoft.com> wrote in
> > > > message news:467DD3C6-C401-45E8-BAA3-672857CB9EB1...microsoft.com...
> > > > 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?
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>

markdullingham | Fri, 23 May 2008 03:44:00 GMT |

Microsoft Excel Related Links

Microsoft Excel Hot Answers

adding to a number containing text
Hi, =(sum(A3+1) normally works great for me but now I am dealing with a number which contains text ie 'PE10000', how do I write a formula to add 1 to this to give 'PE10001'? Many thanks. Rick...
Adding to a ListBox
I have a userform that contains a listbox. Users can add values by entering number in a textbox and clicking an "add" button. The problem is once a certain number of entries have been added, the next values appear at the end of the listbox. Using the .AddItem method, how do I add values to the b...
Adding to a list
How would you go about adding to a list from another worksheet? -- famdamly --- famdamly's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29382 View this thread: http://www.excelforum.com/showthread.php?threadid=492635...
Adding to a group of text.
I have about 5000 words in columns, I want to add a IMG extension on these columns, cant his be achieved? for example: 1 WORD 2 PLAY 3 GAME I would lke to add .JPG on the end of each of those words, all the way down for 5,000 products, can this be done, and if so, how? thanks in advance...
adding to a formula
I have a workbook with 2 worksheets. Each cell (10 rows by 25 columns worth) in the last worksheet adds the value from the corresponding cell in the first worksheet to a constant number (e.g. sheetlast.cell(A1) = sheet1.cell(A1)+5). I have created a maco to make a copy of the first worksheet for...

Microsoft Excel New questions

Zulu times
I need to change Zulu time back 1 hour due to the daylight savings time. I have a format I need to follow, which is example ?P 04:45Z? Where the P needs to remain because it signifies proposed, and the Z needs to remain, because it signifies Zulu and the time 04:45 needs to be made 03:45 while p...
Z-test using excel
Hi, Not exactly an excel question but still posting here. I want to do z-test using Excel. I do have the analysis tool pak installed (along with Analysis tool-pak VBA). Being used to Canned software (The concerned tabulation software terms it as Z-Test for percentages) which tell me automaticall...
Z-test using excel
Hi,Not exactly an excel question but still posting here.I want to do z-test using Excel. I do have the analysis tool pak installed(along with Analysis tool-pak VBA).Being used to Canned software (The concerned tabulation software terms it asZ-Test for percentages) which tell me automatically whe...
Z-Ordering supported?
Does Excel (2002, 2003) support Z-Ordering of Inserted Objects?...
ZOrder Behavior Changed in Excel 2007?
Argh! Form controls now *always* appear on top of shape/drawing objects. What's up with that? Prior versions of Excel allows you to send Form controls behind shape objects by right clicking on them and choosing "Order->Send to back". Ok, so you're probably asking "why would you ever...
Zooming of Excel
I am creating a report using excel and i want to write/find a module that makes my report open up on anyones machine using excel 2000 look the same. I want it to adjust the zoom according to the users screen setting? Is this possible? I am not sure how to do it. If anyone knows could they help i...
Zooming of Excel
I am creating a report using excel and i want to write/find a module thatmakes my report open up on anyones machine using excel 2000 look the same.I want it to adjust the zoom according to the users screen setting? Is thispossible? I am not sure how to do it. If anyone knows could they help i am...
Zooming in on a row
How can I click on a row (or cell) and that record is seen separately on a different worksheet....
Zoom via Macro for WorkSheet to activate?
Hi,I have more worksheet in the workboock.I wish the Application in another PC - wiht differentMonitor-resolution to opening. However in the sameworkSheet-largeness.A.) Automatlically?B.) Via macro (Button) in the defined Zoom, for all workSheet toactivate?Have I any cance?Thank you very much!We...
Zoom via Macro for WorkSheet to activate?
Hi, I have more worksheet in the workboock. I wish the Application in another PC - wiht different Monitor-resolution to opening. However in the sam workSheet-largeness. A.) Automatlically? B.) Via macro (Button) in the defined Zoom, for all workSheet t activate? Have I any cance? Thank you very...

Microsoft Excel Related Categories

Microsoft Excel

(Microsoft Excel)Q&A: 'Adding to a range of cells for validation from a cell', With 7 answers.

Microsoft Excel: Adding to a range of cells for validation from a cell

Keywords & Tags: adding, range, cells, validation, cell, microsoft, excel

Microsoft Excel Related Links

Microsoft Excel Hot Answers

Microsoft Excel New questions

Microsoft Excel Related Categories

Microsoft Excel