Need suggestions regarding cell reference matrix/array

[wthbloop]

In the project I'm working on, a user should be able to change the value of a cell, and that changes a sheet named Input, which then propagate to other sheets that have that same value.

The problem is, I'm talking about 1000+ references and 3000+ lines of code, using Switch Case in a Worksheet_Change context. This is already making it hell to maintain. For example, user updates 'Client Name' on Sheet4 - 'Client Name' might appear on Sheet1 and Sheet3, but not any others. On Sheet4, there's a reference to the 'Input' sheet, and on the Input sheet, there's a reference to the cells on Sheets 1, 3, and 4.

I'd like to set up a matrix that will hold cell references, and loop through the applicable references. I'm kind of drawing a blank though as to how to accomplish this exactly.

I'd love to hear your suggestions.

 

[StephenCrump]

I'm not sure I'm reading this correctly ...

Normally in a spreadsheet model, I would have an Input sheet for the user to specify variables and parameters, e.g. a range name called ClientName, and have my workings in other sheets using the values from Input.

It sounds like you want to allow the user to be able to change a formula: =ClientName to a value "New Client Name" in any sheet? So you'd need a Worksheet_Change event to recognise that it needs to update ClientName to "New Client Name" in the Input sheet, and change the value "ClientName" in the ActiveSheet back to a formula: =ClientName.

Is this correct, and if so, why do you want to do this?

If not, can you provide more detail on what you are trying to do?

 

[wthbloop]

There are no formulas. I cannot trust sales people not to mess them up.

Right now, I have Worksheet_Change events that write to 'Input' when those fields are edited. 'Input' also has a Worksheet_Change event that writes to every sheet with that particular field (except the sheet that invoked the Worksheet_Change on 'Input', to prevent an infinite loop). This part is fine, and works well.

The problem I'm facing is code maintenance. Every sheet has all the affected 'Input' fields listed. If anything changes on the 'Input' sheet, I have to go through and make changes to every single sheet that references that range. What I'd like is some kind of matrix that stores these range references. I'm not sure how to effectively do that (dynamically) or how to loop through them.

 

[StephenCrump]

I sympathise with your concerns about other people messing up your workbook.

But not with your solution. Effectively you're writing your own dependency tree instead of using the functionality built in to Excel?

But if you do want to pursue this approach, you could set up a hidden worksheet that looked something like this:

Excel 2010

	A	B	C	D
1	Description	Input	Sheet1	Sheet2
2	Client Name	$A$1	$B$2	$C$3
3	Client Number	$A$2	$B$3	$C$4

<tbody>
</tbody>

Hidden​

with dynamic formulae to accommodate rearrangements within each sheet, e.g.
B2: =ADDRESS(ROW(Input!A1),COLUMN(Input!A1))

And have code something like:

'In sheet modules for Input, Sheet1 and Sheet2
Private Sub Worksheet_Change(ByVal Target As Range)

    Call UpdateWorkbook(Target)
        
End Sub
'In a code module
Sub UpdateWorkbook(Target As Range)

    Dim rngNew As Range
    Dim lNoRows As Long, lNoCols As Long
    Dim lColNo As Long, i As Long, j As Long
    
    Application.EnableEvents = False
    
    With Sheets("Hidden")
        lNoCols = .Cells(1, Columns.Count).End(xlToLeft).Column
        lColNo = Application.Match(ActiveSheet.Name, .Rows(1), 0)
        lNoRows = .Cells(Rows.Count, lColNo).End(xlUp).Row
        For i = 2 To lNoRows
            Set rngNew = Intersect(Target, Target.Parent.Range(.Cells(i, lColNo).Value))
            If Not rngNew Is Nothing Then
                For j = 2 To lNoCols
                    Sheets(.Cells(1, j).Value).Range(.Cells(i, j).Value) = rngNew.Value
                Next j
            End If
        Next i
    End With
    
    Application.EnableEvents = True

End Sub

 

[Jerry Sullivan]

@wthbloop, The crux of the problem is that you are trying to create collections of cells across several sheets that all share the same value and each allow user input.

Have you considered using the named ranges with worksheet scope? You could use the same names on each sheet and use VBA Worksheet_Change code to sync those values when a user makes a change to any one of them.

 

[wthbloop]

@wthbloop, The crux of the problem is that you are trying to create collections of cells across several sheets that all share the same value and each allow user input.

Have you considered using the named ranges with worksheet scope? You could use the same names on each sheet and use VBA Worksheet_Change code to sync those values when a user makes a change to any one of them.

Thank you. I wish you had seen my original post about this whole project - it would have saved me loads of time reinventing the wheel.

I sympathise with your concerns about other people messing up your workbook.

But not with your solution. Effectively you're writing your own dependency tree instead of using the functionality built in to Excel?

But if you do want to pursue this approach, you could set up a hidden worksheet that looked something like this:

Please elaborate on the built-in functionality. Are you and Jerry referring to the same thing?

I had originally asked in my original question (different thread) if there was a better way to do what I was trying to accomplish, but unfortunately it went unanswered.

 

[wthbloop]

@wthbloop, The crux of the problem is that you are trying to create collections of cells across several sheets that all share the same value and each allow user input.

Have you considered using the named ranges with worksheet scope? You could use the same names on each sheet and use VBA Worksheet_Change code to sync those values when a user makes a change to any one of them.

I just tried this and maybe misunderstood what you meant. I thought I could apply the same name to a cell on different sheets and changing one would change the rest. All this does is name the cells on an individual Sheet. I still have to refer to them as Sheet1!SomeName, Sheet2!SomeName. While nice for management reasons, it does not eliminate the need to track and call each one individually.

 

[wthbloop]

Effectively you're writing your own dependency tree instead of using the functionality built in to Excel?

I mulled over this during lunch, and yes: I'm writing my own dependency tree because I do not know of an easier way to manually build a two-way dependency.

Your solution is both elegant and effective. Thank you.

 

[Jerry Sullivan]

I just tried this and maybe misunderstood what you meant. I thought I could apply the same name to a cell on different sheets and changing one would change the rest. All this does is name the cells on an individual Sheet. I still have to refer to them as Sheet1!SomeName, Sheet2!SomeName. While nice for management reasons, it does not eliminate the need to track and call each one individually.

A benefit of using this approach is that you wouldn't need to maintain a table to associate the cells that should be sync'd to the same value. The names of these cells could all share the same prefix to flag them as needing to be sync'd. For example....
syncClientName
syncClientNumber

A Workbook_SheetChange event would identify whether a changed cell has a name beginning with "sync", and if so, change the value of any cells that have that same name on their corresponding sheets.

Do you want some help coding that?

 

[StephenCrump]

A benefit of using this approach is that you wouldn't need to maintain a table to associate the cells that should be sync'd to the same value. The names of these cells could all share the same prefix to flag them as needing to be sync'd.

Jerry, nice one! I was thinking you'd still need some sort of table to track the named ranges. A common prefix is a neat solution, and should make for succinct coding.

@wthbloop, on looking again at my approach, I'd do it slightly differently. Rather than looping through every row in the "hidden" worksheet looking for an address match (which in your case sounds like >1,000 iterations), it would be more efficient to loop through each cell in Target (i.e. the changed range), which more often than not would probably mean only one iteration.