referncing dynamic worksheets

[allie123]

Hi everyone,

I work on a workingbook call "Contract Traking". There is master sheet call 'Contracts' and a blank subsheet 'Contract Detail'. The master worksheet 'CONTRACTS' - includes all Contract Name (from A4), all customer name(B4), funding...ect. The subsheet describe each contract detail which includes Contract Name, customer name, hours...The master worksheet -CONTRACTS and subsheet - contractDetail reference to each other. I aslso write a user form that user can fill out the value into CONTRACTS - The mastersheet, copy the balnksheet pass relative value into the subseet.

The problem I need to solve is the subsheet was created dynamically. Each subsheet all reference to Mastersheet some specific cell value. If the master sheet cell value changed, the subsheet relative cell value should be update automatically. For example,

* All the subsheet were created by VBA CODE after input new contract in the
mastersheet (CONNTRACTS)
* MasterSheet cell A5 (Cusomter Name) was change then ,Subseet(1) B2
value (Client Name) also update automaticlly.
* MasterSheet cell A6 (Cusomter Name) was change then , Subseet(2) B3
value (Client Name) also update automaticlly.
* If subsheet1 C2 vlaue (hours) changed, MasterSheet 'CONTRACTS' D5 value (Hours) also update automaticlly.
* If subsheet2 C2 vlaue (hours) changed, MasterSheet 'CONTRACTS' D6 value (Hours) also update automaticlly.



I know it is relate to reference and range. I tried to write different formulas. None of them worked. I'll appreciate the help if anyone let me know to solve the problem with formula or VBA code. Thanks


Allison