GOOGLE SHEETS: How to find and return the nth non blank bell value based on today's date column

[songo]

Hi All,
I have tricky question, want to combine HLOOKUP with Array & Index, functions.
What I am trying to accomplish, I have sheet that’s needs to be updated every day automatically, on the top of the sheet I have =today() function.
Now I have second sheet, it’s actually scheduler with team stuff, dates and planned additional activities in hours.
And here is the trick… not all people have such a activities, some of them don’t and cell in blank.
I want excel to find today’s column in scheduler (obviously HLOOKUP – but instead of putting index (row number) I want excel to pick up nth non blank value.
Below is link to table of scheduler example,
today - =today() is 26^th I want excel to show number 2.
Tomorrow- =today() will be 27^th I want excel to show number 3.

https://drive.google.com/open?id=0ByJQKthhcDhuRTdfS3RmMjgtdUk


On the link below I have found formula to pickup nth blank value, but it stick to specific range I need that range to be changed based on today’s date column picked up by HLOOKUP or some other function.
https://www.extendoffice.com/documents/excel/3970-excel-find-nth-non-blank-cell.html

 

[James006]

Re: How to find and return the nth non blank bell value based on today's date column

Hi,

Why don't you post your current formula ...

and explain what would need to be modified ...

 

[songo]

Re: How to find and return the nth non blank bell value based on today's date column

ok, i will try but it's not easy cause I don't have an idea how to combine 2 formulas.

=ArrayFormula(index('Scheduler'!D:D,small(row('Scheduler'!D:D)+(100*('Scheduler'!D:D="")),4))&"")

formula above works fine, it shows 4th non empty value in column D in Scheduler- sheet.
What I need to upgrade, is that formula changes range every day depending on today's date, 26th of may is column D in Scheduler and it's fine for today,
but tomorrow I want formula to pickup next column as a range witch is E as for 27th of May.

 

[songo]

Re: How to find and return the nth non blank bell value based on today's date column

And here is HLOOKUP function.
=HLOOKUP(B2,'Scheduler'!3:25,4),
where B2 is cell with =today() function.
In scheduler row 3 is filled with dates, so hlookup perfectly find today's column, and now instead of putting row_index_num, (for ex. 4) i want excel to show me (1st/2nd/3rd etc) non empty value in finded todays column.

Hope it's clear now))) uffff

 

[63falcondude]

Re: How to find and return the nth non blank bell value based on today's date column

songo, it is important to specify that you are using Google Sheets.

While Excel and Google Sheets are similar, they also have their differences.

 

[James006]

Re: How to find and return the nth non blank bell value based on today's date column

Hello,

An attempt would be replace
'Scheduler'!D:D
by
Indirect("'Scheduler'!"&LEFT(ADDRESS(1,day(today())-22,4),1)&":"&LEFT(ADDRESS(1,day(today())-22,4),1))

Hope this will help

 

[James006]

Re: How to find and return the nth non blank bell value based on today's date column

@63falcondude

Thanks for pointing out OP is using Google Sheets ...

I do not have the slightest idea about Google Sheets ... and most probably the Indirect() function will not work ...

 

[songo]

Re: How to find and return the nth non blank bell value based on today's date column

guys... really sorry for not mentioning google sheets, I am actually trying on both, but eventually it should be on google sheets.
James, i have tried your advise...

=ArrayFormula(index(Indirect("'Scheduler'!"&LEFT(ADDRESS(1,day(B2)-22,4),1)&":"&LEFT(ADDRESS(1,day(B2)-22,4),1)),
small(row(Indirect("'Scheduler'!"&LEFT(ADDRESS(1,day(B2)-22,4),1)&":"&LEFT(ADDRESS(1,day(B2)-22,4),1)))
+(100*(Indirect("'Scheduler'!"&LEFT(ADDRESS(1,day(B2)-22,4),1)&":"&LEFT(ADDRESS(1,day(B2)-22,4),1))="")),2))&"")

it worked somehow in google sheets, showed me 2nd value in column D, if I change date for tomorrow is shows 2nd value from E.
However since it's not connected to dates in Scheduler, just to columns/rows, i will face problem when month ends, because there will be cople of columns with summaries etc and then new month will start that's why i wanted to use HLOOKUP, because no matter what it just looks for same date and go down to column.

 

[Joe4]

Re: How to find and return the nth non blank bell value based on today's date column

Moderator Note:
I have moved the thread and updated the title to indicate it is a Google Sheets question.

 

[songo]

Re: How to find and return the nth non blank bell value based on today's date column

Guys!!
I have figured out workaround, Index + nested ifs + match function.



=index(
if(Scheduler!5:5<>"",Scheduler!5:5,
if(Scheduler!6:6<>"",Scheduler!6:6,
if(Scheduler!7:7<>"",Scheduler!7:7,
if(Scheduler!8:8<>"",Scheduler!8:8,
Scheduler!9:9)))),match(B2,Scheduler!3:3,0))

Luckely i have only 25 people in my team so it would be 25 nestef ifs...not stylish but works... in case somebody has better looking idea i would appreciate help