changes made in master worksheet will not mismatch data in other worksheets

Hello,

I have a workbook, with multiple worksheets. currently, I have the names - from column A of the Master ws - linked in all of the attached worksheets. The problem is:

- some of the data is manually entered in some of the attached worksheets, and if I make a change in column A, of the Master, the manually entered data from the other worksheets will not carry through to stay with the original name.

So, if i insert a row and add a new name in column A of the master ws, the names will update in all of the attached worksheets, but if there was any data entered for those names that shifted up or down, the data does not shift.

Is there a macro, or a better way to set up this workbook to avoid these mishaps?

attached is an example to view.

thank you, in advance, for your help!

Kindest Regards,
Paula

Hi Paula,

I like your workbook design, but I think it needs a small design change to work the way you want. If this were my project, I would do the following:

a. Add one column (preferably 'A') to each sheet which originally contains a 'Unique ID Number'.
The 'Unique ID Number' becomes the KEY for all your 'Index-Match' formulas.
'The Unique ID Number' (e.g. 1,2,3,4) is entered on Sheet 'Master' even on rows that have no data (for future additions).
If and when you delete a row on Sheet 'Master' the 'Unique ID Number' for that row dies.
This is needed for the case where a 'Name' is changed.

b. The other sheets (e.g. 'Treat Sum') are completely BLANK on rows that contain NO DATA.

c. When a new name is entered on Sheet 'Master', on all the other sheets that require all rows ('Treat Sum', etc), using VBA (Worksheet_Change() ).
(1) Copy all the formulas from the last row used to the next row.

(2) Put the Value of the 'Unique ID Number' on the new row.

(3) If the New 'Unique ID Number' on sheet Master was 22, then the form of your formulas would change from:

Formula:

Please Login or Register  to view this content.

NOTE that the '22' would have to be 'hard coded' into the formula using VBA.

(4) You can now sort your Sheets any way you want and data would be correct.

d. If you ever deleted a row, you would have to Delete all the rows on the Other Sheets with the same Unique ID Number.
This is tricky but doable. For more information see the code and attached file associated with post #4 in the following thread: http://www.excelforum.com/excel-prog...ml#post3931419

e. On Other Sheets like 'Active M' where you only have a 'Partial List' of Names, manual Data Entry of 'Names' is prohibited. Names can only be selected from a 'ListBox' of existing 'Unique ID Number' and associated 'Name'. The 'Unique ID Number' is hardcoded, and the 'Name' is from 'Index Match' formula.


I could not see any of your VBA code, because your VBA is password protected.

If you have problems implementing any of the above, continue in this thread and upload a sample workbook that contains your code. If your problem is specific, please describe the problem in as much detail as possible.

Lewis