yogi_Compare Two lists and Flag row where data from list 2 appears in list 1

Google Spreadsheet   Post  #2455

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     May-29-2018

question by: Mike Wilday (LAPU)
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/PP-mexvj3xk;context-place=mydiscussions

Compare Two lists and Flag row where data from list 2 appears in list 1.

yogi_Compute Row By Row Running Count Of General Service Numbers (column C) And Compute Specific Service Numbers (column D)

Google Spreadsheet   Post  #2454

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     May-29-2018

question by: Logan Twidell
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/x7o-9ArDbEk;context-place=mydiscussions
Dynamic cell referencing within an array function

yogi_Conditionally Format Cells In Column A Which Match The Entry In DropDown List In Cell G6 Or The Group Which They Belong To From Sheet2 - Part2

Google Spreadsheet   Post  #2453

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     May-26-2018

question by: Krzysztof Czajka
https://productforums.google.com/forum/#!topic/docs/XGupzH6gBac;context-place=mydiscussions
Matching formula
Hi folks!

I try to create timetable and I want to create formula which will help students or them parents to find when they have a class or private lesson.

I will attach link below.

What I want exactly is, if I pick name from List in cell G6 I'd like to that name to be highlighted in all spreadsheet, in addition kid belongs to the different classes/groups, Latvian, Trolls etc and I'd like to be those groups highlighted as well when I pick child name.

Simplyfing if I pick name Amelia in G6 I'd like to highlighted that name and Troll group as she belong to that group as well.

I was able to use match formula, but once I picked name in G6 and it was matching with the name from A column it was highlithing name but in G6 not in A column.

https://gyazo.com/642587ba0bb0eb379569c06a398c0281 

and You can see on the other spread that Rayley is in the Trolls group

https://gyazo.com/6eee8deafa038af9df5c0263ed966b07 

so I'd like Trolls and his name to be highlithed.

I hope You will be able to understand. I always try to find out everything on my own but this is difficult for me.

Thanks in advance.

yogi_Array Formula For Sum Of Hours By Student And Date For Entries In Separate Date Sheets

Google Spreadsheet   Post  #2452

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     May-25-2018

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

SUMIFS in ArrayFormula?

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

Working on Students!

I'm having a problem with the Column D. I need to make it so that when the person's Attend becomes "Y" the sum of the person's hours in the 8/10! would be calculated and returned.

I tried this

=ArrayFormula(SUMIFS('8/10'!$I2:$I,'8/10'!$E2:$E,$A3:$A))

yogi_Conditionally Format Cells In Column A Which Math The Entry In DropDown List In Cell G6

Google Spreadsheet   Post  #2451

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     May-25-2018

question by: Krzysztof Czajka
https://productforums.google.com/forum/#!topic/docs/XGupzH6gBac;context-place=mydiscussions
Matching formula
Hi folks!

I try to create timetable and I want to create formula which will help students or them parents to find when they have a class or private lesson.

I will attach link below.

What I want exactly is, if I pick name from List in cell G6 I'd like to that name to be highlighted in all spreadsheet, in addition kid belongs to the different classes/groups, Latvian, Trolls etc and I'd like to be those groups highlighted as well when I pick child name.

Simplyfing if I pick name Amelia in G6 I'd like to highlighted that name and Troll group as she belong to that group as well.

I was able to use match formula, but once I picked name in G6 and it was matching with the name from A column it was highlithing name but in G6 not in A column.

https://gyazo.com/642587ba0bb0eb379569c06a398c0281 

and You can see on the other spread that Rayley is in the Trolls group

https://gyazo.com/6eee8deafa038af9df5c0263ed966b07 

so I'd like Trolls and his name to be highlithed.

I hope You will be able to understand. I always try to find out everything on my own but this is difficult for me.

Thanks in advance.

yogi_Conditionally Format Arrival Date If Shipping Date Has Arrived Or Passed

Google Spreadsheet   Post  #2450

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     May-24-2018

question by: UMuildABook
https://productforums.google.com/forum/#!topic/docs/XGupzH6gBac;context-place=mydiscussions
Conditional Formatting: Is Date X days from Today

I have a column of cells with dates that an order needs to arrive in the customer's hands by.  Next to this cell is the number of days it takes to ship to their location.  I would like the arrival date to be highlighted it is less than the number of ship days away from today.

yogi_Find minimum value in range of duplicates

Google Spreadsheet   Post  #2449

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     May-24-2018

question by: Paul-Grant Van-Rooyen
https://productforums.google.com/forum/#!topic/docs/8iyQlLwxcxs;context-place=forum/docs

Find minimum value in range of dulicates

Hi there folk,

I am looking for a formula/query to run and find the minimum values in a range that contains duplicates (2 or more). 

