Google Spreadsheet Post #1690
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Jul-07-2014
post by SBC-Global: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/uR0Xe9ptjpM)
Scan row for value, get column of that match, apply column in another formula in a different row
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Jul-07-2014
post by SBC-Global: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/uR0Xe9ptjpM)
Scan row for value, get column of that match, apply column in another formula in a different row
+------+--------------+-------
| | A | B | C | D | E |
+------+--------------+-------
| 1 | 7/05/2014 | 7/06/2014 | 7/07/2014 | 7/08/2014 | 7/09/2014 |
+------+--------------+-------
| 2 | YES | NO | NO | YES | YES |
+------+--------------+-------
| 3 | Yes to date: | 1 | | | |
+------+--------------+-------
| 4 | No to date: | 2 | | | |
+------+--------------+-------
Let's say I have a table like above using Google Spreadsheet. I want to be able to know the column that matches the current date. So for example, Today is 7/07/2014 - that would return Column C. I'd like to be able to have a function/formula that will dynamically change the range based on which column the current date is in for row 1
Let's say I have a cell that I only want to display data from the start of the range and only until the current day.
So the formula in cell B3 would scan line 1 to determine which column contains the current date - in this case on 7/07/2014 the column is C. So it would scan Line 2 from the beginning column A to column C (A2:C2) where "C" is the variable. And it would "count" the number of cells the word "YES" appears in.
The formula value would change day-by-day as the date would change and the length of the range it scans should be dynamic based on the date.
So, for example, tomorrow on 7/08/2014 - the Field B3 would change automatically to be the value of "2" instead of "1", without changing the table at all. What changed is the DATE, and the value returned in the cell should be updated automatically.
I hope this makes sense! If there is any clarification needed please let me know!
----------------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment