I have a simple spreadsheet with an absence calendar (https://docs.google.com/spreadsheets/d/1PfuGPIyWawiBE2Czz9_PPKFk59Vn9eNUF2PFiXu2Wu8/edit?usp=sharing) that shows me if a person is absent today. I also want to see the date when each person comes back. How can I find a next empty cell in a row and get the day (first row) from the column with the empty cell?
1
-
While a link to a spreadsheet might be helpful, as the spreadsheet might edited or no longer available, and also people willing to answer should not be forced to follow a link to an external place, all the relevant details should be included directly in the question.– Rubén ♦Sep 15 at 22:02
Add a comment
|
3 Answers
I came with this solution
=index(B1:H1;0;find(";;";join(";";B2:H2))/(len(B2)+1)+1)
The logic is that it concatenates all the columns into one text string separated by ";". It then finds the first instance of ";;" which is the first empty space. It then transforms this location into counts and index it at the dates row.
You might need to change some ";" to "," in your spreadsheet depending on your location
A modified solution based on yours when the length of values are not constant:
=index(B1:1;0;LEN(REGEXREPLACE(LEFT(JOIN(";"; B2:2);FIND(";;";JOIN(";"; B2:2)));"[^;]";""))+1)
Explanation:
LEFT(JOIN(";"; B2:2);FIND(";;";JOIN(";"; B2:2)))convert the columns to;separated string, and keep the part before first;;(blank cell)REGEXREPLACE({above}; "[^;]";"")remove all non-;characters- Count number of
;s withLEN
Needed a formula that could figure out the sum of a list of numbers in the same column below the current cell up to the next blank cell, came up with this, I know this is an old post but I hope it helps someone else:
=SUM(ARRAYFORMULA(INDIRECT(ADDRESS(ROW()+1,COLUMN())&":"&ADDRESS(ROW()+((FIND("TRUE",JOIN("",ARRAYFORMULA(ISBLANK(ARRAYFORMULA(INDIRECT(ADDRESS(ROW()+1,COLUMN())&":"&LEFT(ADDRESS(ROW(),COLUMN()),SEARCH("$",ADDRESS(ROW(),COLUMN()),2)-1)))))))-1)/5),COLUMN()))))
Broken down:
ADDRESS(ROW(),COLUMN())
[ $A$1 ]
Current cell's address
SEARCH("$", [ $A$1 ] ,2)
[ 3 ]
Position in cell address of second "$" (starts at position "2" to ignore the first "$")
Note: this is to account for the column having 2 or more letters, for example if you put this formula in cell $AA$1 it will return "4" instead of "3"
LEFT( [ $A$1 ] , [ 3 ] -1)
[ $A ]
Text left of second "$" in current cell's address
ADDRESS(ROW()+1,COLUMN())
[ $A$2 ]
Address of cell below current cell
ARRAYFORMULA(INDIRECT( [ $A$2 ] &":"& [A] ))
[ $A$2:$A ]
Array from cell below current cell to bottom of sheet
JOIN("",ARRAYFORMULA(ISBLANK( [ $A$2:$A ] )))
[ FALSEFALSE... ]
Array with each cell converted to "FALSE" if it contains anything or "TRUE" if it's blank, then joined into a text string
((FIND("TRUE", [ FALSEFALSE... ] )-1)/5)
[ 10 ]
The position directly before ("-1") the first instance of "TRUE" in the text string divided by 5 (the number of letters in "FALSE")
ADDRESS(ROW()+ [10] ,COLUMN())
[ $A$11 ]
Address of cell directly above next blank cell in column
SUM(ARRAYFORMULA(INDIRECT( [ $A$2 ] &":"& [ $A$11 ] )
[ SUM($A$2:$A$11) ]
Sum of all cells below current cell up to next blank cell
-
It isn't clear if you are answering a slightly different question or if it is a solution that will address the OP's question. Also, your answer would benefit from sample data before and after in markdown format as it is extremely long and verbose and appears to require a lot of investment from a reader to understand it or confirm it applies to the question and/or is correct. Consider editing [edit] the answer for clarity.– Blind Spots ♦Sep 16 at 21:09