The first range which is sorted is in A1:B14 with the required results in D1:E18 (formula required in Cell D2.

The first range which is sorted is in A19:B32 with the required results in D19:E26 (formula required in Cell D20.

https://docs.google.com/spreadsheets/d/169Vewmpcwu5r3bbOT2Ich06a17naOUUuOuIPWMkDo4I/edit?usp=sharing

Cheers

yogi_List Dates For Specified WeekDay Starting From A Given Day For Specified Number Of Weeks

Google Spreadsheet   Post  #2448

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     May-23-2018

question by: Colin TheWilson
https://productforums.google.com/forum/#!topic/docs/8iyQlLwxcxs;context-place=forum/docs
Increment dates
Hello! I'd like to know a way to increment dates based on the start of the week.

For example, I want to make a series of column headings that say "Week of 5/1", "Week of 5/7", "Week of 5/14", "Week of 5/21", "Week of 5/28".

I'm going to do this on a large scale (A new set of headings every month for many years), so I want to have a function that would only take the month and would auto-generate the week start headings. 

Is something like this possible? Thanks!

yogi_Count Entries For Specified TimeFrames

Google Spreadsheet   Post  #2447

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     May-11-2018

question by: Koji Ueunten
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/lw0tmdsaq-Y;context-place=forum/docs
Count entries(timestamp) based on calendar month

Sample Sheet

The timestamps are taken from a Google Form, rest of the data redacted for privacy.

I would like to count the number of entries i have in the current month, using 'TODAY()' so the sheet stays updated as the months pass and more form entries are submitted.  I would also like updated totals of the three months and six months including the current month.  I have reworked a formula(I do not understand this formula at all )taken from elsewhere on this forum but it fails when the date range spills over to the previous year.  On the 'Totals' tab, I have my best guess in column B and the expected Values in D. You can see that December returns zero.  I am open to alternative methods, especially if they are simpler/more readable.

Please let me know if there is more information I can provide.  Thanks in advance.

yogi_List WeekDays (Monday to Friday) Only Between From And To Specified Dates

Google Spreadsheet   Post  #2446

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     May-11-2018

question by: Jim Morison
https://productforums.google.com/forum/#!topic/docs/IOGU293VN8M;context-place=forum/docs

List All Week Days In Column

Hi everyone,

What I'm looking to do is simply list the dates for an entire year (Jan 1, 2018 through Dec 31, 2018) in the format "Friday, May 11" excluding weekends. That's all...

Similar to this:

yogi_From 'Speed Torque Table' Sheet2 Find The Torque Associated With Specified Speed Value In Cell A3

Google Spreadsheet   Post  #2445

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     May-11-2018

question by: Tyler Toepke-Floyd
https://productforums.google.com/forum/#!topic/docs/2LCjOMYaI6w;context-place=forum/docs

How to get sheets to pick a value from a table that is closest to a target value in Sheets

I have a table that contains testing data values for speed and torque of a motor. I am doing a calculation to give me a calculated speed. I want to make a formula that will find the speed closest to my target value, and then grab the torque that is associated with that speed. The goal is to find the actual attainable torque based on a theoretical speed. 

Target speed 1000 RPM

table in separate sheet

Speed     Torque

980           21

997           19

1005         18

1012         16

I would want the cell to display 19 as the answer given the input of 1000. 

So its looking up a value closest to the target value and then grabbing the number from the cell beside it. Hope this makes sense

yogi_Sort By 'Day And Time' When 'Day And Time' Together Have Been Entered As Text

Google Spreadsheet   Post  #2444

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     May-09-2018

question by: ScriptTrouble
https://productforums.google.com/forum/#!topic/docs/5ekVC8iavOE;context-place=forum/docs
How to sort by day and time not a to z

I have a schedule for employees. But when we sort the sheet it puts everything by a to z the I would like it to actually put the response in the right order starting Monday morning. The problem is Friday is always first. but more importantly, 1 am is followed but 1 pm.

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

yogi_Sort Table B3:I68 and L3:S23 in 'Points Standing' By Column I And S Respectively In Descending Order

Google Spreadsheet   Post  #2443

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     May-04-2018

question by: PhilipORoss
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/i24B3s2FF_c;context-place=mydiscussions
Sort a range that uses a =unique function

I have a spreadsheet that keeps score of a competition.

Competition is 6 rounds, and I'm using a vlookup embedded in an iferror to total the points of each rounds.

To generate the final list, I'm also using a unique function, to ensure that participants names only appear once.

Happy to post a link to a copy of the spreadsheet if someone can offer some help!

I'm trying to sort the range by points value, and the total points is calculated by =sum(range)-min(range) as the total points is total minus lowest value.

Whenever I attempt to sort the range it's repopulates into the list of names initially populated by the unique function.

I've found this by playing around with the formulas that generate the total points, with no change any time.

Anyone able to offer help?

TL;DR: Unable to sort range by points value in column G, as column A has unique function

yogi_Conditionally Format Cells Based on Absolute Value Difference to Another Cell

Google Spreadsheet   Post  #2442

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     May-01-2018

question by: Chadillac
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/wgkg8WHMowI;context-place=forum/docs
Conditional Formatting Based on Absolute Value Difference to Another Cell

I'm wondering if it is possible to create custom conditional formatting of a cell based on its absolute value difference from another cell. In the spreadsheet linked below, for example, I would like to have B13 and B14 change color to indicate their difference between each other is greater than 1.

For context: these are essay scores that have been assessed twice by readers, so each "Essay ID" corresponds to the pair of scores each essay has received. I'm hoping to create custom conditional formatting that would highlight where reader scores vary by more than 1 for each column B through H. Is this possible?

Test spreadsheet located here: https://docs.google.com/spreadsheets/d/183Vs4pqPl-qBoo0ZWqDq06jYL_sxFFz5vbi76CquzcU/edit?usp=sharing