yogi_Conditionally Format Cells E7 To EY33 If Entry In Column E is 'Complete' and Entry In Column F Through EY is 'X'

Google Spreadsheet   Post  #2182

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-12-2017

question by Matthew Dengler:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/a0acWqzgBB0;context-place=forum/docs
Dynamically Changing Conditional Formatting in Sheets
Hi everyone,

I would like to apply a set of conditional formatting logic across a group of cells, but have the rows and cell numbers change based on the cell that I "drag/copy" the conditional formatting to.

To be clear, I would essentially like the conditional formatting logic with the following formula...

=AND(E7="some text",K7="some other text")

...To be applied to a large number of cells. But I cannot just apply the above formula to all cells, I need the rows and cell values formula to change dynamically based on each cell that I apply it to (as if I had dragged a normal formula across a group of cells). 

To be clear again, I need the above formula to change to =AND(E8="some text", K8="some other text") and =AND(E7="some text",L8="some other text") when I apply it to different rows and columns.

Can anyone help me with this?

Link to spreadsheet: https://docs.google.com/spreadsheets/d/1lRCq05SCfZOYXe3t_fte9F6rf2LWCM77jgxkJ50ASTg/edit?usp=sharing

yogi_Mark The First Instance Of An Item As OK And All Subsequent Instances of the Same Item As DUPLICATE

Google Spreadsheet   Post  #2181

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-11-2017

question by Aleksandar Arsovski:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/BeOsYmx8l9k;context-place=forum/docs
Array formula Check Duplicate, First instance of duplicate to be Ok

Hi I need help with to modify arrayformula for Duplicate check up.

https://docs.google.com/spreadsheets/d/133wLHdtTa80jge1Vw_K9IGVFOmyl7sM7zjmMRIPP6xg/edit?usp=sharing

in B1 is my arrayformula which not functioning good, since I want first instance of the duplicate value to have Ok not Duplicate.

In C2 to C21 is how I want the result to look, and the formulas there are working as I want but I doesn't know how to make it work in Array Formula.

Any help appreciated.

Regards,
Aleks

yogi_Based On Form Input Make A Computed Column For Gender Based ID For male and Female Students

Google Spreadsheet   Post  #2180

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-10-2017

question by Ovation Theatre:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/Yp6PU2gnepM;context-place=forum/docs

Creating a unique ID based on gender of respondent

 

Hello! I am a high school theatre teacher looking to utilize Google Forms for our musical auditions. I was wondering if it is possible to create a unique ID based on the gender of the respondent. For example, I may have three male students sign up, and they are assigned M001, M002, and M003 based on marking "Male" as their gender on their forms. Similarly, when female students sign up, they are assigned, F001, F002, etc.

Is this possible? This would save me an enormous amount of time if we already assigned audition numbers prior to the audition

yogi_Pull Name From A2:A51 For Specified Debate Type In E2:E51

Google Spreadsheet   Post  #2179

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-09-2017

question by Jon Baney:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/LkhrFcDZ4_Y;context-place=forum/docshttps://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/8QCT_s7s_pg;context-place=mydiscussions

IF or Lookup function (If false, find next true cell)

Okay, here it goes. This is as much as I can get you, it isn't the actual sheet that I will be using, but a copy (I'm a teacher and for one, I cannot just publish student info like emails to the web, and for two...this is something that I'm creating now and won't have real data for until schools starts, so the data is fake, but it is realistic to what I need):

https://docs.google.com/spreadsheets/d/1ZEINvy0xnZkMAbX_xPWHxne6BSIhuI__hQbCOVvTudU/edit?usp=sharing

I have a line inserted between row 52 and 53 which divides the "tables". The lower table is based on responses in the top table (I am trying to get lists of student names who are competing in each event). I typed into each cell on row 54 what the function for that column from that point down should be looking for. To demonstrate what the data should look like as a finished product, I manually input what the table should look like after the formula is applied to each column. This "desired data" starts on the next row, 55, and continues down until in each column until there is no more "true" responses to the function.

I think I provided you with what you requested. As far as the logic goes for why I need this, I need to be able to be able to view all of the input from the top table as-is (it is actually a query pulled from another sheet that will be linked to a google form and will update as students "sign up" for each tournament) because I need to be able to view that data exclusive to that student for various reasons. I need the bottom table because I need to be able to easily see how many students are competing in all of the events without having to a) reorder the sheet or b) pull all of the responses to show and have 50+ responses under each event some of which says "student name from column A" and the rest saying "false". Starting on row 100 I have an example of what I WAS able to get, but you will see why that is undesirable...I want each column to only show the "true" responses--though, in collumn 101D-101L, I am not sure how to make those formulas search for a phrase within text and kick back a true statement if true, which is what I want (and is my second question listed in my original post). Example desired formula: Give me the value for column A any time column F contains "informative speaking":

