MJT1985

Q: How to populate a table with data based on a countdown of days

HI

im trying to index information from one table to another based on the date. iPad mini 4, ios9.2

 

image.png

Bshed on the table above, I would like both the product and the quantity from the "new" table to move to the "1 week" table, and the existing information in the "1week" table to move to the "2 weeks" table. The information from the 2 weeks table will move to another table on another sheet (Which has a separate set of peculiarities but that's for amother day)

i would like the information from all the tables to shift every 7 days, all 3 on the same day, and once the "new" table is shifted, I would like the "new" table to be blank.

HOw do I achieve such a miracle?

iPad Mini, iOS 9.2.1

Posted on Mar 29, 2016 12:40 AM

Close

Q: How to populate a table with data based on a countdown of days

  • All replies
  • Helpful answers

  • by t quinn,Apple recommended

    t quinn t quinn Apr 2, 2016 2:03 PM in response to MJT1985
    Level 5 (4,995 points)
    Mac OS X
    Apr 2, 2016 2:03 PM in response to MJT1985

    Hi MJT1985,

     

    Since you can have either a formula in a cell or data but not both we will need to have a data table in addition to the three tables you show. This data table will be where all products are entered along with the date you are entering them. We will need 2 additional columns in this table.

    Screen Shot 2016-04-02 at 2.44.46 PM.png

    D2= WEEKNUM(A2,first-day)

    This is filled down.

    E2= D2+(COUNTIF(D$2:D2,D2)÷1000)

    This is filled down.

    This creates an index that combines the week number with a count of how many entries are in that week. We will use the index to pull data to three report tables.

    Screen Shot 2016-04-02 at 2.50.22 PM.png

    This week::A2= INDEX(Data Input::A,MATCH(WEEKNUM(TODAY(),first-day)+((ROW(cell)−1)÷1000),Data Input::$E,0))

    1 Week Past::A2= INDEX(Data Input::A,MATCH(WEEKNUM(TODAY(),first-day)−1+((ROW(cell)−1)÷1000),Data Input::$E,0))

    2 Weeks Past::A2= INDEX(Data Input::A,MATCH(WEEKNUM(TODAY(),first-day)−2+((ROW(cell)−1)÷1000),Data Input::$E,0))

    These are filled down and then across.

    Make sure the $ are in the correct places.

     

    Once your formulas are working you can wrap them in IFERROR() so you don't need to look at the error triangles. These formulas will work if you have less than 999 entries per week. If instead of dividing by 1000 in each of the 4 formulas you used 100 it would handle 99 entries/week.

     

    Let me know if you have any questions.

     

    quinn