Forum Discussion

Marc von Osinski's avatar
Marc von Osinski
Copper Contributor
Oct 25, 2018

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

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!  

 

 

 

 

excel
Formulas and Functions
office 365
  • erol sinan zorlu's avatar
    erol sinan zorlu
    Iron Contributor
    Nov 05, 2018

    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.

    • Marc von Osinski's avatar
      Marc von Osinski
      Copper Contributor
      Nov 20, 2018

      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

Resources