ST Alpha - "TTIS spreadsheet help: I want to find the first and last non-blank values a in a row of cells -- (edited)" (Reading a Post)

SlickerTalk
Search Archives
The Leaderboard
The FAQ

Down to Post

Backboards:

Posts: 155

TTIS spreadsheet help: I want to find the first and last non-blank values a in a row of cells -- (edited)

Posted by Max Sep 6 '21, 20:17

My A1:G1 column headers are Sunday, Monday, Tues etc.

Cell values
A2
B2
C2 Rain
D2
E2
F2 Snow
G2

My 'first' return cell formula is
INDEX(A2:G2,MATCH(TRUE,INDEX((A2:G2<>0),0),0)) which will show "Rain" as the value from the first cell from the left which is not blank.

I want my 'last' return cell formula to show the the value from the (last) cell from the right which is not blank, in this case F2 "Snow". If I were to add the value "Hail" to G2, then the formula would show "Hail."

Responses:

aha, here's the solution from my favorite channel, and the answer appropriately involves the MAX function. -- (edited) - Max Sep 6, 20:59 [youtube]
pretty sure there's an easy way to do it using macros, but I'd have to go and find it. -- nm - znufrii Sep 6, 20:48
I'm guessing G2:A2 doesn't start the search in G2 and proceed up? -- nm - crash davis Sep 6, 20:43 1
- I can't figure out how to make it start the search in that direction, so I'm thinking I need Transpose and maybe Sort and Rows. -- nm - Max Sep 6, 20:46

Post a message top

TTIS spreadsheet help: I want to find the first and last non-blank values a in a row of cells -- (edited)

Replies are disabled on threads older than 7 days.