Formula to pull latest updates from a row

I am trying to setup a dashboard that gets updated with KPI's monthly. But, I only want the most recent entry to be reflected in the charts/dashboard. I get the gist of how to do it with if/or/isblank formulas, but can't quite get it right. It's reversed right now, I can fix that. Am I on the right track here, just need to write it all out for Jan-Dec?

=IF(OR(NOT(ISBLANK(January@row)), NOT(ISBLANK(February@row))), January@row, February@row)

Find more posts tagged with

Accepted answers

All comments

Jgorsich

That path will work, but it will be a little cumbersome.

You can use the index() function to somewhat mimic Excel's offset function like this:

=INDEX([January]@row:[December]@row, 1, COUNT([January]@row:[December]@row))

The "1" in the middle is to tell it to not offset rows at all so that you are just indexing along the columns.

rachmidd

Above answer did not work for me, returns a blank cell

Jgorsich

ahh - the index command says "move X number of steps into the range" and you are determining "x" with the count function. Here you are moving only 3 steps in and landing on March which is blank. When I answered I'd assumed your range always had the cells filled from left to right.

If you aren't always going to have a value for every entry, you need to get trickier.

For that scenario you need to index through only those months that have entries. I BELIEVE collect() preserves order (though I've not tested it), so collecting all the entries and indexing through just THAT may work.

=INDEX(collect([January]@row :[December]@row,[January]@row :[December]@row ,<>"") , 1, COUNT([January]@row :[December]@row ))

I think that will work - you are first collecting all the non blank values and then indexing to the last one. I haven't tested it though, so try a few test cases to make sure it always works.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.