Adding a value after last SPILL value

[rizzo93]

I have a SPILL formula -- TRANSPOSE(W3#) -- that transposes a vertical column of values from this:

1
2
3
4

To this:

1 2 3 4

When it's done, I'd like it to automatically add another value after the last spilled value like this:

1 2 3 4 OVERALL

My source is dynamic, so sometimes it ends at 4, while other times it can end on 7, 10, or whatever. But either way, I want to add "OVERALL" right after the last value.

Is there a way to do this in the formula?

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=LET(r,ROWS(W3#),s,SEQUENCE(,r+1),IF(s<=r,INDEX(W3#,s),"Overall"))

 

[RoryA]

Do you have TOCOL() available to you?

 

[rizzo93]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=LET(r,ROWS(W3#),s,SEQUENCE(,r+1),IF(s<=r,INDEX(W3#,s),"Overall"))

Fluff, that worked!! Amazing!

I can now see the potential with LET and SEQUENCE. Very cool. Thanks!

 

[rizzo93]

Do you have TOCOL() available to you?

Alas, I do not. It seems my employer has yet to upgrade us to a version with many other features I'm currently waiting on. But thank you for stepping in!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[rizzo93]

Glad we could help & thanks for the feedback.

Just thought of something, how would I change that formula to have it list the values vertically instead of horizontally?

 

[Fluff]

Just remove the comma inside the sequence function.

 

[rizzo93]

Just remove the comma inside the sequence function.

Got it. Thanks again!

 

[Fluff]

My pleasure.