How to remove a row from another worksheet
I have a List of names in Column(A)’Sheet1.
Row1-Judy
Row2-eric
Row3-Ester
Row4-Joe
Row5-John
Case1:
I need to remove the Row which the [name(Eric),Column(A)-Sheet1 ] is in when i key the name(Eric) in Sheet2.
Trick1:
Here’s the tricky part. I have Column(B) & Column(C) in Sheet2.
If a cell in Column(C) has text by using =ISTEXT, it will immediately search between Column(B)’Sheet2 and Column(A)’Sheet1.If the name is found in Column(A)'Sheet1 then it will remove the Entire Row the name it’s in.
Assuming the name’Sheet2 is in the same row as the text to be check(=ISTEXT) in Column(C)
| Attachment | Size |
|---|---|
| DeleteRows.xls | 33.5 KB |
»
- rspeedster's blog
- Login or register to post comments
- 8072 reads
removing rows...
hihi.
What are you actually trying to do ?
Give me a high level idea because the solution to your problem is most probably not the one you are after in this request...
Getting the Whole Idea
Basically i have a list of names(Sheet1) and a whole other data in Sheet1.
Sheet1
S/N Company Check in Name ID ID Expiry Country
1 Fujitsu 01/12/08 Joe 0 696969 30/01/08 China
2 Dell 02/01/09 John 0 686968 02/12/09 Thailand
3 Acer 01/06/09 jim 0 692369 01/12/09 India
Sheet2
S/N Company Check in Name ID CHECK Out
1 Fujitsu 01/12/08 Josh 0 696969 30/01/10
2 Dell 02/01/09 John 0 686968 02/12/10
3 Acer 01/06/09 James 0 692369 01/12/10
So the moment i key the name John in Sheet2,it will remove the entire row which John is in, in Sheet1.
Sheet1
S/N Company Check in Name ID ID Expiry Country
1 Fujitsu 01/12/08 Joe 0 696969 30/01/08 China
2 Acer 01/06/09 jim 0 692369 01/12/09 India
With X1up, S/N Shall automatically roll and leaving Sheet1 without the entire row which John is in.
Here’s the tricky part,upon checking the column ‘CHECK Out if there is text in it, it searches only the entire column of names in Sheet1 & Sheet2.
a step further
lets take another step back...
It seems like you're trying to track people's arrivals and departures...
Tell me more.
Data List
nope,i'm not trying to track people's arrivals and departures. can help?
Delete Rows
I have attached the solution to your first post...
I think however, that this solution will create lots of questions from you, and may not be what you're ultimately looking for..
the solution uses VBA code.. you can learn more about VBA here:
excelexperts.com/Free-VBA-Training-Videos
Solution file:
excelexperts.com/sites/default/files/DeleteRows.xls
Nick