find and delete 2 rows

[rumkus]

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

 

[Otto Moehrbach]

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]

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:

 

[Otto Moehrbach]

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]

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:

 

[Otto Moehrbach]

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]

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:

 

[aaron.kempf]

data entry?

in Excel?

learn a database program, like MS Access for example-- or eat shit.

Excel isn't a data entry platform.

 

[Otto Moehrbach]

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 (e-mail address removed) 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]

Hi Otto

Thank you very much for everything. Received your file aswell.

Perfect result !!

Very kind regards

Otto Moehrbach yazdi: