Google Sheets how to get value on certain row if the column is Today()?

0
votes

I have these sheets on Google Sheets

Sheet "Alpha"
    A       B      C        D  
1 Date    03/11   03/12   03/13
2 Status  DONE    DONE    In Risk

Sheet "Beta"
    A       B      C        D  
1 Date    03/11   03/12   03/13
2 Status  DONE    DONE    DONE

Sheet "Gamma"
    A       B      C           D  
1 Date    03/11   03/12       03/13
2 Status  DONE    In Risk    BLOCKED

I want to make a summary sheet with result on daily based as below:

Let's suppose today is 03/13

Sheet "Summary"
    A       B    
1 Name    Status
2 Alpha   In Risk
3 Beta    DONE
4 Gamma   BLOCKED

I want to populate the column A using script or function. So, the step on my mind would be:

  1. Populate column A with script/function
  2. On column B using formula for:
    • Finding the Today date on other sheet
    • Get value of the row on the column from today date from other sheer

Is it possible to do? How to do it?

1
google-sheets
share a copy of your sheet - player0

1 Answers

2
votes

In Sheet "Summary"

Finding by name in Column A

Cell B2: =HLOOKUP(TODAY(),INDIRECT(A2&"!$1:$2"),2,0)

Cell B3: =HLOOKUP(TODAY(),INDIRECT(A3&"!$1:$2"),2,0)

Cell B4: =HLOOKUP(TODAY(),INDIRECT(A4&"!$1:$2"),2,0)

Finding by Sheet

Cell B2: =HLOOKUP(TODAY(),Alpha!$1:$2,2,0)

Cell B3: =HLOOKUP(TODAY(),Beta!$1:$2,2,0)

Cell B4: =HLOOKUP(TODAY(),Gamma!$1:$2,2,0)

Function References