Monday, September 25, 2017

yogi_Repeat Sheet Names By Number Of Items In Respective Sheet Names In One Combined List

Google Spreadsheet   Post  #2248

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Sep-25-2017
question by: Thomas Wu
https://productforums.google.com/forum/#!topic/docs/uL6otiR9PNs;context-place=forum/docs
Repeating value X times depending on number of entries in a range
I currently use a Filter formula to grab all the relevant entries on multiple tabs:
={filter(Fruits!A:A,Fruits!A:A<>"",row(Fruits!A:A)>1);filter(Vegetables!A:A,Vegetables!A:A<>"",row(Vegetables!A:A)>1)}

That formula above will output 1 list of items from the "Fruits" tab and the "Vegetables" tab.

It's particularly helpful because I don't know how long the list from both tabs will be as they will grow/shrink daily.

What I want to do next is to add a column that describes which worksheet each entry comes from.

I know I can do this via a formula that will check each worksheet to find the name:

If(match(key,Fruits),"Fruits","Vegetables")

But I imagine there is another way to count the number of filter entries in the first formula and repeat "Fruits" for as many entries in the first part of the formula and then vegetables for the following entries. Any ideas of how to do this? Here's a spreadsheet with dummy data:

Cell A3 is where I'd like to have the formula entered. Thanks in advance if anyone has any ideas!

-Thomas

yogi_Conditionally Format column B If column A Date Is Equal To Or Greater Than Today's Date By 1 Year

Google Spreadsheet   Post  #2247

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Sep-25-2017
question by: Homestead Help
https://productforums.google.com/forum/#!topic/docs/uL6otiR9PNs;context-place=forum/docs
Conditional Formatting with two sells
What I am trying to do is make a cell change color when another cell is a certain date. Example:

I want for a cell to change if the other cell is one year away from today. 
If cell A is equal to or greater than one year of today's date I want cell B to change green. 

I thought this was going to be easier than it is turning out to be...


Thursday, September 21, 2017

yogi_Filter Form Responses To Show Only The Most Recent (or Latest) Response Submitted By 'Provider Name' And 'Level Of Service'

Google Spreadsheet   Post  #2246

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Sep-21-2017
comment by Mrugesh Patel; question by: mrdanpratt
Filter form responses to show only the most recent response if data is duplicated 
Let's say I want people to fill out a form with their email address and their favorite color (just for example).  So the spreadsheet (the sheet name is 'Form Responses') has column A with the timestamp, column B with the email address and column C with the color.  Let's say I want to display this data on a second sheet using =ARRAYFORMULA('Form Responses'!A:C) 

Let's say someone responds with red but later they want to change their response to blue.  So they respond again.  I don't want to display both of their choices.  I only want to display all the most recent ones on the second sheet.  Is there a way to create such a filter?  It would be fine to display the filtered data further to the right on the second sheet, like in columns D, E, and F.  Any help would be appreciated.
 
-------------------------------------------------------------------------------------

Wednesday, September 20, 2017

yogi_Rearrange Data In Table A:C For Unique Names Letters And Numbers

Google Spreadsheet   Post  #2245

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Sep-19-2017
question by: jonesnca
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/KHDf_QlmbeA;context-place=forum/docs
How do I group/summarize this data in Google Sheets?
Let's say I have this data in a spreadsheet

+-------+---------+--------+
| Name  | Letters | Number |
+-------+---------+--------+
| Bob   | a,b,c   | 1,2,3  |
+-------+---------+--------+
| Bob   | d       | 4      |
+-------+---------+--------+
| Bob   | b,c     | 3,5    |
+-------+---------+--------+
| Alice | e       | 5      |
+-------+---------+--------+

How do I write a formula that displays this data like this?

+-------+---------+-----------+
| Name  | Letters | Number    |
+-------+---------+-----------+
| Bob   | a,b,c,d | 1,2,3,4,5 |
+-------+---------+-----------+
| Alice | e       | 5         |
+-------+---------+-----------+

Tuesday, September 19, 2017

yogi_Setup Cells B8:E To Be Automaticlly Populated When A Sequentially Numbered Invoice Sheet Testnnnnn Is Added

Google Spreadsheet   Post  #2243

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Sep-19-2017
question by: Amanda Zamora
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/wI1yIHdx6pQ;context-place=mydiscussions
Aging Report cells auto imports data from sheets
I am trying to create an Aging Report.

Example:
I am working on a new invoice "BEAM170003"
Can you help me get the data from the newly created sheet "BEAM170003" to automatically show up in cells A8:D8 in Sheet "AGING REPORT"? (photo attached)

I did a few things that worked to grab the data but I'd have to manually input the formula in the next row... no thanks!
I'm sure there is a faster automated way of doing this that would save me loads of time :)


yogi_Compute Row By Row Running Average Of Shots Per Game

Google Spreadsheet   Post  #2242

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Sep-19-2017
question by: Carl Backlund
Trying to calculate shots per game for several games
I have a spreadsheet where I try to calculate the shots per game from my team from game to game.

Example if game 1 had 35 shots, then the shots per game are 35/1 = 35.
If game two had 30 shots, then shots per game after two games is (35+30)/2 = 32.5

I would like it if my sheet could caluclate this for me automatically while all I need to do is to write down the amount of shots from each game.

Is this possible with some sort of formula or do I have to calculate this by hand after every round?