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

[Steve1987]

[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:

=CELL("address",INDEX(Master!B3:GQ420,MATCH(TOC!H6,Master!B3:B420,0), MATCH(TOC!H5,Master!B3:GQ3,0)))[/FONT]
[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]

 

[Snakehips]

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.

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
DataAdd = Right(DataAdd, Len(DataAdd) - WorksheetFunction.Find("!", DataAdd, 1))


Sheets("Master").Range(DataAdd) = NewData
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.

 

[Steve1987]

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]

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]

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]

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.

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
DataAdd = Right(DataAdd, Len(DataAdd) - WorksheetFunction.Find("!", DataAdd, 1))


Sheets("Master").Range(DataAdd) = NewData
End If


End Sub