Transferring data in Excel

[0sureal]

Hi all, looking for some help.

I am creating a spreadsheet but i am struggling with a formula/macro.

on sheet one there will be rows of data, with the final tap having a drop down option for "closed" and "open"

what i need the spreadsheet to do: when the option "closed" is chosen, all the data from the row transfers from sheet one to sheet two automatically.

my question, is this possible?
if so, i would really appreciate the help.

thank you.

 

[Trevor_S]

Yes, it's possible! I can see two options:

A "basic" option with formulae - Sheet 2 could contain a lot of "if" formulae. But this could be a lot of formulae if there is a lot of data in Sheet 1, you would need to remember to copy those formulae down, and there would be gaps on Sheet 2 for rows where Sheet 1 still showed "open".

Probably a better option with macros - you could get the macros to copy across the data when the row on Sheet 1 is closed, putting the data on the next available line. This could either be done based on your existing drop down, or you could have an "open/close" button at the top of the sheet which would change the status in the final column of whatever row was selected when the button was clicked, and copy the data across. The button has the advantage that the macro only runs when the button is clicked. Otherwise if its triggered by the drop down, the macro would need to be running in the background all the time, checking for a change in the status column, which could have a minor impact on speed if its a large/complex spreadsheet.

If you take one of the macro options, what would you want to happen if the row was subsequently re-opened or re-closed? Would you want re-opening to remove the line from Sheet 2 - if so, is there a column containing a unique reference that could be used to find the entry on Sheet 2? Re-closing would create a line on Sheet 2, so if the line wasn't removed on re-opening, you could have a duplicate - would this matter?

I'd be happy to draft a macro if you could provide details of:
- the two sheet names
- the columns containing data, including which is the "closed"/"open" status column
- what you want to happen in the event of re-opening (if it's delete the original row from Sheet 2, which column contains a unique reference)
- whether you want the macro to be based on the existing drop down or a button

 

[FDibbins]

Try this...

	A​	B​	C​	D​	E​	F​	G​
1​	Name	header1	Header2		Name	header1	Header2
2​	aa	1​	10​		aa	1​	10​
3​	bb	2​	20​			4​	40​
4​	cc	3​	30​			7​	70​
5​	aa	4​	40​
6​	bb	5​	50​
7​	cc	6​	60​
8​	aa	7​	70​
9​	bb	8​	80​
10​	cc	9​	90​

F2=IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$10=$E$2,ROW($A$2:$A$10)),ROWS($A$1:A1))),"")
ARRAY entered, using CTRL SHIFT ENTER, not just enter
then copy across and down

 

[0sureal]

wow thank you for the info.

i have a sample worksheet in excel if needed.

i like the idea of the macro with the open/close button.

the data wouldn't be reopened, a new one would be created, and they would all have their own unique code.

Date logged	Ticket number	Affected Service	Keyword	Problem Ticket	Incident management instructions	Status	Template Available?
20.12.16	123456	word	word	na	send to microsoft	open	na
21.12.16	789101	internet explorer	performance	na	reset server	closed	na
22.1216	135168	domain	access	na	send to security	work in progress	na

<tbody>
</tbody>

i want it so that the Status column is what triggers the move

 

[0sureal]

wow thank you for the info.

i have a sample worksheet in excel if needed.

i like the idea of the macro with the open/close button.

the data wouldn't be reopened, a new one would be created, and they would all have their own unique code.

Date logged	Ticket number	Affected Service	Keyword	Problem Ticket	Incident management instructions	Status	Template Available?
20.12.16	123456	word	word	na	send to microsoft	open	na
21.12.16	789101	internet explorer	performance	na	reset server	closed	na
22.1216	135168	domain	access	na	send to security	work in progress	na

<tbody>
</tbody>

i want it so that the Status column is what triggers the move

 

[FDibbins]

I created my suggestion using the same sheet for the extract, but it could just as easily be put on another sheet, the adjusted formula (still ARRAY entered), would be
=IFERROR(INDEX(Sheet2!B:B,SMALL(IF(Sheet2!$A$2:$A$10=$A$2,ROW(Sheet2!$A$2:$A$10)),ROWS(Sheet2!$A$1:A1))),"")

 

[Trevor_S]

If you want the macro solutions, then assuming that the data in your example starts in cell A1 of Sheet1, that the sheet to copy to is called Sheet2 and Sheet2 already has a header row:

Option 1 - trigger macro by changing column G to "closed" (macro must be pasted under Sheet1, not ThisWorkbook or a module):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 and Target.Value = "closed" Then
    Target.EntireRow.Copy (Sheets("Sheet2").Range("A1").Offset(Application.WorksheetFunction.CountA(Sheets("Sheet2").Range("A:A")), 0))
End If
End Sub

Option 2 - trigger macro with a button which changes the status in column G of the active row and copies the row across, attach the following macro to a button on Sheet1:

Sub CloseButton()
If Range("G" & ActiveCell.Row).Value <> "closed" Then
    MsgBoxReply = MsgBox("Close Ticket Number " & Range("B" & ActiveCell.Row).Value & "?", vbYesNo, "Enter message box title here")
    If MsgBoxReply = vbYes Then
        Range("G" & ActiveCell.Row).Value = "closed"
        Range("G" & ActiveCell.Row).EntireRow.Copy (Sheets("Sheet2").Range("A1").Offset(Application.WorksheetFunction.CountA(Sheets("Sheet2").Range("A:A")), 0))
    End If
Else
    MsgBoxReply = MsgBox("Ticket Number " & Range("B" & ActiveCell.Row).Value & " has already been closed.", vbOKOnly, "Enter message box title here")
End If
End Sub

Do not have both macros pasted in at the same time, otherwise clicking the button will duplicate the copy (option 2 macro changes the status, which will trigger option 1 macro!).