## Friday, January 31, 2014

### yogi_Working With Source Data As A Formula Output In New versus Old Google Sheets - part 4

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-01-2014
question by Bee Lini via GMail (https://mail.google.com/mail/u/0/#inbox/143e5a80f84ed371)

Hi Yogi,
Sorry to bug you with a personal question such as this. I was speaking with Adam Lusk about a project and he referenced you. I was wondering if you could take a peek at my formula. I have spent countless hours trying to find a work around to no avail. It use to work on the old google sheets, but I can't wrap my brain around the current error.
If you could take a look and point me in the right direction, or solve it for me I would be very appreciative.

Hope all is well
--------------------------------------------------------------------------------------------------------------------------------------------------------

using a combination of FILTER and QUERY functions
this (part4) is setup in New Google Sheets -- for setup in Old Google Sheets see part 3

### yogi_Working With Source Data As A Formula Output In New Google Sheets versus Old Google Sheets - part 3

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-01-2014
question by Bee Lini via GMail (https://mail.google.com/mail/u/0/#inbox/143e5a80f84ed371)

Hi Yogi,
Sorry to bug you with a personal question such as this. I was speaking with Adam Lusk about a project and he referenced you. I was wondering if you could take a peek at my formula. I have spent countless hours trying to find a work around to no avail. It use to work on the old google sheets, but I can't wrap my brain around the current error.
If you could take a look and point me in the right direction, or solve it for me I would be very appreciative.

Hope all is well
--------------------------------------------------------------------------------------------------------------------------------------------------------

using a combination of FILTER and QUERY functions
this (part3) is setup in Old Google Sheets -- for setup in New Google Sheets see part 4

### yogi_Working With Source Data As A Formula Output In New versus Old Google Sheets - part 2

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-31-2014
question by Bee Lini via GMail (https://mail.google.com/mail/u/0/#inbox/143e5a80f84ed371)

Hi Yogi,
Sorry to bug you with a personal question such as this. I was speaking with Adam Lusk about a project and he referenced you. I was wondering if you could take a peek at my formula. I have spent countless hours trying to find a work around to no avail. It use to work on the old google sheets, but I can't wrap my brain around the current error.
If you could take a look and point me in the right direction, or solve it for me I would be very appreciative.

Hope all is well
--------------------------------------------------------------------------------------------------------------------------------------------------------
this is setup in New Google Sheets -- for setup in Old Google Sheets see part 1

### yogi_Working With Source Data As A Formula Output In New versus Old Google Sheets - part 1

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-31-2014
question by Bee Lini via GMail (https://mail.google.com/mail/u/0/#inbox/143e5a80f84ed371)

Hi Yogi,
Sorry to bug you with a personal question such as this. I was speaking with Adam Lusk about a project and he referenced you. I was wondering if you could take a peek at my formula. I have spent countless hours trying to find a work around to no avail. It use to work on the old google sheets, but I can't wrap my brain around the current error.
If you could take a look and point me in the right direction, or solve it for me I would be very appreciative.

Hope all is well
--------------------------------------------------------------------------------------------------------------------------------------------------------------
this is setup in Old Google Sheets -- for setup in New Google Sheets see part 2

### yogi_Select Specified Number Of Random (lotto / lucky) Numbers From Specified Range Of Low And High Values

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-31-2014
question by Cyrus Sam via vCita (https://mail.google.com/mail/u/0/#inbox/143e2398070511e5)

Please what I was trying to say is about lotto how to calculate 5 out of 90 numbers is about lotto. to show me the formula to calculate 5 out of 90 lotto numbers.

Or if there is any help that you people can give me and my family I appreciate it a lot.

I do hope to hear from you people as soon as possible.

Thanks
Cyrus

--------------------------------------------------------------------------------------------------------------------------------------------------

## Thursday, January 30, 2014

### yogi_Set Up Conditional Formatting For Items Due inMoreThan3wks Due withIn3wks Due withIn2wks Due lessThan1wk PastDue Over30days

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-29-2014

Originally created this spreadsheet in Smartsheet. Free trial is over so I have moved to Google.

My spreadsheet has a column for Date Received. This automatically calculates a Due Date of  +30 Days in the next colum over. I need each row to change colors based on the due date.

At the top of the sheet you will see a color chart of how I would like my rows to change. (As you can see, the rows are already colored as they have moved over that way from Smartsheet) I need all new rows to take on this formatting as well.

I was playing around and trying to figure this out myself to no avail. Please use PO 3592 TA as a sample to play with.
-----------------------------------------------------------------------------------------------------------------------------------------------------

## Wednesday, January 29, 2014

### yogi_Pull Contact Name And Contact's Data1 through Contact's Data17 For Year Specified In Cell A15

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-29-2014
Multiple filtering
---
Hi Yogi,

sorry for my inconistency.

I have made for you a new COMMENTS sheet, and have written what you are expected. I hope that's clear.

----------------------------------------------------------------------------------------------------------------------------------

### yogi_Pull Data From 'Form Responses 1' For Staff Of CEO (cell 5) With CEO_Staff Data In Sheet '0' Cells H12 to I

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-29-2014
How to lookup (using Index Small If Row) with multiple values, returning for all rows
I am working with this formula:
=ARRAYFORMULA(IFERROR(INDEX('Form Responses 1'!\$A\$2:\$M;SMALL(IF('Form Responses 1'!\$D\$2:\$D=\$C\$5;ROW('Form Responses 1'!\$A\$2:\$M)-1);ROW(A1)),11),""))

\$C\$5 is a person's name on the spreadsheet.This formula looks that person up in 'Form Responses1'!D, as how ever many times it appears and returns the info (in the 11th column) from those rows. It works great where I need it to, but now, I'd like to have the formula look up multiple people's name and return all rows for each person. I've tried modifying the underlined section of the formula to have multiple values, but have had no success.

---

I'm looking for formulas to go in "Customer Report" Rows 21 and down. I want them to pull the information from "Form Response 1" for the CEO and any Staff that works for that CEO. Currently, the formula only pulls data for the CEO.

As the current selection of "John B" for CEO on the Customer Report" it should show the information in Row 21 as it currently shows, and then also show information from row 4 in "Form Response 1" as Ashley P works for John B.

On the "0" tab, I created a table that will always populate a column with the CEO selected at the top of the "Customer Report" and the CEO's staff, to help with the process. My thinking was to have the formulas in the "Customer Report " rows 21 and down to use '0'!J14:J21 as the values to lookup but couldn't figure out how to do it, if that is even the right thinking to do it. I can not have the form response have the CEO and staff in different columns - that would be too easy!

-------------------------------------------------------------------------------------------------------------------------

## Tuesday, January 28, 2014

### yogi_get count of each of the items listed in cells B30:B34 in each column of range C4:I26 Marked with red chain dotted line

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-28-2014
Assign a numerical value to text
---
this problem is not about assigning numerical value to text ... it is for getting count of text items
and then summing the instances of text items encountered

## Monday, January 27, 2014

### yogi_Set Up FORMULAS for WEEKDAYS between Starting Period Date and Ending Period Date

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-27-2014
How to make Work days auto propagate in a mothly calendar
So this is my first post in this forum so i hope im in the right place.

I need help making days auto propagate based on a real time month calendar year. For example if i want to make a 2-3 month span of Jun-Jul 2013 for example this is what i want:

 JUN 2013 JUL 2013 WEEK 1 WEEK 2 WEEK 3 WEEK 4 WEEK 1 WEEK 2 WEEK 3 WEEK 4 WEEK 5 3 4 5 6 7 10 11 12 13 14 17 18 19 20 21 24 25 26 27 28 1 2 3 4 5 8 9 10 11 12 15 16 17 18 19 22 23 24 25 26 29 30 31

thats all manually typed in but i want to use a function to type in the first day and have the rest fill in.

at first i used this but then just got the days with the next one below
=ArrayFormula( text( date( year(A6) , month(A6) , day(A8) + column(A1:AQ1) ),"m/d/yyyy") )

this one i used to put the first day of that month and it auto fills in the range i set.
=ArrayFormula( text( date( year(A6) , month(A6) , day(A9) + column(B9:AQ9) ),"d") )
I fill in 3 then in 4's spot i put this code and it fills from 4 on to end range, but it does every day of the month (including weekend dates)
3 4 5 6 7 8 9 10 etc..

this formula does NETWORKDAYS which is work days mon-fri

=IF(WEEKDAY(A25)=7,A25+2,IF(WEEKDAY(A25)=6,A25+3,A25+1))

but this requires it to have a full date (6/20/2013) in the starting cell to the left of it then click and drag to all the cells you want to the right
also i dont know how to just get the day # instead of the full date m/d/yyyy

Is there a way to mix the 2 of these formulas?
The first formula is the one i want to use because it auto fills days to the right of it without having to click/drag and it also just outputs the day value # but i need it to only output WEEKDAYS workdays mon-fri
Is there a way to make that formula work for what i need?

Thanks for the help :)

-------------------------------------------------------------------------------------------------------------------------------------------------

### yogi_From A Table Of Names And Details Of Various Items Rearrange Data By Name And Items As In A Database List

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-27-2014
Transposing some columns and duplicating others
Hi

I have some data collected from a google spreadsheet form. The form asks for various details but includes a name and then a couple of pieces of data about up to 4 items. In the spreadsheet this all goes in as a single row. We do various data processing exercises with this data that suits the one row per form. There is one piece that we would like to do that doesn't, and for that data I would like to transpose some data. What we would like is each item with it's data on a row with the name as the first column, so if a use fills in data for all 4 items they would have 4 rows with 3 columns, name, item and details. We would like this as a formula in the sheet so that as new forms are filled in, the data is automatically updated as it is for the other representations.

The data is sensitive so I've knocked up a test sheet here

We want to turn

 Name Item 1 Some data about item1 Item 2 Some data about item 2 Item 3 Some data about item 3 Item 4 Some data about item 4 Fred car VW motorbike BMW bicycle rusty shoes worn out Wilma tram relaxing boat sedate Bill train late bus late car expensive

into

 Fred car VW Fred motorbike BMW Fred bicycle rusty Fred shoes worn out Wilma tram relaxing Wilma boat sedate Bill train late Bill bus late Bill car expensive

I have had a search around and found functions that look useful (like transpose), but I'm no spreadsheet expert and I've not been able to think of a way to combine them to get the data we want.

Thanks

Jeremy
--------------------------------------------------------------------------------------------------------