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.

 

[wthbloop]

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.

Your solution is actually incredibly fast. It's not even noticeable when editing a single value that might propagate to 4 or 5 other sheets. I'm guessing it's because most of the code doesn't even execute because the Intersect returns false.

The initial import, however, takes a minute, but then again I'm importing 400 values from a CSV (web-based sales CRM). They're Sales, they'll live.

@Jerry - I now get what you're saying, and that's pretty neat. I kinda was up against the wall on time, so I went with Stephen's solution.

I really appreciate your guys' help.