Cell linking data from new row daily

I am wanting to link a cell from a sheet where each day a new row is added within the same column heading. Example: staffing numbers are reported each day in a new row.

What should I enter in the destination cell so each day the new row, but same column, data is entered?

Or is cell-linking not the right approach?

Answers

  • Katy H
    Katy H Community Champion

    @rogerton I don't think cell linking is the right move here. I would use a helper column titled "Recent Date" that would use a MAX function to pull the most recent date of entry (assuming this is only updated daily)

    The MAX formula might look like this, the {} brackets indicate a cross-sheet reference.

    =MAX({Date Submitted})
    

    Then I would do an index match formula to pull the staffing numbers associated with that date.

    =INDEX({Staffing Numbers}, MATCH([Recent Date]@row, {Date Staffing Numbers Submitted}, 0))
    

    Hopefully this makes sense! If you submit multiple staffing numbers each day you might want to use a unique ID in the source sheet instead of a date column.

    Katy Hall

    Head of Product Management

    ILLA Canna

    LinkedIn