Google Spreadsheet Post #1481
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Jan-09-2014
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Jan-09-2014
question by bcrowder7 (http://productforums.google.com/forum/?zx=jr8bvtb398mw#!mydiscussions/docs/06Lr1QZux_o)
Need array formula that looks up partial string and returns true value
Need array formula that looks up partial string and returns true value
Hello,
Currently I'm working on a gForm where personnel select a zone that they have checked for the day (in spreadsheet it'll say B8-Z1, B8-Z2, referencing building and zone).
Submissions will show all of the rooms that were checked off for that zone as being 'done,' but in order to play with the data in the way I need, I need to be able to have a formula that will return a value of 1 (aka 'True') if a room number shows up in a submission that also matches the date listed.
This spreadsheet has a copy of example Submissions. In the original spreadsheet, it sorts Form Responses such that most recent submissions are listed first.
The B8-Z1 is just the first zone I'm working on. Ideally, I'd like to have a formula that will look at the room number that's above it and the date in column C and return a value of '1' if it returns a 'true' that the room number was found in the submissions sheet in column H.
These zones will be checked in daily, so I am assuming an array formula is what I'll need to not overly complicate the spreadsheet, so it'll need to track at least the last 365+ submissions for each zone.
Please let me know if you need additional information. If I can get help with this, I would greatly appreciate it. The only other option I can think of is separating the questions and having someone check a box that says 'Yes' for a particular room and then easily using a query to do this, but that would require having one 'question' per room, and that will heavily increase the number of cells that are being added to the spreadsheet with each form submission.
---
Sheet: B8-Z1
Column G2:G
My expected result would be the number 1 in cell G3 (because my submissions were dated yesterday) if, in sheet 'Submissions1', the room number '8118A' appears in a cell in column H that has the same date in that row (This example in particular would be row 10, Cell E10) as what is listed in column C of sheet B8-Z1.
If it is not present, then I would want it to say 0.
Hopefully this helps
---
Also, to follow up on the logic:
If someone indicated that this room was checked on this particular day, then the formula will be able to spit out a 1 for that day and for that room to indicate that this is true, so it not only needs to know if the column contains the room number, but also if the date it was checked matches the dates column.
--------------------------------------------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment