Tuesday, July 23, 2024

yogi_Calendar For Specified School Year Starting With Specified Month And Specified Day Of Week

 Oh Yes ... with the new school year coming up, there will be need to create and print a a calendar starting with a specified month and specified day of week

In the following I present a calendar that has been created with formulas and the calendar with automatically update as 

You input the Year, Starting Month, and Starting Day Of Week 

So 

you can create the calendar for yourself my making a copy of the spreadsheet for yourself 

Or

If there is interest, I can set up a Google Form for you to specify the Year, Starting Month and Starting Day of week and the calendar will update itself to suit your need


Saturday, December 31, 2022

It's The Time Of The Year -- Happy New Year 2023

Dear Family and Friends 

Happy New Year

Pray By Grace Of God We Will Get Over With Covid 19 Soon

I present to you a 12 Month Calendar You may want to print this just as presented Year 2023 starting with January 
or 
set it up using the formulas as presented starting with any month and any day of week
 and
 also for any other year to suit your fancy and to suit your needs 
I know some of our family and friends have different needs in that their School/Hostel/Business starts from a different month other than January -- so you can print this calendar starting with any month of the year to suit your needs.

 If there is interest in your supplying the starting month on-line I can set up a Form for you to supply the month on-line and the calendar will adjust to suit your needs 

W I T H   B E S T   W I S H E S !

Friday, December 31, 2021

It's The Time Of The Year -- Happy New Year 2022!

Dear Family and Friends 

Happy New Year

Pray By Grace Of God We Will Get Over With Covid 19 Soon

I present to you a 12 Month Calendar You may want to print this just as presented Year 2022 starting with January 
or 
set it up using the formulas as presented starting with any other month
 and
 also for any other year to suit your fancy and to suit your needs 
I know some of our family and friends have different needs in that their School/Hostel/Business starts from a different month other than January -- so you can print this calendar starting with any month of the year to suit your needs.

 If there is interest in your supplying the starting month on-line I can set up a Form for you to supply the month on-line and the calendar will adjust to suit your needs 

W I T H   B E S T   W I S H E S !

Thursday, December 31, 2020

. It's The Time Of The Year -- HAPPY NEW YEAR ! .

Dear Family and Friends

H A P P Y    N E W    YEAR !

By Grace Of God We Will Get Over With Covid Soon

I present to you a 12 Month Calendar
You may want to print this just as presented starting with January
or
set it up using the formulas as presented starting with any other month
and
also for any other year to suit your fancy and to suit your needs

I know some of our family and friends have different needs in that their School/Hostel/Business starts from a different month other than January -- so you can print this calendar starting with any month of the year to suit your needs.

If there is interest in your supplying the starting month on-line I can set up a Form for you to supply the month on-line and the calendar will adjust to suit your needs


W I T H    B E S T    W I S H E S !


Tuesday, December 31, 2019

It's The Time Of The Year -- HAPPY NEW YEAR 2020!

Dear Family and Friends

H A P P Y    N E W    YEAR !

I present to you a 12 Month Calendar
You may want to print this just as presented Year 2020 starting with January
or
set it up using the formulas as presented starting with any other month
and
also for any other year to suit your fancy and to suit your needs

W I T H    B E S T    W I S H E S !


Wednesday, April 17, 2019

Sunday, March 31, 2019

yogi_Conditionally Format Columnas A2:E If Date In Column A Is Over 13 Months Old

Google Spreadsheet   Post  #2592

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Mar-31-2019
question by: Tico
https://support.google.com/docs/thread/3351311?hl=en
Conditinally format entire row, based off of an "expiration date"
Is there a way to use conditional formatting to recolor an entire row if a cell filled in with a date in the corresponding row is more than a year old

For example,

Cell A1 is marked with a date. The rest of the row (A2:A10) are filled in with several other pieces of information assosiated with that date. I need to set it so that if the date in A1 is more than 13 months old ("expires") that the entire row will shade in. 

I know I can do this setting up a cell to be a "variable" but I would like to do this WITHOUT making another cell. This document will be going live on several business servers and I would like to keep it clean (and the less things people can mess up the better haha)

I am not sure if I am making any sense here... So I also made a test document to try and re explain what I am trying to get done here.


thank you all for your help!!!

Sunday, March 24, 2019

yogi_transpose text data from 8 columns to vertical grouping in google sheet

Google Spreadsheet   Post  #2591

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Mar-24-2019
question by: Brian Pugh
https://support.google.com/docs/thread/2937308?hl=en
Possible to transpose text data from 8 columns to vertical grouping in google sheet? 
I'm trying to help one of our teachers collect grad bios from our 8th grade students and send the data to the yearbook printer. The printer needs each student's submission to be grouped and listed in vertical order (see "vertical order" tab). Is there a formula that will transpose each student's 8 horizontally listed values into groups vertically? Thank you very much. example sheet


Friday, March 15, 2019

