Ignoring blank cells with INDEX and COUNT

craigPowerlog · 07/02/19

Hello,

I had some fantastic help from Andree with the first part of my sheet, but i'm having trouble to get the below formula to ignore any blank cell in the column. I've tried ISBLANK.....but I cant get it to work...

=INDEX([Generator Location]:[Generator Location], COUNT([Generator Location]:[Generator Location]))

Hope some one can help.

Thanks.

Andrée Starå · 07/02/19

Hi Craig,

Thanks for the kind words!

That is tricky. I'll get back to you if I solve it. Do you have to reference the whole column?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome · 07/02/19

What about using some sort of Row ID to match against? Without screenshots I can't be sure, but it seems like you are looking down a column for the lowest entry.

If that's the case, a row ID could be used in a MAX/COLLECT as the "text_to_search_for" portion within a MATCH function.

=INDEX([Generator Location]:[Generator Location], MATCH(MAX(COLLECT([Row ID]:[Row ID], [Generator Location]:[Generator Location], ISTEXT(@cell)), [Row ID]:[Row ID], 0))

craigPowerlog · 07/03/19

Hello Andree,

Thank you for your help again.

I do need to reference the whole column because the information comes through via form, so new rows are constantly added. I tried limiting to a batch of rows within the column, but it always misses the new data.

I keep going back to it, but its a tricky one to solve!

Thank you again.

craigPowerlog · 07/03/19

Hi Paul,

That's exactly what I'm trying to do. I have a document that my staff fill out for a particular piece of equipment. I'm trying to get the latest location of that piece of equipment so I can track their whereabouts.

I have just given your formula a go and it came up with #INCORRECT ARGUMENT SET....I definitely think you're on the right lines, I've been playing with something similar, but I keep coming up with errors.

The original code I posted works as long as there is information in every cell of that column, but if there are blanks, it seems to stall on the blank cell. So that's why I'm trying to adapt that formula to ignore the blank cells, or do how you say with a row ID....

Thanks again.

Andrée Starå · 07/03/19

There was an error (one parenthesis was missing after @cell) in Pauls formula.

@Paul. Nice work with the formula!

Try something like this.

=INDEX([Generator Location]:[Generator Location]; MATCH(MAX(COLLECT([Row ID]:[Row ID]; [Generator Location]:[Generator Location]; ISTEXT(@cell))); [Row ID]:[Row ID]; 0))

The same version but with the below changes for your and others convenience.

=INDEX([Generator Location]:[Generator Location], MATCH(MAX(COLLECT([Row ID]:[Row ID], [Generator Location]:[Generator Location], ISTEXT(@cell))), [Row ID]:[Row ID], 0))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Paul Newcome · 07/03/19

Andree,

Good catch. Thanks. Those parenthesis have been getting me lately. I'll blame it on the recent coffee brand change. Hahaha.

Andrée Starå · 07/03/19

You're welcome!

They have been messing with me as well lately, but I had a serious talk with them, and now they will behave!

I blame it on the squirrels!

Paul Newcome · 07/03/19

Speaking of squirrels...

I assume you've seen the new functions? It's in the Smartsheet Formula Examples template in the solution center. They've built in a filter to show all of the new ones. I had a total "nerd moment" reading through them. Haha

Andrée Starå · 07/03/19

I saw them yesterday in the function list but did not think about the formula example sheet-

Thanks!

Time to get busy!

Paul Newcome · 07/03/19

I never think about the functions listing. I always use the template. Haha. I guess the functions list would be a better reference though because there is no telling how outdated my sheet from the template really is.

But yes. Definitely time to get busy. Haha

Andrée Starå · 07/03/19

24 new functions

Performance enhancements!

Formulas in contact columns! Wow!

craigPowerlog · 07/04/19

Hello Andree and Paul...

Thank you both! That works perfectly!!

Oooo I'm now off to look at these 24 new functions too.....

Thank you again.

Andrée Starå · 07/04/19

Hi Craig,

Excellent!

Glad that we got it working!

Have fun with the new functions!

Paul Newcome · 07/05/19

Haha. Happy to help, Craig!

Welcome to the Community, where the only ones who actually know what's going on are the highly caffeinated squirrels chasing laser pointers through your brain.

Ignoring blank cells with INDEX and COUNT

Comments

Categories