Creating a template

[sauce1979]

I am currently trying to create a template for user to fill in. i have data as follows:

[B]D_AC        D_RU          D_SBU     f00[/B]
501          207         90001      34
502          208         90021      54
501          208         90001      44
501          208         90312      84
501          209         90001      54
501          209         90211      54
501          210         90021      64
501          210         90001      54


[B]D_RU        D_ENT[/B]
207            H_YJ
207            H_EY
207            H_CY
208            H_EY
208            H_EJ
209            H_EZ
209            H_OO
210            H_YY
210            H_TY
207            H_EY

these two sets of data are on 2 different worksheets. The first is called base data, the second entity. There are further worksheets which contain all the data for a given D_RU. Thus there is a worksheet called 207 which holds all the records where the D_RU = 207 and so on so forth.
The value in column f00 is split between entities which are listed in the entity worksheet depending on the RU number. I need to provide a template which will allow a user to enter the split and then add the new split lines to the base data.

For example:

In the 207 worksheet three more columns should be added to the row where the D_RU IS 2O7. The three rows should be HYJ,H_EY, H_CY as they represent the 3 entities associated with 207. A user can then split the value f00 between those columns. There should be a check which will ensure the sum of those entity colum = f00. Wherever there is a data under entity column added this data should be added to the base data work sheet as a new row. This is shown below.

[B]D_AC        D_RU          D_SBU     f00     H_YJ      H_EY     H_CY[/B]
501          207         90001      34     10           20        4

The base data should then change to:

[B]D_AC        D_RU          D_SBU     f00   D_ENT[/B]
501          207         90001      10      HYJ
501          207         90001      20      HEY
501          207         90001      4       HCY
502          208         90021      54
501          208         90001      44
501          208         90312      84
501          209         90001      54
501          209         90211      54
501          210         90021      64
501          210         90001      54

or something to that effect.
I am not that familiar with excel so any help would be much appreciated

 

[sauce1979]

your assumptions are correct.

the reason for having the separate sheets is because that is the format that is requested. A separate sheet for each d_ru is clear for users.