yogi_Conditionally Format Cells Where Entry After Fourth Dash In Rows 2 and 3 Do Not Match

Google Spreadsheet   Post  #2590

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Mar-15-2019
question by: John Borges
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/eJraFBCs9N0;context-place=forum/docs
Finding Occurrences Where Two Threads in a Multi-lined Cell Do Not Match

Saturday, February 9, 2019

yogi_For IDs In Column A Import Info For Associated Entities In Columns E:H While Making Manual Edits In Columns B C D I

Google Spreadsheet   Post  #2589

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Feb-09-2019
question by: ComputerCandy
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/WlYpzJnxYeA;context-place=forum/docs
Import Data per Row/Column, Based on ID Number
Is there a way to import data a row at a time based on matching the ID number of a column? (Using formulas or queries in the second header row.)

I'm working on several collecting spreadsheets. The goal is that there will be a checklist template that people case use where they can edit some columns. But other columns will auto-populate data based on catalog numbers that match the catalog numbers of a master list. For example...

I made a simplified version of what I'm trying to do that anyone can edit...

Master Database List:

User Checklist:

As you can see, some columns are fine for the user to edit information about their collection. But other more universal information should automatically import based on matching Catalog IDs. *If possible, it would be especially helpful if the user could still do normal column sorting.

Is this possible? Any help would be very appreciated. Thank you.

Thursday, February 7, 2019

yogi_Grouping pivoting and aggregating multiple columns with text

Google Spreadsheet   Post  #2588

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Feb-07-2019
question by: Reuben Robbins:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/1s_C0yEOFd4;context-place=forum/docs
Grouping, pivoting and aggregating columns multiple columns with text
I am trying to rearrange data from a big table with QUERY and IMPORTRANGE. Some of the cells contain text. The formula is:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/xxxxxxxxxxxxx", "Answer_Data!T2:BM"), "Select Col1, sum(Col8) where Col4 = 'langhandcg' and not Col6 contains 'Measure_' and not Col6 contains 'Item_' Group by Col1 pivot Col5 label Col1 'pid' ",0)

Here is a sample sheet that lays out my issues:


The issue is that I'm not truly summing col8, but in order to get the data to be filtered and aggregated the way I want, I can only think of doing a pivot table.

Is there another way I can get my data arranged how I want with QUERY and IMPORTRANGE? Or with other formulas?

--------------------------
Note:
In developing my solution I did not have to pivot the data ... if the data does have to be pivoted then my resulting can be transposed


Tuesday, February 5, 2019

yogi_Refresh Calc In Cell K27 if Changes Are Made In Cells K3:K15

Google Spreadsheet   Post  #2587

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Feb-05-2019
question by: MACK:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/Pd249NVcV8Y;context-place=forum/docs
Text zwischen zwei leeren Zellen Zählen
Hallo Helfer und Helferinnen,

bin auf der suche nach einer Lösung damit in einem Tabellen-Sheet, wo in Spalte B entweder "Ja" oder "Nein" steht und in Spalte A das Datum, wobei die gesamte Zeile wo das Datum steht leer ist.

Jetzt möchte ich eine Formel so Erstellen bzw. eine Benutzerdefinierte Formatierung erstellen die von einem Datum der Spalte B bis zum nächsten Datum der Spalte B die "Ja" zählt und das Ergebnis jedes Tagesabschnittes in der leeren Zelle in Spalte B anzeigt.

Hoffe die Frage ist verständlich geschrieben :)

Danke schon jetzt für Eure Hilfe


Sheet.JPGdas sollte so aussehen Smiley_Danke.jpg

Sunday, February 3, 2019

yogi_Count The Number Of 'Ja' In Column B Before the Row Of Date Change In Column A

Google Spreadsheet   Post  #2586

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Feb-03-2019
question by: MACK:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/Pd249NVcV8Y;context-place=forum/docs
Text zwischen zwei leeren Zellen Zählen
Hallo Helfer und Helferinnen,

bin auf der suche nach einer Lösung damit in einem Tabellen-Sheet, wo in Spalte B entweder "Ja" oder "Nein" steht und in Spalte A das Datum, wobei die gesamte Zeile wo das Datum steht leer ist.

Jetzt möchte ich eine Formel so Erstellen bzw. eine Benutzerdefinierte Formatierung erstellen die von einem Datum der Spalte B bis zum nächsten Datum der Spalte B die "Ja" zählt und das Ergebnis jedes Tagesabschnittes in der leeren Zelle in Spalte B anzeigt.

Hoffe die Frage ist verständlich geschrieben :)

Danke schon jetzt für Eure Hilfe


Sheet.JPGdas sollte so aussehen Smiley_Danke.jpg

Saturday, February 2, 2019

yogi_Conditionally Format A:B If There is Number In Column B And Format Column B For "Ja" and "Nein"

Google Spreadsheet   Post  #2585

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Feb-02-2019
question by: MACK:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/Pd249NVcV8Y;context-place=forum/docs
Text zwischen zwei leeren Zellen Zählen
Hallo Helfer und Helferinnen,

