How to pull data from most recently created column?

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

Find more posts tagged with

Accepted answers

Leibel S

@Sofia Fernandez

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

A couple steps here:

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
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.

All comments

Sandra Guzman

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

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

Screen Shot 2021-01-13 at 11.42.05 AM.png

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

@Sofia Fernandez

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

A couple steps here:

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
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

Thank you very much! @Leibel S and @Sandra Guzman

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.