|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
Worksheet1 (names list)
Column A column B
NameA 1
NameB 2
I have managed below by worksheet1 change event and I intend
to use sheet2 / columnB as a validation list somewhere else to
speed up data entering- hate using mouse.
Couldn't manage to update sheet2 when a name is deleted on sheet1.
Don't know the usage of formulas in event codes.Any help ?
Thank you.
Worksheet 2
Column A column B
NameA 1-NameA
NameA 1
NameB 2-NameB
NameB 2
|
|
Remember that you're the only one who knows what you have, what you want,
and how you want to get it. Please post back and explain:
What is on sheet 1?
What is on sheet 2?
What sheet has the change event macro and what is the code of that macro?
What cell on what sheet has the Data Validation?
What list on what sheet is used in the Data Validation in that cell?
What do you want updated on sheet 2 when you delete a name in sheet1.
Be specific. Remember that you are talking to someone who has no idea what
you are talking about.
HTH Otto
<rumkus[ at ]hotmail.com> wrote in message
news:1156974453.905693.139370[ at ]b28g2000cwb.googlegroups.com...
[Quoted Text] > Worksheet1 (names list)
>
> Column A column B
> NameA 1
> NameB 2
>
> I have managed below by worksheet1 change event and I intend
> to use sheet2 / columnB as a validation list somewhere else to
> speed up data entering- hate using mouse.
> Couldn't manage to update sheet2 when a name is deleted on sheet1.
> Don't know the usage of formulas in event codes.Any help ?
>
> Thank you.
>
> Worksheet 2
>
> Column A column B
> NameA 1-NameA
> NameA 1
> NameB 2-NameB
> NameB 2
>
|
|
Sorry for responding late to your kind post.
My intention was to speed up data entry by giving a flexible validation
list to data enterer. A validation list which would contain both names
- at top - and some easy codes like 1,2,3 and so on at bottom of the
list. I thought in a week time people wouldn't need use mouse at all
once they learned the codes.So I thought if they have to click the
arrow
They will see a list that'd remind them the corresponded codes as
well.
Say name " Alan Darker " to be picked from the list they click the
arrow and my planned list will appear as below and they will pick
"2-Alan Darker"
1-Jo Pillard
2-Alan Darker
3- ..........
4-..........
5...........
1
2
3
4
5 Next time they will just type 2. And in both cases they will have
only "Alan Darker" in the cell. So on the "Names" sheet I need
below
Column A Column B Column C
Jo Pillard 1 1-Jo Pillard
Alan Darker 2 2-AlanDarker So far no problem.
But below .
..
..
.. So column A and column B and column C sould be merged
somewherelse as
column A column B
Jo Pillard 1-Jo Pillard
Jo Pillard 1
Alan Darker 2-Alan Darker
Alan Darker 2
This column B-sorted- will be used as validation list once picked
"code" or "code-name" only names will be placed.
I found formulas for column B then A but I couldn't prevent the mess
when it came to
Sorting column A & B and couldn't do anything when a name is deleted
from the name list.Finally I restart liking my mouse.
If above is not already too much how can I code below in an event
macro ?
'=IF(B3="","",INDEX(ticketlogAA,MATCH(1,(ticketlogBB<=B3)*(ticketlogCC>=B3),0)))
Thank you for your patience.
Otto Moehrbach yazdi:
[Quoted Text] > Remember that you're the only one who knows what you have, what you want,
> and how you want to get it. Please post back and explain:
> What is on sheet 1?
> What is on sheet 2?
> What sheet has the change event macro and what is the code of that macro?
> What cell on what sheet has the Data Validation?
> What list on what sheet is used in the Data Validation in that cell?
> What do you want updated on sheet 2 when you delete a name in sheet1.
> Be specific. Remember that you are talking to someone who has no idea what
> you are talking about.
> HTH Otto
> <rumkus[ at ]hotmail.com> wrote in message
> news:1156974453.905693.139370[ at ]b28g2000cwb.googlegroups.com...
> > Worksheet1 (names list)
> >
> > Column A column B
> > NameA 1
> > NameB 2
> >
> > I have managed below by worksheet1 change event and I intend
> > to use sheet2 / columnB as a validation list somewhere else to
> > speed up data entering- hate using mouse.
> > Couldn't manage to update sheet2 when a name is deleted on sheet1.
> > Don't know the usage of formulas in event codes.Any help ?
> >
> > Thank you.
> >
> > Worksheet 2
> >
> > Column A column B
> > NameA 1-NameA
> > NameA 1
> > NameB 2-NameB
> > NameB 2
> >
|
|
Tell me, in words, what the formula does, in detail, and I'll code it for
you. You say you want this code in an event macro. What event macro?
There are many event macros. The difference is the event that triggers the
macro to fire. What event do you want? HTH Otto
<rumkus[ at ]hotmail.com> wrote in message
news:1157404726.886259.227330[ at ]h48g2000cwc.googlegroups.com...
[Quoted Text] > Sorry for responding late to your kind post.
> My intention was to speed up data entry by giving a flexible validation
> list to data enterer. A validation list which would contain both names
> - at top - and some easy codes like 1,2,3 and so on at bottom of the
> list. I thought in a week time people wouldn't need use mouse at all
> once they learned the codes.So I thought if they have to click the
> arrow
> They will see a list that'd remind them the corresponded codes as
> well.
> Say name " Alan Darker " to be picked from the list they click the
> arrow and my planned list will appear as below and they will pick
> "2-Alan Darker"
> 1-Jo Pillard
> 2-Alan Darker
> 3- ..........
> 4-..........
> 5...........
> 1
> 2
> 3
> 4
> 5 Next time they will just type 2. And in both cases they will have
> only "Alan Darker" in the cell. So on the "Names" sheet I need
> below
>
> Column A Column B Column C
> Jo Pillard 1 1-Jo Pillard
> Alan Darker 2 2-AlanDarker So far no problem.
> But below .
> .
> .
> . So column A and column B and column C sould be merged
> somewherelse as
> column A column B
> Jo Pillard 1-Jo Pillard
> Jo Pillard 1
> Alan Darker 2-Alan Darker
> Alan Darker 2
>
> This column B-sorted- will be used as validation list once picked
> "code" or "code-name" only names will be placed.
> I found formulas for column B then A but I couldn't prevent the mess
> when it came to
> Sorting column A & B and couldn't do anything when a name is deleted
> from the name list.Finally I restart liking my mouse.
>
> If above is not already too much how can I code below in an event
> macro ?
>
> '=IF(B3="","",INDEX(ticketlogAA,MATCH(1,(ticketlogBB<=B3)*(ticketlogCC>=B3),0)))
>
> Thank you for your patience.
>
>
> Otto Moehrbach yazdi:
>> Remember that you're the only one who knows what you have, what you want,
>> and how you want to get it. Please post back and explain:
>> What is on sheet 1?
>> What is on sheet 2?
>> What sheet has the change event macro and what is the code of that macro?
>> What cell on what sheet has the Data Validation?
>> What list on what sheet is used in the Data Validation in that cell?
>> What do you want updated on sheet 2 when you delete a name in sheet1.
>> Be specific. Remember that you are talking to someone who has no idea
>> what
>> you are talking about.
>> HTH Otto
>> <rumkus[ at ]hotmail.com> wrote in message
>> news:1156974453.905693.139370[ at ]b28g2000cwb.googlegroups.com...
>> > Worksheet1 (names list)
>> >
>> > Column A column B
>> > NameA 1
>> > NameB 2
>> >
>> > I have managed below by worksheet1 change event and I intend
>> > to use sheet2 / columnB as a validation list somewhere else to
>> > speed up data entering- hate using mouse.
>> > Couldn't manage to update sheet2 when a name is deleted on sheet1.
>> > Don't know the usage of formulas in event codes.Any help ?
>> >
>> > Thank you.
>> >
>> > Worksheet 2
>> >
>> > Column A column B
>> > NameA 1-NameA
>> > NameA 1
>> > NameB 2-NameB
>> > NameB 2
>> >
>
|
|
Sheet 1 ( contains ticket log )
Column A Column B Column C
TicketlogAA ticketlogBB ticketlog CC ( range names )
Name 2 51 100
Name 3 100 151
Sheet 2 ( contains sales )
Column A column B column C
Ticket no Name adult pax
71 ?
?=IF(A2="","",INDEX(ticketlogAA,MATCH(1,(ticketlogBB<=A2)*(ticketlogCC>=A2),0)))
How can put above formula in Workseet_change event - that triggers
with column A ?
Thank you very much indeed.
Otto Moehrbach yazdi:
[Quoted Text] > Tell me, in words, what the formula does, in detail, and I'll code it for
> you. You say you want this code in an event macro. What event macro?
> There are many event macros. The difference is the event that triggers the
> macro to fire. What event do you want? HTH Otto
|
|
You and I are having trouble communicating. Answer the following questions
for me:
You say to use a Worksheet_Change event on Column A. Column A of what
sheet?
When you use that formula in your worksheet, you do so in some specific cell
in some specific sheet. That formula can only return a value and it can
only return that value to the cell in which it resides. For me to code the
gist of that formula, I have to know the location of that cell. That means
cell address and sheet.
Again, tell me, in words, what that formula does. Tell me that, in great
detail, as if you were telling someone how to compute that value when that
person knows nothing about your data. HTH Otto
<rumkus[ at ]hotmail.com> wrote in message
news:1157451738.774184.37440[ at ]e3g2000cwe.googlegroups.com...
[Quoted Text] > Sheet 1 ( contains ticket log )
>
> Column A Column B Column C
> TicketlogAA ticketlogBB ticketlog CC ( range names )
> Name 2 51 100
> Name 3 100 151
>
> Sheet 2 ( contains sales )
>
> Column A column B column C
> Ticket no Name adult pax
> 71 ?
>
>
> ?=IF(A2="","",INDEX(ticketlogAA,MATCH(1,(ticketlogBB<=A2)*(ticketlogCC>=A2),0)))
>
>
> How can put above formula in Workseet_change event - that triggers
> with column A ?
>
> Thank you very much indeed.
>
> Otto Moehrbach yazdi:
>> Tell me, in words, what the formula does, in detail, and I'll code it for
>> you. You say you want this code in an event macro. What event macro?
>> There are many event macros. The difference is the event that triggers
>> the
>> macro to fire. What event do you want? HTH Otto
>
|
|
You are so right. Once again my apology
=IF(B4="","",INDEX(ticketlogAA,MATCH(1,(ticketlogBB<=B4)*(ticketlogCC>=B4),0)))
1. My task involves with 2 sheets "sales" and "ticketlog"( both
one row one record )
2. On the "sales" sheet column B has "ticket number" column C
has "sales person"
3. when a "ticket number" is entered "sales person" comes
automatically from sheet "ticketlog" .Above formula resides on
sheet "sales" column"C"
4. On sheet "ticketlog" I have as columns (A) "sales person"
(B) "startno" (C) "endno"
5. In short on sheet "sales" a ticket number is entered and the
formula goes to sheet "ticketlog" and retrives the sales person
name whose a ticket book is log to calculating if the "ticket
number" is between "startno" and "endno"
6. Above formula that retrieves the names from sheet" ticketlog"
uses the range names.
"ticketlogAA" for "sales person name"
"ticketlogBB" for "startno" of the ticket book
"ticketlogCC" for "endno" of the ticket book
Thank you Otto. You are very kind.
Otto Moehrbach yazdi:
[Quoted Text] > You and I are having trouble communicating. Answer the following questions
> for me:
> You say to use a Worksheet_Change event on Column A. Column A of what
> sheet?
> When you use that formula in your worksheet, you do so in some specific cell
> in some specific sheet. That formula can only return a value and it can
> only return that value to the cell in which it resides. For me to code the
> gist of that formula, I have to know the location of that cell. That means
> cell address and sheet.
> Again, tell me, in words, what that formula does. Tell me that, in great
> detail, as if you were telling someone how to compute that value when that
> person knows nothing about your data. HTH Otto
|
|
The 2 macros below will do what you want. The first macro must be placed in
the sheet module of the Sales sheet. The second macro goes into a regular
module. Watch out for line wrapping in this message. I'm sending the small
file I used for this to the rumkus[ at ]hotmail.com address listed in your post.
Post back if this is not a valid address for you. This small file contains
all the code properly placed. Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Column = 2 And Target.Row > 1 Then
Call GetName(Target)
End If
End Sub
Sub GetName(TickNum As Range)
Dim RngColATL As Range
Dim i As Range
With Sheets("TicketLog")
Set RngColATL = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
For Each i In RngColATL
If TickNum.Value >= i.Value And _
TickNum.Value <= i.Offset(, 1).Value Then
TickNum.Offset(, 1).Value = i.Offset(, -1).Value
Exit Sub
End If
Next i
MsgBox "Ticket number " & TickNum.Value & " could not be found."
End With
End Sub
<rumkus[ at ]hotmail.com> wrote in message
news:1157477897.729378.18310[ at ]p79g2000cwp.googlegroups.com...
[Quoted Text] > You are so right. Once again my apology
>
>
> =IF(B4="","",INDEX(ticketlogAA,MATCH(1,(ticketlogBB<=B4)*(ticketlogCC>=B4),0)))
>
> 1. My task involves with 2 sheets "sales" and "ticketlog"( both
> one row one record )
> 2. On the "sales" sheet column B has "ticket number" column C
> has "sales person"
> 3. when a "ticket number" is entered "sales person" comes
> automatically from sheet "ticketlog" .Above formula resides on
> sheet "sales" column"C"
> 4. On sheet "ticketlog" I have as columns (A) "sales person"
> (B) "startno" (C) "endno"
> 5. In short on sheet "sales" a ticket number is entered and the
> formula goes to sheet "ticketlog" and retrives the sales person
> name whose a ticket book is log to calculating if the "ticket
> number" is between "startno" and "endno"
> 6. Above formula that retrieves the names from sheet" ticketlog"
> uses the range names.
> "ticketlogAA" for "sales person name"
> "ticketlogBB" for "startno" of the ticket book
> "ticketlogCC" for "endno" of the ticket book
>
> Thank you Otto. You are very kind.
>
>
> Otto Moehrbach yazdi:
>> You and I are having trouble communicating. Answer the following
>> questions
>> for me:
>> You say to use a Worksheet_Change event on Column A. Column A of what
>> sheet?
>> When you use that formula in your worksheet, you do so in some specific
>> cell
>> in some specific sheet. That formula can only return a value and it can
>> only return that value to the cell in which it resides. For me to code
>> the
>> gist of that formula, I have to know the location of that cell. That
>> means
>> cell address and sheet.
>> Again, tell me, in words, what that formula does. Tell me that, in great
>> detail, as if you were telling someone how to compute that value when
>> that
>> person knows nothing about your data. HTH Otto
>
|
|
Hi Otto
Thank you very much for everything. Received your file aswell.
Perfect result !!
Very kind regards
Otto Moehrbach yazdi:
[Quoted Text] > The 2 macros below will do what you want. The first macro must be placed in
> the sheet module of the Sales sheet. The second macro goes into a regular
> module. Watch out for line wrapping in this message. I'm sending the small
> file I used for this to the rumkus[ at ]hotmail.com address listed in your post.
> Post back if this is not a valid address for you. This small file contains
> all the code properly placed. Otto
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Count > 1 Then Exit Sub
> If IsEmpty(Target.Value) Then Exit Sub
> If Target.Column = 2 And Target.Row > 1 Then
> Call GetName(Target)
> End If
> End Sub
>
> Sub GetName(TickNum As Range)
> Dim RngColATL As Range
> Dim i As Range
> With Sheets("TicketLog")
> Set RngColATL = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
> For Each i In RngColATL
> If TickNum.Value >= i.Value And _
> TickNum.Value <= i.Offset(, 1).Value Then
> TickNum.Offset(, 1).Value = i.Offset(, -1).Value
> Exit Sub
> End If
> Next i
> MsgBox "Ticket number " & TickNum.Value & " could not be found."
> End With
> End Sub
|
|
|