Copying data from one table to another w/good formatting. Too many IFs to keep track of

[MacHead]

*** Updated post with Workbook Upload***

Hello- This website has been immensely helpful to me as I've been doing a crash course of sorts in Excel for my job. I'm especially fond of "copy/paste link" to automatically duplicate info onto different worksheets.

But now I'm stumped. I need to automate data entry from one worksheet tracking time per project into another that serves as an invoice. My problem is that this involves bringing over both both nominal and quantitative data (i.e., employee's name/role and # hours worked). I am tracking hours worked on each project (x axis) by each employee (y axis). If they worked any hours on a project (project hrs cell>0), then I need their name and role to appear on the invoice page along with the number of hours worked. If not, then I don't want their name to appear on the invoice under that particular project, even with a 0 hrs listed by their name.

Is there a way to do something like =IF(project hrs cell>0, "John Doe Architect" + project hrs cell, 0) wherein i don't have to separate the nominal and quantitative data? And instead of having 0 as the alternate,... just have nothing?

Lastly, if an employee doesn't work any hours on a project, how can I make the next employee who does work on the project have their data transferred to the invoice without any empty rows in between? Is this possible?

machead prob.xlsx (apologies any odd formatting- had to edit and upload this on my mac from home, which has Numbers instead of Excel.

Thank you !!!

[mcmahobt]

It's hard to adequately understand your problem without a sample workbook. Images are sometimes helpful, but more often than not, uploading a sample workbook that omits sensitive data is best. Plus, nobody likes recreating data

[Radridz3]

I am new here also and following this closely for info about how to take a crap ton of IF , AND , OR , THEN statements out of a couple sheets I'm working on. this is kind of the same principle in theory. No matter what I was gonna post mine cause I am stumped. Im pretty good at Conditional formulas and writing code with instruction sets but I cannot for the life of me figure of my hang up.... Best of luck....

[mcmahobt]

I may have misunderstood, but are you able to post a sample workbook or not?

[MacHead]

Good tip. I have spent 8 hours doing all sorts of new formulas for other projects. I will see if I can figure out how to upload a workbook (minus sensitive data) here to better explain my situation. =)

[mcmahobt]

See attached for a possible solution. I can appreciate an attempt at setting up an invoice log, but a little formatting and database management goes a long way. I went directly against what you had asked, and split up the merged cells in columns A:D within the Inv worksheet. I could have kept them merged and input the formula, but the resulting expression would have been incredibly unwieldy.

I have provided a few examples of what the formulas will produce if the Time Sheet is populated, but note that there was no Type field in the Time Sheet, so there is no formula within that field on the Inv tab.

[MacHead]

I am so glad you went against what I had asked and unmerged the cells A-D. Oh my gosh,... it's beautiful. It works! It's amazing. Thank you.

I'm going to study it and continue learning the various functions/formulas. I am so grateful.
As to "type", i have no idea what that is, and it isn't on the time tracking sheet. I assume someone else from the team completes that part.

I owe you.

[MacHead]

Looking at the formula you plugged in to transfer info over to the Invoice worksheet:

A1 =IFERROR(INDEX(ROW('time sheet'!$A$10:$A$20)-9,SMALL(IF((ISNUMBER('time sheet'!$D$10:$D$20))*('time sheet'!$D$10:$D$20>0),ROW('time sheet'!$A$10:$A$20)-9),ROWS($1:1))),"")

I assume $A$20 refers to the fact that my employee list on the time tracking work sheet extends down to row 20? If I add more employees to my worksheet for tracking hours, would I simply substitute out that number? Example, if I added enough employees such that my list reached down to row 30, would I do:

A1 =IFERROR(INDEX(ROW('time sheet'!$A$10:$A$30)-9,SMALL(IF((ISNUMBER('time sheet'!$D$10:$D$30))*('time sheet'!$D$10:$D$30>0),ROW('time sheet'!$A$10:$A$30)-9),ROWS($1:1))),"")

I tried that and it didn't copy over any info to the invoice for employees beyond the 20th row still. So is there something else I need to manipulate?

[mcmahobt]

The formula I had set up is an array formula. These differ from normal formulas because they need to be entered with CRTL+SHIFT+ENTER, not just ENTER. What does that return?

[MacHead]

I THINK IT IS WORKING! I haven't changed the way I've been doing it as far as I can tell, but it is working today. ....

thank you... Will continue working on it and copying over the actual company data into the worksheets=)