How to pull data from most recently created column?

Options

I have data that I upload manually every month, and would like to reference it into another sheet; however I only want the last column created to be referenced and I want it to happen automatically without having to change the formula to reference a new column

So in the dashboard it'd hold this info..

person x || most curren RF

and then on a different sheet it'd hold

person x || dec 2020 RF || jan 2020 RF || etc

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options

    @Sofia Fernandez

    Assuming your last RF column is always at the end of the sheet, this can be done.

    A couple steps here:

    1. Create a column at the end of your sheet (after the last RF column) let's call it 'BLANK'. You wont be filing out anything here at any time
    2. Create a column after that one called LATEST RF. The formula for this would be the below. Replace the month #1 to match your first column...

    =IF(COUNT([month #1]@row:BLANK@row) > 0, IFERROR(INDEX(COLLECT([month #1]@row:BLANK@row, [month #1]@row:BLANK@row, @cell <> ""), COUNT([month #1]@row:BLANK@row), 1), ""))


    Any subsequent months added to the sheet should be inserted to the left of the BLANK column. This would automatically include them in the LATEST RF formula.

    Your second sheet should reference the LATEST RF column.

Answers

  • Sandra Guzman
    Sandra Guzman ✭✭✭✭✭✭
    Options

    Hi @Sofia Fernandez!


    I would suggest creating a report that pulls information from your original sheet and select the column you wish to display. You will have to set a filter to display what you would like to see visually. There may be more involved in your set up to get the data to pull through accurately. Then you could publish the report widget in your dashboard.


    You would have to share redacted information with the columns and details shared in order to get more specific direction on what to do.


    All the best,

    Sandra

  • Sofia Fernandez
    Sofia Fernandez ✭✭✭✭
    Options

    I don't think that would work, And I don't think you've caught what the issue is.


    Every month I add 3 or 4 columns to this sheet. As you can see here. I only want the most recent (the right most) column to be linked to a particular cell in another sheet.


    So that when I open my other sheet, I always have the most current value for the "RF"


    Does that make sense?

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓
    Options

    @Sofia Fernandez

    Assuming your last RF column is always at the end of the sheet, this can be done.

    A couple steps here:

    1. Create a column at the end of your sheet (after the last RF column) let's call it 'BLANK'. You wont be filing out anything here at any time
    2. Create a column after that one called LATEST RF. The formula for this would be the below. Replace the month #1 to match your first column...

    =IF(COUNT([month #1]@row:BLANK@row) > 0, IFERROR(INDEX(COLLECT([month #1]@row:BLANK@row, [month #1]@row:BLANK@row, @cell <> ""), COUNT([month #1]@row:BLANK@row), 1), ""))


    Any subsequent months added to the sheet should be inserted to the left of the BLANK column. This would automatically include them in the LATEST RF formula.

    Your second sheet should reference the LATEST RF column.

  • Sofia Fernandez
    Sofia Fernandez ✭✭✭✭
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!