sequential letters

I have a sheet where I have parent and child rows, and I would like one column in the child rows to have sequential letters. I have a picture here. I have a parent/child column that would normally be hidden, but I count the number of children so obviously the parent will have a number greater than zero and the children will be 0. I don't know if that will help or not, but in the column "Row", I would like the letters to automatically sequence from A to whatever based off how far away they are from the parent. So the first child row is A, second child row is B, third child row is C and so on.

Anything I can find is always about number sequence, not letter. I'm kind of stumped.

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

What is the possible number of digits you would end up going to? Will it just be A - Z, or could it possibly be A - ZZZZ, or...?

Basically you would need to generate a number and then swap that number out with a letter or letters from a table.

Eric Figge

We generally only go go E or F, something like that, so A-Z would cover it. I'm not for sure how to do the number to alpha conversion in Smartsheets.

Paul Newcome

Ok. If you are only going to E or F, there are a couple of different options.

The first step for all of them is to pick the numeric solution of choice. I will refer to this throughout the example as number formula.

The most straightforward way to do this would be to use a nested IF.

=IF(number formula = 1, "A", IF(number formula = 2, "B", IF(number formula = 3, "C", IF(..............................))))))

But depending on your number formula, this could get pretty lengthy and doesn't lend well to maintenance if you needed to change a letter associated with a number or add or deleted a number/letter.

Another option would be to create a table where each letter is going down 1 column and each number is going down another.

Letter.....Number

A..................1

B..................2

C..................3

D..................4

E..................5

F..................6

Then use a formula such as

=INDEX(Letter:Letter, MATCH(number formula, Number:Number, 0))

This allows the absolute most amount of flexibility and ease of maintenance combination as the formula will not need to be updated, you can extend the table to account for as many letters/numbers as you want, and the letter/number combos don't have to be in any specific order.

A third option would be similar to the second, but it would be used only if the letters are going to go in numerical order (letter/number 1 at the top, letter/number 2 next, so on and so forth).

For this option you can create only the Letter column, and use this formula:

=INDEX(Letter:Letter, number formula)

Eric Figge

I'm assuming the best place for this table would be in a separate sheet?

Paul Newcome

That would depend on how you are using the sheet as well as exactly which solution you are using. If you are sorting the sheet or adding new rows, then going with solution 2 using an INDEX/MATCH would allow you to keep the table on the same sheet in hidden columns.

If you are deleting rows or you want to use only the INDEX function (option 3), then you will want to use another sheet.

The absolute biggest factor would be whether or not you are deleting rows (or moving them to another sheet). If you are not doing that, then you can keep it on the same sheet (if you prefer).

Jennifer M.

@Paul Newcome I am attempting to use the above example you gave for using sequential letters, combined with the answer you gave for parent-child numbering here: https://community.smartsheet.com/discussion/64903/parent-child-numbering. I am running into an issue where "A" is not counted due to the Ancestor Column having "0" and blanks in my "Two" Column. Any suggestions on how I can have the A included in the first child row for the numbering in the WBS column?

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