Sheet 1 and 2 are a Timesheet setup in a table. Table1 Sheet1 and Table2 Sheet2. Sheet 1 is the 1st 2 weeks of the pay period, Sheet 2 is the second 2 weeks. Formulas in the Table autofill in case a new row needs to be added based on spit days.
What I need to do is copy data from 1 of the rows to a separate sheet that will be a form used to track exactly what task are done that day if its under a specific grant. out of the possible grants only 3 need this. The user will complete most things on the form which will be sheet3 4 5 etc... The data moved over is used for uniformity and currently because its on different documents and does not auto fill users screw up the hours they work and total hours for the grant. IE the need to copy it from the timesheet so pay roll prove the data if a grant audit occurs. You can image the headaches and legal troubles based on a screwup by the user. This will keep the data the same and no screwups.
So.....
The grant code DS, DA, and DOP are located in column K13 thru K26 (if no new rows are added). If K13 on Table1 has the DS code I need to fill in data from B C E K and L on a new sheet. Then if the next time one of the above codes is used I need a second sheet to fill in the data only from that row. Say K18 on Table 1. then the next time its K15 on Table2.
Any ideas how to do this?
I have considered using Sum&countif to tell me how many times the grant is used. Then i would need a formula to find the 1st time its used on Sheet3 and Sheet4 would look for the second time its used and so forth.
I dont want to use Macros because of security and VB might be to cumbersome. It would be great not to have 40 sheets and have to put formulas in each but have excel auto create a new sheet based on how many codes are used but I assume that has to be coded in VB.
my intention is to put 8 new sheets and use formulas on each one to look for the first grant code used the second, third, etc... and fill in the required data.
Any help would be great.
What I need to do is copy data from 1 of the rows to a separate sheet that will be a form used to track exactly what task are done that day if its under a specific grant. out of the possible grants only 3 need this. The user will complete most things on the form which will be sheet3 4 5 etc... The data moved over is used for uniformity and currently because its on different documents and does not auto fill users screw up the hours they work and total hours for the grant. IE the need to copy it from the timesheet so pay roll prove the data if a grant audit occurs. You can image the headaches and legal troubles based on a screwup by the user. This will keep the data the same and no screwups.
So.....
The grant code DS, DA, and DOP are located in column K13 thru K26 (if no new rows are added). If K13 on Table1 has the DS code I need to fill in data from B C E K and L on a new sheet. Then if the next time one of the above codes is used I need a second sheet to fill in the data only from that row. Say K18 on Table 1. then the next time its K15 on Table2.
Any ideas how to do this?
I have considered using Sum&countif to tell me how many times the grant is used. Then i would need a formula to find the 1st time its used on Sheet3 and Sheet4 would look for the second time its used and so forth.
I dont want to use Macros because of security and VB might be to cumbersome. It would be great not to have 40 sheets and have to put formulas in each but have excel auto create a new sheet based on how many codes are used but I assume that has to be coded in VB.
my intention is to put 8 new sheets and use formulas on each one to look for the first grant code used the second, third, etc... and fill in the required data.
Any help would be great.
