Excel for IOS (no Macros)... Auto Sort List Based on cell value

[Otisair]

Good Morning -

I'm trying to figure out how to have a dynamic list automatically sort without using macros (not allowed in Excel IOS). The setup is this:


Cell E11:E50 contains names of tasks or projects that are abbreviated. In column F, if I enter an X next to a task or project, that task name (in column E) is copied to Cell E1. The next x that is entered in column F is then copied to cell E2. This continues down to Cell E10. At this time, I don't expect to have more than 10 tasks highlighted at one time. The reason for this is, I concatenate each of those task names (spaces in between) and assigned that concatenated value as metadata to an email for quicker retrieval later.

My issue is right now, I can't seem to get the next checked (Column F) task name to copy to the next available cell. I can certainly do this quickly in big boy excel using Macros, but not in the IOS version. As a result, I have spaces between the list in E1:E10. I go through a sort process in E11:E200 and sort it on column F descending to show the X's. Certainly there must be a better way?

Ideas?

Many Thanks!

Shane

 

[mikerickson]

Put this CSE formula in E1 and drag down to E10

=INDEX(E:E, SMALL(IF($F$11:$F$50="x",ROW($F$11:$F$50)),ROW(A1)), 1)

This has to be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

 

[Otisair]

Nice! Working better, but now when I try to concatenate those values in E1:E10, I'm getting a #NUM! return since the values of E1:E10 are formulas. I'm sure I can just do an extra step and copy the values into another column and concat from there.

Many Thanks Mike!

ps. I'll wait a few to "SOLVE" it in case you wanted to add anything else. This is working on my sorting issue for sure though. Thanks

 

[mikerickson]

You could wrap it in an IFERROR(that formula, "") to mask the #NUM errors and make the concatenation work.

 

[chicagocomputerclasses]

What Excel version do you have? You should be able to use macros on a Mac, it's just more limited than Windows version, but it's not like it's not available.

 

[RoryA]

iOS is for iPads/iPods/iPhones. No macros will run on any of those.

 

[chicagocomputerclasses]

Ah, I see.

 

[Otisair]

SOLVED....

I enveloped the original function with the IFERROR function as suggested and all works perfectly. Many Thanks!