Link rows on different sheets, so same actions apply

[Bloople]

So, now my first problem has been kindly fixed, I have another query. This will just be a nice bonus if I can get it to work.

I have a workbook with an overview sheet of jobs. When I select a city in column 6, for example London, the entire row is copied to the London spreadsheet.
When I come to complete the job, I select Completed in column 9 on the Overview sheet and it copies it the entire row to the Completed sheet and deletes it from the Overview sheet.

What I'm now after is to find a way for that action to delete the duplicate in the London sheet? As it is, I have to go into the City sheet and delete the job manually.

I was advised that there needs to be a unique reference for each job (thanks Fluff) and this might be an issue. Most jobs have a unique reference number but there are some anomolies in which rather than a ref number, it could be a name or address, and these could be repeated in future so it wouldn't be unique. eg, the reference column (Column D) will have 1234, 2833, 1834, and so on, then there might be one where the only reference would be Bloople. And next month, there might be another job relating to Bloople, and you'd never know what number you're up to so can't just add a number on the end.

 

[mumps]

Fluff is correct. You could manually add a new column in the Overview sheet to hold the unique identifier. This could be as simple as using a different number for each job (1, 2, 3, 4, etc.)

 

[Bloople]

Fluff is correct. You could manually add a new column in the Overview sheet to hold the unique identifier. This could be as simple as using a different number for each job (1, 2, 3, 4, etc.)

Brilliant idea!! I'll do that, I'll set it up with 1, 2, 3 and so on. I'd like to do it all ready in advance, 1 to 1000 or something silly, so that entry doesn't have to be input each time, but then, when a row gets deleted, that might mess it up?

 

[Bloople]

Brilliant idea!! I'll do that, I'll set it up with 1, 2, 3 and so on. I'd like to do it all ready in advance, 1 to 1000 or something silly, so that entry doesn't have to be input each time, but then, when a row gets deleted, that might mess it up?

I just tested it and it does lose a row, so job 6 gets completed, then what's left is 5 and the next one is 7, for example. However, it doesn't matter does it... They don't mean anything.

 

[mumps]

That's correct. It doesn't matter.

 

[Bloople]

Does anyone have the code for doing this please?

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of both sheets. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[Bloople]

Just trying to work it out

 

[Bloople]

temp.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1						CITY			STATUS
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
Overview

Cells with Data Validation
Cell	Allow	Criteria
F1:F57	List	=Key!$B:$B
I2:I57	List	=Key!$C:$C

I created a new one without the content but the exact same formatting, so you can see it without all the information being visible.

This is the overview sheet. The city sheets and the completed sheet are copies of this.

When I choose the city from the list in column F, it copies the entire row to the corresponding city sheet.
When I choose Completed on the status menu in column I, it cuts the row and pastes it in the completed sheet.

That all works perfectly thanks to Fluff!
Now, if possible, I'd like that when I choose Completed, that as well as doing what it currently does, it also deletes the corresponding row on the city sheet, that it earlier copied.

So I input details on row 1 on Overview. I choose London and a copy goes in the London sheet, (whichever is the next unoccupied row)
When I've done the job, I choose Completed, and the contents of row 1 are deleted from Overiew and pasted in Completed.
I currently have to go find that job in the London sheet and manually delete it. I'd like it to be linked to the row on the Overview sheet so it gets deleted at the same time.

Does that make sense?

 

[mumps]

In which column have you placed the unique identifier?