bin auf der suche nach einer Lösung damit in einem Tabellen-Sheet, wo in Spalte B entweder "Ja" oder "Nein" steht und in Spalte A das Datum, wobei die gesamte Zeile wo das Datum steht leer ist.

Jetzt möchte ich eine Formel so Erstellen bzw. eine Benutzerdefinierte Formatierung erstellen die von einem Datum der Spalte B bis zum nächsten Datum der Spalte B die "Ja" zählt und das Ergebnis jedes Tagesabschnittes in der leeren Zelle in Spalte B anzeigt.

Hoffe die Frage ist verständlich geschrieben :)

Danke schon jetzt für Eure Hilfe


Sheet.JPGdas sollte so aussehen Smiley_Danke.jpg


yogi_Conditionally Format Column B If Date Is Less_Than_7_days Less Than 1 Month Or More Than 1 Month Away

Google Spreadsheet   Post  #2584

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Feb-02-2019
question by: Laurence Browne:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/wzFydy5DY5c;context-place=forum/docs
Conditional format - Robot system for due dates
Hi 

I have a spreadsheet with multiple coloums for when re-training is required. I would like a robot colour system to easily identify when training is due based of the cell changing colour based on the date

Red - 1 week away
Amber - 1 month away
Green - over 1 month away

i.e. if the date of the cell is 19/11/2021 the cell should be green but if the cell is 07/02/2019 the cell should be red. 

Thank you for any help you can provide.

yogi_For The Entities In Column B Pull The Latest Instance Of Entity And The Amount In Column C

Google Spreadsheet   Post  #2583

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Feb-02-2019
question by: Harristweeter:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/Bvu4QmTcxL8;context-place=forum/docs

Find Most Recent Entry for a Given Category, and Return Third Column Information

Good Morning,

I am working on my budget spreadsheet. I have a sheet where each month I copy my bank statements as new rows (this allows me to track bank balances over time). I want to write a formula which finds the most recent statement for each bank and copies the information from the row.

For Example the table may look like this:

     A                      B                  C              D
12/2/18             Bank A       #123456       $101.50
12/5/18             Bank B       #789101      $8,890.02
12/10/18           Bank C      #121314       $0.01
01/10/19           Bank A       #123456       $10.78

Basically, I'll have another table that will have each account to show the most recent balance. I'm trying to identify a formula that will look through this sheet and find the December Entries for Banks B and C, and pull the bank balance (Column D) but will find the January Entry for Bank A (ignoring the December entry).

I'm stumped. Any advice?

Wednesday, January 30, 2019

yogi_WorkAround For How To Lookup Values Across Multiple Worksheets To Return Sheet Names

Google Spreadsheet   Post  #2582

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-30-2019
question by: Erin Sublette:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/y8kWoymOxRA;context-place=forum/docs
How To Vlookup Values Across Multiple Worksheets To Return Sheet Names
I would like to search multiple sheets in a workbook for a name of someone and then in the cell specify the sheet name. Below is a link to my document. 

On the Employee Info tab, I would like column B to reflect the sheet name (department) those workers are in. 





Monday, January 28, 2019

yogi_Conditionally Format Column B If Date is Between 0 to 2 2 to 4 and 4 Months Past

Google Spreadsheet   Post  #2581

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-28-2019
question by: Konrad24:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/y8kWoymOxRA;context-place=forum/docs

Condition Format

I'm trying to create a sheet that highlights dates in a cell that are in between a specific date range. For example if a date is between 0-2 months of the current date it will highlight the cell green. 2-4 months of current date will highlight yellow and 4 months and beyond red. Help would be greatly appreciated. Thanks

Wednesday, January 23, 2019

yogi_Count Row By Row How Many Numbers In Each Row Are Smaller Than The Numbers In Row Below

Google Spreadsheet   Post  #2581

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-23-2019
question by: mooflower:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/GiYRp6j8aGA;context-place=topicsearchin/docs/category$3Ahow-do-i
Count number of cells in a range whose value exceeds a cell value located above it
I'm trying to count the number of cells in a range that exceed the value of a cell located just above it.  CountIf doesn't fit, would one of the database functions do it, such as DCOUNTA?  I could of course add it all up using a ternary IF, but that will be a lot of IF statements for a long range.

Example (comma separated values):

4, 5, 4, 3, 4
5, 5, 3, 3, 5

The number of times the lower range exceeds the value above it: 2   (the first and last entries)

Thanks !

Sunday, January 20, 2019

yogi_Compute Row By Row Running Average Of Numbers In Column B

Google Spreadsheet   Post  #2580

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-20-2019
question by: Viktor Petrovich:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/rszAV_rtfwE;context-place=forum/docs
daily average
Hi
I have a simple sheet and  which create new row every day with date (A) and daily amount (B) 
Need the formula to put daily average in (C)

appreciate any help with that


Clip2net_190120235641.png
5 KB