yogi_Conditionally Format Columns A and B When Columns A and B In Corresponding Rows Are Not Blank

Google Spreadsheet   Post  #2054

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-20-2016

post by: Ryan PP:

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

Conditional formatting for range based on data in another range
Hello, I am trying to set up a rule that will highlight cells in Sheet1 based on if that cell in sheet 2 has data in it

I want to apply it to a range B2:AF26

My rule is =indirect("Sheet2$B$2:$AF$26")<>"" but am receiving a formula error

My logic should go if Sheet2!B2 has data in it then highlight Sheet1!B2, if Sheet2!B3 has data in then highlight Sheet1!B3, and so on for the array B2:AF26

Is there a way I can do this with 1 rule?

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

following is solution to a bit more generalized problem

yogi_Find Associated Category Based On KeyWord For Given Categories Sentences And KeyWords

Google Spreadsheet   Post  #2053

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-18-2016

post by: Raivis:

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

Array formula that checks for specific string

 

Hi,

It should be simple: array formula + vlookup + regex, but I am struggling with it. Please see this spreadsheet.

Column A is the category, B is a column that holds sentences. C is the keyword column. I am trying to come up with an array formula that would look for keywords in Sentences and return Category. You can see that my approach is not working. The column E shows the values that I expect to see. Could you please help me?

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

yogi_Compute Month By Month Date Of Event Held On Nth Weekday Of Month

Google Spreadsheet   Post  #2052

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

post by: Jennifer Eaton:

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

return date for nth recurrence of a weekday in a month

Hello

I have events that recur on every nth day of the month. For example, every 3rd Tuesday. I need to populate a cell with the date next occurrence. Basically if today is 3/11/2016 and this particular event happens on every 3rd Tuesday of the month I need to return 3/15. 

If today is 3/16/2016 I need to return the next yet to happen occurrence, so, 4/19.

Then I need to populate the next two occurrences, so, in the first example I'd have 3/15 then 4/19 and then 5/17.

Any help would be greatly appreciated.

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

yogi_Compute Nights Stayed From Table Of Enrolled Date And ExitDate

Google Spreadsheet   Post  #2051

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

post by: AMontiel:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/VxlxlKhcGWI;context-place=forum/docs
Formula to calculate nights present
I am trying to create a formula that calculates the number of nights a client was present in October 2015. Column S has the client's enrollment date and Column T has the exit date. Exit dates are blank if the client hasn't exited the program.

Here is an attempt at the formula, but it is missing clients who enrolled before 10/1/2015 and exited after 10/31/2015.

=IF(OR(AND(S2<"10/1/2015",T2=""),T2>"10/31/2015"),31,T2-"10/1/2015")

Here is my second attempt.

=IF(OR(AND(S2<"10/1/2015",T2=" "),AND(S2<"10/1/2015", T2>"10/31/2015")),31,0)+IF(AND(S2>="10/1/2015",T2=" "),"10/31/2015"-S2,0)+IF(AND(S2>="10/1/2015",T2>"10/31/2015"),"10/31/2015"-S2,0)

Does anyone have an idea how to do this calculation?

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

yogi_Create Weekly Calendar In Cells B1:H1 For Current WeekK From Monday To Sunday Dates In dd mm yyyy

Google Spreadsheet   Post  #2050

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

post by: holymak:

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

Mon 7/3/16

Tue 8/3/16

Wed 9/3/16

Thur 10/03/16

Fri 11/03/16

Sat 12/3/16

Sun 13/03/16

I would like to set up a weekly calendar as above for revision timetable for my son.

I would like for it to update each week so that next week will autoupdate to:

Mon 14/3/16

Tue 15/3/16

Wed 16/3/16

Thur 17/03/16

Fri 18/03/16

Sat 19/3/16

Sun 20/03/16

It would help a lot if the current date cell is highlighted as shown.

Any advice would be gratefully welcomed.

Thanks

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

yogi_Find Teachers Available To Substitute On Specified Day And Specified Class Period

Google Spreadsheet   Post  #2049

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-05-2016

post by: bpugh2:

https://productforums.google.com/forum/#!topic/docs/MAyHBPypO4o;context-place=mydiscussions
formula to match absent teacher with teacher to fill teaching duties?

If a teacher is absent for 1 or more periods during a day, his/her teaching duties are covered by other teachers who have on-call periods in which they, if selected, must substitute on the day and in the period of the absent teacher.

On my 'fake' 'Timetables' tab of my spreadsheet I have the timetables of several teachers; and on the OnCall tab, teachers' names along with which period and day that they may be called to substitute for an absent teacher. 

Is there a formula that will take the day and period of an absent teacher and return the matching day and period of a duty with an on call period?

For example:

Absent: Friday

Period 3

Science 6-3

Costanza Bardsley

Available to substitute: 

Brian  Pugh

Friday

Period 3

Thank you very much. 

formula question

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

yogi_Pull Into Sheet7 Names That Atre Present In Column C Of Both Sheet5 And Sheet6

Google Spreadsheet   Post  #2048

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-05-2016

post by: Tom Kalinoski:

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

Get list of people that are in both sheets

I have a spreadsheet that contains a list of names in sheet 5 and another list of names on sheet 6.  I'd like to list all the names in sheet 7 that occur both in sheet 5 and sheet 6.  Meaghan Garufi is present once in sheet 5 and once in sheet 6.  She correctly shows up in sheet 7. Lenna Newville appears in sheet 5 two times and does not appear in sheet 6 at all.  Yet, she shows up in sheet 7.  She should not show up in Sheet7.  What is wrong with my formula.  I'm not married to my formula, so I am ok with changing it completely if there is a better way to accomplish this.

Thanks

Tom

=iferror(query(query(ArrayFormula({filter(ArrayFormula({Sheet5!A2:B,if(len(Sheet5!A2:A),1,)}),Sheet5!A2:A<>"");filter(ArrayFormula({Sheet6!A2:B,if(len(Sheet6!A2:A),1,)}),Sheet6!A2:A
<>"")})," select Col1,Col2,count(Col3) where Col1 <> '' group by Col1,Col2 label count(Col3)'' ")," select Col1,Col2 where Col3>1 ",0),"No matches found")

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

yogi_For Entries In Search Sheet Pull Available Results From Different Tabs In The Same Spreadsheet

Google Spreadsheet   Post  #2047

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-05-2016

post by: Jesse Hagah:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/2DR5jYTsLKc;context-place=forum/docs
Searching other sheets based on 2 criteria
Hi,

I'm trying to do something specific in google sheets, but I'm not sure how to accomplish it. I've made an example here:

https://docs.google.com/spreadsheets/d/16UjlF2nbQ2PtYdQ-YDFYFa7RWrYQKLNU-CtkVkvvXEs/edit#gid=1484538133

If I'm missing information or posted this in the wrong place, please let me know. This is my first time posting here :)

Thanks,

Jesse 

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

yogi_Put Data In Columns A and B Into Specified Number of Columns Of Nominally Same Height

Google Spreadsheet   Post  #2046

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-01-2016

post by: Gabriel Cervo:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/cEOg5NiwW8A;context-place=topicsearch/Gabriel$20Cervo
Show formatted list in three columns
Here's what's happening:

I have a sheet with two columns. Column 1 has work orders # and Column 2 has the Location. The list has 150 rows. 

I made a second sheet to display, in descending the order numbers.  I used:

=sort(sheet1!A2:B150, 1, TRUE) 

It worked! But it's a looooooong list and I would like to break this list in 3 columns, to display on a vertical monitor all orders at once. How can I do that, and keep it this sheet 2 always ordered descending ?

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