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

                                         Google Spreadsheet   Post  #1511
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.

https://docs.google.com/spreadsheets/d/1nx2suoNLcXmnX19ZE2mCTlsPXEdPJenYeXyZdF2zyXE/edit#gid=1448742359

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

                                         Google Spreadsheet   Post  #1510
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.

https://docs.google.com/spreadsheets/d/1nx2suoNLcXmnX19ZE2mCTlsPXEdPJenYeXyZdF2zyXE/edit#gid=1448742359

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

                                         Google Spreadsheet   Post  #1509
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.

https://docs.google.com/spreadsheets/d/1nx2suoNLcXmnX19ZE2mCTlsPXEdPJenYeXyZdF2zyXE/edit#gid=1448742359

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

                                         Google Spreadsheet   Post  #1508
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.

https://docs.google.com/spreadsheets/d/1nx2suoNLcXmnX19ZE2mCTlsPXEdPJenYeXyZdF2zyXE/edit#gid=1448742359

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

                                         Google Spreadsheet   Post  #1507
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) 
post by Yogi Anand on Google Docs user forum (https://productforums.google.com/forum/#!newtopic/docs)

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
--------------------------------------------------------------------------------------------------------------------------------------------------

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

                                         Google Spreadsheet   Post  #1506
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-29-2014
post by Teenie (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/uj02AZP9nus)

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.

Here is a link to my spreadsheet https://docs.google.com/spreadsheet/ccc?key=0Amuta5sgdCuMdEY1UnA1UjUwZXBNUEFJYU00UU9EQnc&usp=sharing

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. 

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

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

                                         Google Spreadsheet   Post  #1505
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-29-2014
post by virtuzol (https://productforums.google.com/forum/#!topic/docs/bpgodiACCag)
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.

https://docs.google.com/spreadsheet/ccc?key=0AlHjYxVqmzIRdDd5bWlmS2JQekFGUEtfeTZTMkhhSVE#gid=3

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

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

                                         Google Spreadsheet   Post  #1504
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-29-2014
post by Meredith Poynter (https://productforums.google.com/forum/#!mydiscussions/docs/7AYLl-Pk5nc)
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.

Please help. I haven't been able to find a solution upon searching. THANK YOU!

---

Here is the skeleton of my spreadsheet. https://docs.google.com/spreadsheet/ccc?key=0ApOYbVKFbSMNdHVKMVIwb1lROEFORkdjd2Q4YXNpVFE&usp=sharing

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!

Thanks for your help!

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

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

                                         Google Spreadsheet   Post  #1503
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-28-2014
post by Matthew Eisenberg (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/-s9-Qd2xAoI)
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

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

                                         Google Spreadsheet   Post  #1502
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-27-2014
post by NetEngineer009 (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/KahpovREU1s)
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

                                         Google Spreadsheet   Post  #1501
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-27-2014
post by Jeremy Booth (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/OXn5epLkb4U)
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

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