Monday, July 15, 2013

yogi_Pull Task StartDate And DueDate Columns From A Table Meeting Multiple Specified Criteria

                                          Google Spreadsheet   Post  #1286
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul 15, 2013
user Charity Katz (http://productforums.google.com/forum/?zx=1nw5egofkozk#!mydiscussions/docs/TwYcaDm2gyY)
Formula Works in Excel but Not Google Drive
I have a formula that works in Excel but not in Google Drive. How can I adjust it to make it work?

=IF(AND(NOT(ISERROR(FIND("Charity",v10_Responsible))),(ISERROR(FIND("Completed",v10_Status))),(v10_Due<=(TODAY()+7))),v10_Task,"N/A")

Basically, it is looking in a different spreadsheet for the column named v10_Responsible and reporting is Charity is in the column somewhere. Then it is looking in the column named v10_Status for Completed, and then v10_Due for the past up to seven days in the future, and it is returning the value in the v10_Task field. In Excel it returns the expected data by matching row. So row 1 equals row 1. In Google Drive when the criteria is met, it returns the header row value only.
---
Thank you! 

I removed some of the data, but each tab has a few rows of actual data. The Overview tab is the one that currently contains the formula that works in Excel but not here.

To clarify, I am trying to do the following:

If Who's Responsible contains Charity (or some other defined name), and the Status does not contain Complete, and the date is less than or equal to today plus one week in the future, I want to return the Task, Start Date, and Due Date columns. Ultimately, I would like this to dynamic, essentially updating each time we open the document, but the current formula actually just matches row to row, which is fine too. We can always sort the selected data.

---
I created three overview spreadsheets depicting the results as of today that would be expected if the formula I described above worked as expected. There are different results based on what is possible. Any of those three would be ok, but they are listed in order of preference.

In the sheets other than the Overview, if Who's Responsible contains Charity (or some other defined name), and the Status does not contain Complete, and the date is less than or equal to today plus one week in the future, I want to return the Task, Start Date, and Due Date columns. Ultimately, I would like this to dynamic, essentially updating each time we open the document.
--------------------------------------------------------------------------------------------------------------------------------------------------