# Can Index Match Match Results be used to edit master data?

#### Steve1987

##### New Member
[FONT=&quot]Hi everyone,[/FONT]
[FONT=&quot] I have just a questions regarding editing data after using a function result.[/FONT]
[FONT=&quot]I have a multi sheet workbook that runs from a master sheet matrix that has numerous cells of data. As such on the first sheet (TOC) I have an Index Match Match formula that when two criteria are entered the result shows what I am looking for. I.e. is employee X trained on PT Y? Answer= Trained/ Not Trained etc.[/FONT]
[FONT=&quot]I am now wondering is there a way that if I edit this result it will edit the master data. As say the result says not trained, if I change this to trained on the search result it will change the master matrix.[/FONT]
[FONT=&quot]The other sheets in the workbook all feed from this master also.

Below is the formula that I used.
=INDEX(Master!B3:GQ420,MATCH(TOC!H6,Master!B3:B420,0), MATCH(TOC!H5,Master!B3:GQ3,0))

I also have a formula hidden that gives the cell address of the INDEX result:

[FONT=&quot]
So basically is there a function or macro that if I set up a second input section for a user they can then enter a new value for the cell referenced in the INDEX formula

Thanks[/FONT]

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### Snakehips

##### Well-known Member
Steve, welcome to MrExcel.

Here is one way you might achieve that.

Copy this code into the sheets code module.
Edit the result cell address if not correct. ****
Edit the offset from result cell that will give the result address. *****

You can then edit the result cell directly.

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Cells.Count = 1 Or Not Target.Address = "\$J\$5" Then Exit Sub  ' ****** Edit to correct cell that displays the result

NewData = Target

Ans = MsgBox("Are you sure you wish to update the Master listing with this value?", vbYesNo, "Just Checking")
'Reset the original formula
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With

If Not Ans = vbYes Then
Exit Sub  'do nothing if not yes confirmred
Else 'Change value in Master
DataAdd = Target.Offset(0, 1) '*******Assuming formula with result address is to right of Target cell??  *** edit if not
'reduce to just cell reference

End If

End Sub``````

If you have issue with this event triggering whilst you are designing and entering the result formula then temporarily disable it by typing 'Application.EnableEvents = False' in the Immediate pane and hitting Enter.
Then remember to do 'Application.EnableEvents = True' >> Enter when finished. Alternatively add an X before the name of the sub e.g. 'XWorksheet_Change ....' then remove it when done.

Hope that helps.

Last edited:

#### Steve1987

##### New Member
Hi,
Thank you for that,
I have entered in the code into a module for this sheet though nothing seems to happen, I am not great in the slightest with code so any more help or even a walkthrough for dummies would be very much appreciated.

#### Snakehips

##### Well-known Member
Ok,

First off have you put it in the correct place?
It needs to be in the code module for the TOC sheet. Right click the TOC sheet tab >> click View Code will take you to where it should be.

Report back that it is in the right place.
For the moment, edit the sub's name as I said by adding an X before Worksheet....

And...
Tell me the cell that holds the formula returning the data.
Tell me the cell that holds the formula giving the data's address in Master sheet

#### Steve1987

##### New Member
Hi I have placed the code into TOC sheet module and placed an X before worksheet.

The Cell that returns the data is H7 on the TOC sheet and the cell that has the forumla detailing the address on the Master sheet is K7 on the TOC sheet.

Thanks

#### Snakehips

##### Well-known Member
Ok then either edit the existing code to reference the correct cells as below in blue and then remove the X you put in earlier or copy and paste the below to replace original code.

Then the code should run each time you manually edit cell H7.

Rich (BB code):
``````Private Sub Worksheet_Change(ByVal Target As Range)

'Check that it is cell H7 that has triggered the event and if not exit sub i.e. do nothing
If Not Target.Cells.Count = 1 Or Not Target.Address = "\$H\$7" Then Exit Sub

'otherwise.....
NewData = Target

Ans = MsgBox("Are you sure you wish to update the Master listing with this value?", vbYesNo, "Just Checking")
'Reset the original formula
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With

If Not Ans = vbYes Then
Exit Sub  'do nothing if not yes confirmred
Else 'Change value in Master
DataAdd = Target.Offset(0, 3) 'offset 3 columns as address is in K7
'reduce to just cell reference

End If

End Sub``````

Replies
0
Views
110
Replies
5
Views
262
Replies
2
Views
333
Replies
3
Views
508
Replies
0
Views
245

1,190,790
Messages
5,982,928
Members
439,807
Latest member
WXM86

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back