Cell F="Informative Speaking, Original Oratory" (THE FORMULA IS TRUE!)

Cell F="Informative Speaking" (THE FORMULA IS TRUE!)

Cell F="Original Oratory, International Extemp" (THE FORMULA IS FALSE; this cell would show the next true statement.)

Cell F="International Extemp, Informative Speaking" (THE FORMULA IS TRUE; this would actually appear in the cell above because it was false)

I hope I am explaining this all correct and that it can all be done. 

Thanks for your help!

yogi_Mark Names Of Actors That Are Out Based On Form Submittals Of Date Out And Date Back

Google Spreadsheet   Post  #2178

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-07-2017

question by SMintheHat:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/LkhrFcDZ4_Y;context-place=forum/docs
Vlookup not searching through duplicates

Hello all!

I've gotten a lot of advice from this forum and have created the following sheet to help with work:

https://docs.google.com/spreadsheets/d/1UsQo7fnoxvO49kN1MqCcM_-UUV8NNVO6_hGX82OppoE/edit?usp=sharing

In short it is a way to tell which actor is out based on data entered in the form linked to the sheet and the date at the top of the sheet.

The main search formula is:

=iferror(if(and(vlookup(A4,'Actors Out'!$A$2:$A,1,0),$B$1>=index('Actors Out'!$B$2:$B,match(A4,'Actors Out'!$A$2:$A,0)),$B$1<index('Actors Out'!$C$2:$C,match(A4,'Actors Out'!$A$2:$A,0))),"OUT",""),"")

The problem I'm having is that vlookup is stopping at the first actors name which shows up in the Actors Out form. When there is someone with multiple days out(Example AMY) "OUT" will show up as out when B1 is between 5/30/17 and 6/1/17 but not when B1 is between 6/8/17 and 6/10/17.

Please let me know if there is another to find if the actor will be out based on the cell B1 and the information being entered into the google form.

Thank you!

yogi_Conditionally Format Entire Row Where The SearchBox Item is Found Any Where In The Corresponding Row

Google Spreadsheet   Post  #2177

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-07-2017

question by Thomas KM:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/-gIkDcJbil8;context-place=forum/docs
How do I create a function to highlight cells that contain text specified in a search cell
Hello,

I was wondering if you might be able to help me with an issue I'm facing creating a search/highlight cell in a spreadsheet. I'm looking to be able to type a word into a box at the top of my sheet and then have every row where this word occurs highlighted in the sheet below. I see that there are a few ways to do this in Excel, but I haven't been able to do this here. 

Thank you,

Thomas

Here is a link to the sheet I've been working on:
https://docs.google.com/spreadsheets/d/1N3hZqcradkbm7Y7MAED73nDHT_yXkSSettkctjj3_Rc/edit?usp=sharing

Here is a link to the kind of thing I'm looking for:
https://exceljet.net/tips/how-to-build-a-search-box-to-highlight-data

yogi_WorkAround for Query() - Creating New Column by combining Year & Month

Google Spreadsheet   Post  #2176

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun-07-2017

question by KW LowTS:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/fTNDCXkP0yg;context-place=forum/docs
Query() - Creating New Column by combining Year & Month

I have a query that returns the expected results

=query(A:D, "select A, B, C, D, YEAR(D), MONTH(D)+1 Label MONTH(D)+1 'ProfileCreatedMonth' FORMAT D 'MM/DD/YYYY'",1)

But, I'm trying to create a new column that is the combination of YEAR(D) & MONTH(D)+1 ie "2017-05" or "2016-12". Since query() does not allow merging columns and because dates are more complex to work with, can any recommend a workaround?

[Edited]Or even if there's a way to show a 2nd occurrence of D where I can FORMAT the 2nd occurrence of D to be 'YYYY-MM'. I need the 1st occurrence of D.

Thanks

My example sheet (worksheet HelpB) = https://docs.google.com/spreadsheets/d/14c5q-KFQkKDv8lhytU4XAX0Ur6aO2tWN-hFGptufTr0/edit?usp=sharing