Create worksheet from data in another worksheet with lots of conditional or if...then ???? HELPT

[Marc von Osinski]

Weekly I have to manually create a worksheet from data in another worksheet and there has to be a way to do it, I just don't know how?  Working from a schedule sheet that creates goals for the people working each day I want to create another worksheet that only shows the people working each day and also their associated goals, then create a capital letter "U" underneath them for each unit of their goal.  The first sheet is the one I'm using the data from:

Source worksheetThis is the one I want to makeThe one above is the form I want to automatically be generated from the source worksheet.  The only thing I could figure out was something like "check cell 1 if true then put the name of that person here for Sunday, if not then check cell below it, and so on and so on.....

Any help would be greatly appreciated!!  Thank you!  

[#2]

if your source page is same you can use "MATCH", "INDEX"/"OFFSET" formulas to get data. For example in your pictures the dates are in the first row. You can find the that days column with below formula:

=MATCH(TODAY,SheetName!$1:$1,0)

this will return the column number of the date for example 2 for "8/26/18). you need to add 1 if you want to get the Goal/Hrs.

then you need to find the related person:

=MATCH(PersonName,SheetName!$A:$A,0)

this will return 8 for bob

then you need to use and index or offset formula to get the required data

=OFFSET(SheetName!$A$1, MATCH(PersonName,SheetName!$A:$A,0)-1, MATCH(TODAY,SheetName!$1:$1,0)-1,1,1)

or 

=INDEX(SheetName!$A$1:$R$20, MATCH(PersonName,SheetName!$A:$A,0), MATCH(TODAY,SheetName!$1:$1,0))

needless to say you need to replace generic names like SheetName and PersonName with the actual value you have.

[#3]

Erol,

  I'm sorry but I'm  a little new to this kind of thing in excel.  I don't really understand how all that works.  So in the second sheet I already have the day, date, plan, etc, referencing the correct cell on the first sheet for each day because that is fairly static.  Will what you told me to do start at the top under "Sunday" and look for the first person listed under that day, list their name, unit goal, and dollar goal.  Then search again starting after that person and going down until they find the next person working etc until it finds them all?  It may not be possible but I'm hopeful.  Sorry again for not being more knowledgeable about this.

Thanks again,

Marc

[#4]

can you share the workbook?

[#5]

I've attached the workbook.  The tabs I'm working with are Associate Goals and Daily Goals.

Thanks!

[#6 · Accepted solution]

Hello,

Please check the attached file. I have created formulas for "Bob". You can create similar ones for other people.

[#7]

Thanks so much!  It'll probably take me a little bit to study and make sure I understand it.  Once I do I'll let you know how it goes!  THANKS AGAIN!

[#8]

It took some time but I think now I'm getting it!  Thanks so much, this will save me so much time.  Why did you use "ROUNDUP?"   When I changed the formula for Mack it returned 7 not 6 which is what his goal is for the day?

Thanks!

[#9]

Well for the number of U's and C's I thought this number was being used. The formula for Bob returned 2,5 and I used roundup to find the closest higher integer. than used formula to generate exact number of U's and C's in below table.

[#10]

Why doesn't it just copy the value in the cell it's referencing??  Or is it copying the exact number not the one shown?  I want it to copy the value shown in the cells.  Is that possible?

[#11]

I guess it shall be ROUND(<>,0) instead of ROUNDUP if you format numbers to integers.

[#12]

you can delete the "ROUNDUP(" at the beginning and ";)" at the end for formula to return exact value. How do you define the number of "C"s and "U"s in the tables below?

[#13]

In the AssocGoals sheet when making the goals after the formula I decreased the decimal places in the cell to 0 decimals-I guess that rounded them up or down.  Except for Bob, Sally, and Ralph, they all have set numbers if they work--there's an "IF...THEN"  that says if they work that day they have 2 as their goal.  Everyone else is determined from the formula above then decimal places removed in the cell.  Does that help?