Need help with a formula.

Roy C

I need some help with the following formula.

=INDEX([Phone 5]:[Phone 5], COUNTIFS([Phone 5]:[Phone 5], NOT(ISBLANK(@cell))))

The formula works until there is no result in the column. The error I'm seeing is #INVALID COLUMN VALUE.

Is there a way for this formula to return "" when no results are found? I've tried a lot of combinations without success.

The basics of what I'm trying to do is take a row and make a column out of it for a dashboard. In the row will be multiple phone numbers with a description or purpose of the number. Our customer will use a form to enter their information, however it will be added to the sheet in a single row. Looking at the data in a single row in a dashboard is not clean like it is when the numbers are in a column.

I'm open to any suggestions.

Thank you,

Roy

Kelly Moore

Hey @Roy C

When you said you weren't able to figure out how to make the Index formula work with a zero inserted - does that mean it was returning the same error as above? You're using the COUNTIFS to return the Row position of your phone number list, which is why I wondered if the MAX/Index would do the same thing

=IFERROR(INDEX([Phone 5]:[Phone 5], 0), "")

Did you try the IF/COUNTIFS formula in the post above? What did it do?

Is it possible for you to give us a screenshot of your data - or even a screenshot of fake data if your data is sensitive? It's always so much easier for the community to help whenever a screenshot is provided.

Kelly

In case you want to refresh on the INDEX function, see below

https://help.smartsheet.com/function/index

L_123

Give this a try

=if(count([Phone 5]:[Phone 5])>0,index([Phone 5]:[Phone 5],count([Phone 5]:[Phone 5])),"")

Roy C

Kelly,

I must have not pasted your script IF/COUNTIFS correctly the first time. The second time it worked!

=IF(COUNTIFS([Phone 5]:[Phone 5], NOT(ISBLANK(@cell)))>0, IFERROR(INDEX([Phone 5]:[Phone 5], COUNTIFS([Phone 5]:[Phone 5], NOT(ISBLANK(@cell)))), ""))

It worked with data and without data.

Thank you soo much!

Roy

Kelly Moore

Hey @Roy C

It's hard for me to picture what you are bringing into your dashboard. I wondered if a report would do this for you.

But yes, you can return a blank upon error.

=IFERROR(INDEX([Phone 5]:[Phone 5], COUNTIFS([Phone 5]:[Phone 5], NOT(ISBLANK(@cell)))), "")

cheers,

Kelly

Roy C

Hi Kelly,

I liked adding the IFERROR and should have worked, for some reason I'm seeing #INVALID COLUMN VALUE where column "Phone 5" has no value in any cell.

=IFERROR(INDEX([Phone 5]:[Phone 5], COUNTIFS([Phone 5]:[Phone 5], NOT(ISBLANK(@cell)))), "")

Below is the format I want the numbers to show up in within the dashboard.

I've attempted to have the reports do this but when the data is contained in a single row I wasn't able to figure it out. I've researched within the community for answers.

Thank you,

Roy

Kelly Moore

Hey @Roy C

For testing purposes, if you eliminate the COUNTIFS portion of the formula and insert a zero in it's place, do you get the same error?

If yes, then try this

=IF(COUNTIFS([Phone 5]:[Phone 5], NOT(ISBLANK(@cell)))>0, IFERROR(INDEX([Phone 5]:[Phone 5], COUNTIFS([Phone 5]:[Phone 5], NOT(ISBLANK(@cell)))), ""))

I'm not sure why the IFERROR doesn't cover this- I've run into it a few times and have never come to a good conclusion.

Also, have you considered a MAX(INDEX(COLLECT()),1). I wondered if that would work

IFERROR(MAX(INDEX(COLLECT([Phone 5]:[Phone 5],[Phone 5]:[Phone 5],@cell<>""),1)),"")

Let me know if any of the above works.

Kelly

Roy C

Hey, Kelly

I wasn't able to figure out how to make the formula work inserting a zero where COUNTIFS section is.

The formula IFERROR(MAX(INDEX(COLLECT([Phone 5]:[Phone 5],[Phone 5]:[Phone 5],@cell<>""),1)),"") worked with just numbers in the cell. I returns a '0' if there is anything other than a number. Having a phone number +1 (4XX) XXXX-XXXX makes it not work for me.

I've also tried helper columns. The issue I ran into with a helper column is it doesn't recognize the error text as text. What I've tried is copying the text when there error text didn't equal what the error returned. The formula worked when I copied the error text to another cell for testing but when it's resulting from an error it doesn't recognize it.

Thank you for all your assistance,

Roy

Kelly Moore

Hey @Roy C

When you said you weren't able to figure out how to make the Index formula work with a zero inserted - does that mean it was returning the same error as above? You're using the COUNTIFS to return the Row position of your phone number list, which is why I wondered if the MAX/Index would do the same thing

=IFERROR(INDEX([Phone 5]:[Phone 5], 0), "")

Did you try the IF/COUNTIFS formula in the post above? What did it do?

Is it possible for you to give us a screenshot of your data - or even a screenshot of fake data if your data is sensitive? It's always so much easier for the community to help whenever a screenshot is provided.

Kelly

In case you want to refresh on the INDEX function, see below

https://help.smartsheet.com/function/index

L_123

Give this a try

=if(count([Phone 5]:[Phone 5])>0,index([Phone 5]:[Phone 5],count([Phone 5]:[Phone 5])),"")

Roy C

Kelly,

I must have not pasted your script IF/COUNTIFS correctly the first time. The second time it worked!

=IF(COUNTIFS([Phone 5]:[Phone 5], NOT(ISBLANK(@cell)))>0, IFERROR(INDEX([Phone 5]:[Phone 5], COUNTIFS([Phone 5]:[Phone 5], NOT(ISBLANK(@cell)))), ""))

It worked with data and without data.

Thank you soo much!

Roy

Roy C

L_123,

Your solution also worked!

Thank you!

Roy

Roy C

Kelly, L_123,

I found an issue with both formulas or probably more a SmartSheet issue. When the data is not in row 1 there is no return of data in the cell. This doesn't make sense to me since the formulas are supposed to be searching the column and not just the first row. With the Formula in multiple cells it was returning the phone numbers and descriptions as expected. When I tried using the same formula in my main sheet I wouldn't get the data. The data is not in the top row in my main sheet but it was in my test sheet. I added a row above the data and all the values that were there disappeared.

Thank you both,

Roy

Kelly Moore

Hey Roy,

Please share a screenshot of your data or a sanitized example so we may test in our own sheets.

Roy C

Good Morning Kelly,

Here is how the data is in the sheet. You can test by placing the data in row 1 to see your formulas work then insert a row before the row making the data in row 2. The Phone columns are Text/Number while the Description is a Drop Down Single Select.

Thank you!

Roy

Kelly Moore

Hey Roy

I've been unable to duplicate needing the formula on the top row. You can mark the post as unsolved (if you cannot mark this because the previous responses were accepted, shout out to one of the group moderators and ask them to do it).

I also wondered if you had access to the premier app, Pivot App. I wondered if it would format your data as you desire.

Sorry I couldn't duplicate your sheet.

Roy C

Hi Kelly,

I'm sorry if I didn't provide enough information about what I'm trying to solve. I don't have access to premier apps. Though is sounds like the pivot app would solve the issue. The screen capture was representative of the data I need to be represented into 2 single columns. All the phone numbers in column A and all of the descriptions in column B.

Thank you,

Roy