Transfer Data from 1 sheet to another given conditions.

blanx

New Member
Joined
Mar 10, 2018
Messages
11
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.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609
You could use an array formula with 1 or more criteria to consolidate data into another sheet.

It would be easier to deal with a small sample of both tables, if you can post?
 

blanx

New Member
Joined
Mar 10, 2018
Messages
11
here are 2 pictures, one of part of the timesheet that matters. and the second is the grant sheet that only has the data required to copy over. the grant code says circle but it can just fill in the code used. https://ibb.co/yN7Q8zb
https://ibb.co/4FKND1K

I couldn't figure out how to attach the pictures so the links are for them
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,386
Messages
5,468,296
Members
406,579
Latest member
lollypop1389

This Week's Hot Topics

Top