Trying to save edited data back to another sheet (reverse vlookup?)

[mstreeter78]

I have a worksheet created that pulls data from a hidden sheet for users to review specific to their department. They can enter adjustments in this main sheet which I would like to save back to the hidden sheet. The main sheet pulls data in columns for each account number using vlookup. The data entered needs to be returned to the same row in the hidden sheet with the corresponding account number. It's difficult to describe without showing it, but hopefully someone can make sense of what I'm trying to say.

In other words, the FY2018, FY2019, and FY2020 columns are pulling the data from the hidden sheet using vlookup. So, it looks to the hidden sheet for the account (e.g. 500-7050-00) and returns the corresponding values ($228,061, $239,804, and $125,486). The user can then adjust the FY2020 projection by entering an amount in the Manual Adjust column. I'd like the amount in the Manual Adjust column to then save back to the hidden sheet on the same row as the account (500-7050-00).

If this still doesn't make sense, please let me know.

Thank you!

 

[onlyadrafter]

Hello,

Do you only have 1 hidden sheet?
When do you want the data to be adjusted in the hidden sheet?
How is the adjustment applied i.e. on first row, what happens to 125,486 when entering 89,633?

 

[mstreeter78]

Yes, only 1 hidden sheet.
I'd like the data to be saved back to the hidden sheet each time a cell is changed.
The adjustment (89,633) is added to the YTD total (125,486) to yield the Projection for year end (215,120).

 

[onlyadrafter]

Hello,

Have assumed the following
In the hidden sheet, ACCOUNT NAMES are in column B, 2018 data is in C, 2019 data is in D, 2020 data is in E. Have also assumed the MANUAL ADJUST is in col F but in the other sheet.

In the 'other sheet' code window copy in the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 6 Then
        MY_ACC = Range("B" & Target.Row).Value
        MY_AMT = Target.Value
        With Sheets("Sheet1")
            For MY_ROWS = 3 To .Range("B" & .Rows.Count).End(xlUp).Row
                If .Range("B" & MY_ROWS).Value = MY_ACC Then
                    .Range("E" & MY_ROWS).Value = .Range("E" & MY_ROWS).Value + MY_AMT
                    GoTo MY_END
                End If
            Next MY_ROWS
        End With
    End If
MY_END:
End Sub

you will need to change Sheet1 in the code to your hidden sheet name.

Does this work as expected?