Data transfer between worksheets...

[looeej]

I want to learn formulas involving VLOOKUP and IF commands for existing worksheets generated with a crystal report. In the attached abbreviated report I want to populate the Schd sheet with data from the Rpt sheet. I have an example on Row 2 Schd I hope to create. It combines D2, E2, and C2 from Rpt in one cell (B2, Schd)
for this creation (if possible). Also it calculates a percentage (E2, Schd) based on estimated (K2, Rpt) and actual times (L2, Rpt) derived from the report. I would also need it to populate by the employee name as shown. I hope I'm being realistic to think this is achievable. Any and all support very much appreciated with this project.

Thanks in advance...

[looeej]

I just tried using concatenate and the IF command but not working...is it even possible to do like this example?

=CONCATENATE(IF(Rpt!A2:A11,"Bill",TEXT(Rpt!D2,"0000")),
IF(Rpt!A2:A11,"Bill",TEXT(Rpt!E2,"0000")),
IF(Rpt!A2:A11,"Bill",TEXT(Rpt!C2,"0000")))

This would populate the Sched B column under Bill only as an example.

[looeej]

So I did figure a way to utilize the concatenate feature on the RPT sheet. Then simply bring across to the Schd sheet. Still struggling with VLookup and the whole merging of cells idea. My formula in B7 on Schd isn't working. I'd appreciate any help...thanks all and have a good rest of the night !!

[FDibbins]

1st, your name Bill has a trailing space, you need to remove that - check the other names too.
For the data extraction...
B2=IFERROR(INDEX(Rpt!$D$2:$D$11&", "&Rpt!$E$2:$E$11&", "&Rpt!$C$2:$C$11,SMALL(IF(Rpt!$A$2:$A$11=$A$1,ROW(Rpt!$A$2:$A$11)-1),ROWS(Rpt!$A$1:A1))),"")
C2=IFERROR(INDEX(Rpt!$A$2:$L$11,SMALL(IF(Rpt!$A$2:$A$11=$A$1,ROW(Rpt!$A$2:$A$11)-1),ROWS(Rpt!$A$1:B1)),MATCH(C$1,Rpt!$A$1:$L$1,0)),"")
copy to D2 as well
E2=IFERROR(INDEX(Rpt!$K$2:$K$11,SMALL(IF(Rpt!$A$2:$A$11=$A$1,ROW(Rpt!$A$2:$A$11)-1),ROWS(Rpt!$A$1:D1)))/INDEX(Rpt!$L$2:$L$11,SMALL(IF(Rpt!$A$2:$A$11=$A$1,ROW(Rpt!$A$2:$A$11)-1),ROWS(Rpt!$A$1:D1))),"")
(I may have tehse the wrong way round, if so swap the 2 INDEX() pieces around)

All the above are ARRAY formulas


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[looeej]

Your arrays worked aside from the date. Not sure what's up with that? Having the ability to reach out to a forum like this is outstanding and deserves applause IMO. You guys and all the followers are brilliant people and deserve the utmost respect for your continued inputs. Just an incredibly happy experience coming here !!

J

[looeej]

The date issue was simply the Match command in the array not identifying the correct verbiage requirement. Reading and following the arrays provided me allowed me to correct that. I'm just elated with all this and again so appreciate this forum !!!

[FDibbins]

awesome, Im glad it worked for you

Another thing to keep in mind when pulling in dates like that, is that they could be pulled in under general format, and then they look like a 5=digit number instead...you just need to format them as date