tag:blogger.com,1999:blog-88078055915476735162024-03-16T23:52:00.429-07:00Cloud Computing -- Google Docs WayWith almost universal availability of Internet and the availability of reliable on-line productivity tools, such as Google Docs, individuals as well as companies are switching over to Cloud Computing. In this blog I will post items of interest to my colleagues/patrons/clients.Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.comBlogger2632125tag:blogger.com,1999:blog-8807805591547673516.post-82810567208335068632022-12-31T21:00:00.011-08:002023-01-14T14:11:02.402-08:00It's The Time Of The Year -- Happy New Year 2023<div style="text-align: center;">Dear Family and Friends </div><div style="text-align: center;"><br /></div><div style="text-align: center;"><span style="color: red; font-size: large;">Happy New Year</span></div><div style="text-align: center;"><span style="font-size: large;"><br /></span></div><div style="text-align: center;"><span style="color: red; font-size: large;">Pray By Grace Of God We Will Get Over With Covid 19 Soon</span></div><div style="text-align: center;"><span style="font-size: large;"><br /></span></div><div style="text-align: center;"><span><span style="text-align: left;">I present to you a 12 Month Calendar You may want to print this just as presented Year 2023 starting with January </span></span></div><div style="text-align: center;"><span><span style="text-align: left;">or </span></span></div><div style="text-align: center;"><span><span style="text-align: left;">set it up using the formulas as presented starting with any month and any day of week </span></span></div><div style="text-align: center;"><span><span style="text-align: left;"> and</span></span></div><div style="text-align: center;"><span><span style="text-align: left;"> also for any other year to suit your fancy and to suit your needs </span></span></div><div style="text-align: center;"><span><span style="text-align: left;">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.</span></span></div><div style="text-align: center;"><span><span style="text-align: left;"><br /></span></span></div><div style="text-align: center;"><span><span style="text-align: left;"> 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<span style="font-size: x-small;"> </span></span></span></div><div style="text-align: center;"><span style="font-size: large;"><span style="font-size: medium; text-align: left;"><br /></span></span></div><div style="text-align: center;"><span style="font-size: large;"><span style="color: red; text-align: left;">W I T H B E S T W I S H E S !</span></span></div><div style="text-align: center;"><span style="font-size: large;"><br /></span></div><div style="text-align: center;"><iframe height="1050" width="830" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vSDEGJGuonXcWPvjSY36sPtXrBGIKpKdmqRb5SNKEA-012U_MsQtPcB9ceEw_06gsglR7gwG2sVGrLF/pubhtml?widget=true&headers=false"></iframe></div>Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com0tag:blogger.com,1999:blog-8807805591547673516.post-18638555645284259162021-12-31T20:05:00.009-08:002023-01-15T05:53:00.390-08:00It's The Time Of The Year -- Happy New Year 2022!<div style="text-align: center;">Dear Family and Friends </div><div style="text-align: center;"><br /></div><div style="text-align: center;"><span style="color: red; font-size: large;">Happy New Year</span></div><div style="text-align: center;"><span style="font-size: large;"><br /></span></div><div style="text-align: center;"><span style="color: red; font-size: large;">Pray By Grace Of God We Will Get Over With Covid 19 Soon</span></div><div style="text-align: center;"><span style="font-size: large;"><br /></span></div><div style="text-align: center;"><span><span style="text-align: left;">I present to you a 12 Month Calendar You may want to print this just as presented Year 2022 starting with January </span></span></div><div style="text-align: center;"><span><span style="text-align: left;">or </span></span></div><div style="text-align: center;"><span><span style="text-align: left;">set it up using the formulas as presented starting with any other month</span></span></div><div style="text-align: center;"><span><span style="text-align: left;"> and</span></span></div><div style="text-align: center;"><span><span style="text-align: left;"> also for any other year to suit your fancy and to suit your needs </span></span></div><div style="text-align: center;"><span><span style="text-align: left;">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.</span></span></div><div style="text-align: center;"><span><span style="text-align: left;"><br /></span></span></div><div style="text-align: center;"><span><span style="text-align: left;"> 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<span style="font-size: x-small;"> </span></span></span></div><div style="text-align: center;"><span style="font-size: large;"><span style="font-size: medium; text-align: left;"><br /></span></span></div><div style="text-align: center;"><span style="font-size: large;"><span style="color: red; text-align: left;">W I T H B E S T W I S H E S !</span></span></div><div style="text-align: center;"><span style="font-size: large;"><br /></span></div><div style="text-align: center;"><iframe height="1000" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vSDEGJGuonXcWPvjSY36sPtXrBGIKpKdmqRb5SNKEA-012U_MsQtPcB9ceEw_06gsglR7gwG2sVGrLF/pubhtml?gid=797144369&single=true&widget=true&headers=false" width="830"></iframe></div>Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com1tag:blogger.com,1999:blog-8807805591547673516.post-33692803003964652732020-12-31T15:46:00.009-08:002021-12-31T21:22:36.071-08:00 . It's The Time Of The Year -- HAPPY NEW YEAR ! .<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: center;">
Dear Family and Friends</div>
<div style="text-align: center;">
<br /></div>
<div style="text-align: center;">
<span style="color: red; font-size: large;">H A P P Y N E W YEAR !</span></div><div style="text-align: center;"><span style="color: red; font-size: large;"><br /></span></div><div style="text-align: center;"><span style="color: red; font-size: large;">By Grace Of God We Will Get Over With Covid Soon</span></div>
<div style="text-align: center;">
<br /></div>
<div style="text-align: center;">
I present to you a 12 Month Calendar</div>
<div style="text-align: center;">
You may want to print this just as presented starting with January</div>
<div style="text-align: center;">
or</div>
<div style="text-align: center;">
set it up using the formulas as presented starting with any other month</div>
<div style="text-align: center;">
and</div>
<div style="text-align: center;">
also for any other year to suit your fancy and to suit your needs</div><div style="text-align: center;"><br /></div><div style="text-align: center;">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.</div><div style="text-align: center;"><br /></div><div style="text-align: center;">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</div><div style="text-align: center;"><br />
<br />
<span style="color: red; font-size: large;">W I T H B E S T W I S H E S !</span></div>
<div style="text-align: center;">
<br /></div>
<div style="text-align: center;">
<br /></div>
</div>
</div>
<iframe height="1000" src="https://docs.google.com/spreadsheets/d/1cT7ehwwnKyVNz-aLiBU-JFVH4iXDickT8e4QFcr6yZ4/edit#gid=910942469/pubhtml?widget=true&headers=true" width="830"></iframe></div>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com0tag:blogger.com,1999:blog-8807805591547673516.post-47207754702392370532019-12-31T11:56:00.003-08:002022-07-16T07:37:47.810-07:00It's The Time Of The Year -- HAPPY NEW YEAR 2020!<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: center;">
Dear Family and Friends</div>
<div style="text-align: center;">
<br /></div>
<div style="text-align: center;">
<span style="color: red; font-size: large;">H A P P Y N E W YEAR !</span></div>
<div style="text-align: center;">
<br /></div>
<div style="text-align: center;">
I present to you a 12 Month Calendar</div>
<div style="text-align: center;">
You may want to print this just as presented Year 2020 starting with January</div>
<div style="text-align: center;">
or</div>
<div style="text-align: center;">
set it up using the formulas as presented starting with any other month</div>
<div style="text-align: center;">
and</div>
<div style="text-align: center;">
also for any other year to suit your fancy and to suit your needs<br />
<br />
<span style="color: red; font-size: large;">W I T H B E S T W I S H E S !</span></div>
<div style="text-align: center;">
<br /></div>
<div style="text-align: center;">
<br /></div>
</div>
</div>
<iframe height="1000" src="https://docs.google.com/spreadsheets/d/1cT7ehwwnKyVNz-aLiBU-JFVH4iXDickT8e4QFcr6yZ4/edit#gid=910942469/pubhtml?widget=true&headers=true" width="830"></iframe></div>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com1tag:blogger.com,1999:blog-8807805591547673516.post-49707219676785406492019-04-17T17:54:00.000-07:002019-04-17T18:02:13.456-07:00yogi_Hide And Unhide Rows Based On Values In A Column In Another Tab Using FilterViews<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: "times new roman";">
<span dir="auto" style="border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="color: blue; font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><i>Google Spreadsheet Post #2593</i></span></span></div>
<div style="color: #222222; font-family: "times new roman";">
<span style="color: #222222; font-family: "times new roman"; font-size: small; font-style: italic;"></span><br /></div>
<div>
<div style="color: #222222; font-family: "times new roman";">
<i><span style="color: blue;"><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Yogi Anand, D.Eng, P.E.</span><span style="font-family: "times" , "times new roman" , serif; font-size: small; text-align: center;"> ANAND Enterprises LLC -- Rochester Hills MI</span></span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><span style="color: blue;"> </span> </span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"> </span><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Apr-16-2019</span></i></div>
<div>
<span style="color: #222222; font-family: "times new roman";">question by: Ciara C Doyle</span><br />
<div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<a href="https://support.google.com/docs/thread/4400142?hl=en">https://support.google.com/docs/thread/4400142?hl=en</a><br />
<span style="background-color: white; color: #3c4043; font-family: "google sans" , "roboto" , sans-serif; font-size: 24px;">How to conditionally hide rows?</span></div>
</div>
</div>
</div>
</div>
</div>
<iframe height="800" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vQ2qChnIFVkMxORdTpwFU8JEyvG5-APIWaHbz0LGfmRHNo29lS-qC1_meDamvbcPUo2znKPbqf6hEZu/pubhtml?gid=1365188014& chrome=true;single=true&widget=true&headers=true" width="836"></iframe></div>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com64tag:blogger.com,1999:blog-8807805591547673516.post-56337634871047936542019-03-31T17:24:00.000-07:002019-03-31T17:26:40.035-07:00yogi_Conditionally Format Columnas A2:E If Date In Column A Is Over 13 Months Old <div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: "times new roman";">
<span dir="auto" style="border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="color: blue; font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><i>Google Spreadsheet Post #2592</i></span></span></div>
<div style="color: #222222; font-family: "times new roman";">
<span style="color: #222222; font-family: "times new roman"; font-size: small; font-style: italic;"></span><br /></div>
<div>
<div style="color: #222222; font-family: "times new roman";">
<i><span style="color: blue;"><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Yogi Anand, D.Eng, P.E.</span><span style="font-family: "times" , "times new roman" , serif; font-size: small; text-align: center;"> ANAND Enterprises LLC -- Rochester Hills MI</span></span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><span style="color: blue;"> </span> </span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"> </span><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Mar-31-2019</span></i></div>
<div>
<span style="color: #222222; font-family: "times new roman";">question by: Tico</span><br />
<div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<a href="https://support.google.com/docs/thread/3351311?hl=en">https://support.google.com/docs/thread/3351311?hl=en</a><br />
<span style="background-color: white; color: #3c4043; font-family: "google sans" , "roboto" , sans-serif; font-size: 24px;">Conditinally format entire row, based off of an "expiration date"</span><br />
<span style="background-color: white; color: #3c4043; font-family: "roboto" , "helvetica neue" , "helvetica" , sans-serif; font-size: 14px;">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</span><br />
<div style="background-color: white; color: #3c4043; font-family: Roboto, "Helvetica Neue", Helvetica, sans-serif; font-size: 14px; outline: none;">
<br /></div>
<div style="background-color: white; color: #3c4043; font-family: Roboto, "Helvetica Neue", Helvetica, sans-serif; font-size: 14px; outline: none;">
For example,</div>
<div style="background-color: white; color: #3c4043; font-family: Roboto, "Helvetica Neue", Helvetica, sans-serif; font-size: 14px; outline: none;">
<br /></div>
<div style="background-color: white; color: #3c4043; font-family: Roboto, "Helvetica Neue", Helvetica, sans-serif; font-size: 14px; outline: none;">
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. </div>
<div style="background-color: white; color: #3c4043; font-family: Roboto, "Helvetica Neue", Helvetica, sans-serif; font-size: 14px; outline: none;">
<br /></div>
<div style="background-color: white; color: #3c4043; font-family: Roboto, "Helvetica Neue", Helvetica, sans-serif; font-size: 14px; outline: none;">
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)</div>
<div style="background-color: white; color: #3c4043; font-family: Roboto, "Helvetica Neue", Helvetica, sans-serif; font-size: 14px; outline: none;">
<br /></div>
<div style="background-color: white; color: #3c4043; font-family: Roboto, "Helvetica Neue", Helvetica, sans-serif; font-size: 14px; outline: none;">
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.</div>
<div style="background-color: white; color: #3c4043; font-family: Roboto, "Helvetica Neue", Helvetica, sans-serif; font-size: 14px; outline: none;">
<br /></div>
<div style="background-color: white; color: #3c4043; font-family: Roboto, "Helvetica Neue", Helvetica, sans-serif; font-size: 14px; outline: none;">
<a href="https://docs.google.com/spreadsheets/d/17yV6Nn-7f5CgzrFkU3LWqBv9lvfk8oz4R9v8MH9ugwA/edit?usp=sharing" rel="nofollow noopener" style="color: #1a73e8; text-decoration-line: none;" target="_blank">https://docs.google.com/spreadsheets/d/17yV6Nn-7f5CgzrFkU3LWqBv9lvfk8oz4R9v8MH9ugwA/edit?usp=sharing</a></div>
<div style="background-color: white; color: #3c4043; font-family: Roboto, "Helvetica Neue", Helvetica, sans-serif; font-size: 14px; outline: none;">
<br /></div>
<div style="background-color: white; color: #3c4043; font-family: Roboto, "Helvetica Neue", Helvetica, sans-serif; font-size: 14px; outline: none;">
thank you all for your help!!!</div>
<span style="background-color: white; color: #3c4043; font-family: "google sans" , "roboto" , sans-serif; font-size: 24px;"><br /></span></div>
</div>
</div>
</div>
</div>
</div>
<iframe height="700" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vS6T50KOKF2dhVGCHkbL6s6CtzSv0zjF17Ns0pNymfkyRNfAfYHslc4XyCe9Xbo1WN4JyWArB1M1sWJ/pubhtml?widget=true&headers=true" width="836"></iframe></div>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com0tag:blogger.com,1999:blog-8807805591547673516.post-44418374420766461392019-03-24T10:56:00.000-07:002019-03-24T11:00:48.923-07:00yogi_transpose text data from 8 columns to vertical grouping in google sheet<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: "times new roman";">
<span dir="auto" style="border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="color: blue; font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><i>Google Spreadsheet Post #2591</i></span></span></div>
<div style="color: #222222; font-family: "times new roman";">
<span style="color: #222222; font-family: "times new roman"; font-size: small; font-style: italic;"></span><br /></div>
<div>
<div style="color: #222222; font-family: "times new roman";">
<i><span style="color: blue;"><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Yogi Anand, D.Eng, P.E.</span><span style="font-family: "times" , "times new roman" , serif; font-size: small; text-align: center;"> ANAND Enterprises LLC -- Rochester Hills MI</span></span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><span style="color: blue;"> </span> </span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"> </span><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Mar-24-2019</span></i></div>
<div>
<span style="color: #222222; font-family: "times new roman";">question by: Brian Pugh</span><br />
<div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="color: #0000ee; font-family: "roboto" , "arial" , sans-serif;"><span style="font-size: 14px; white-space: nowrap;"><u>https://support.google.com/docs/thread/2937308?hl=en</u></span></span><br />
<span style="background-color: white; color: #3c4043; font-family: "google sans" , "roboto" , sans-serif; font-size: 24px;">Possible to transpose text data from 8 columns to vertical grouping in google sheet? </span><br />
<div class="thread-question__payload" style="background-color: white; color: #3c4043; font-family: Roboto, "Helvetica Neue", Helvetica, sans-serif; font-size: 14px; margin: 1rem 0px; max-height: 9999px; outline: none; overflow: hidden; transition: max-height 8s ease 0s;">
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. <a href="https://docs.google.com/spreadsheets/d/19IdbRL05P1ynxDFeScWUD4jjFLLwty2KhK1lVpBFSWc/edit?usp=sharing" rel="nofollow noopener" style="color: #1a73e8; text-decoration-line: none;" target="_blank">example sheet</a></div>
<div class="thread-question__details" style="background-color: white; color: #3c4043; font-family: Roboto, "Helvetica Neue", Helvetica, sans-serif; font-size: 14px; margin-bottom: 1.5rem; outline: none;">
<div class="thread-question__details-label" style="outline: none;">
Details</div>
<div class="thread-question__details-list" style="outline: none;">
<a href="https://support.google.com/docs/threads?hl=en&thread_filter=(category:docs_sheets)" style="color: #1a73e8; text-decoration-line: none;">Sheets</a>, <a data-thread-question__details-property-name="docs_platform" data-thread-question__details-property-value="docs_microsoft" href="https://support.google.com/docs/threads?hl=en&thread_filter=(detail:docs_platform:docs_microsoft)" style="color: #1a73e8; text-decoration-line: none;">Microsoft Windows</a>, <a data-thread-question__details-property-name="docs_package" data-thread-question__details-property-value="docs_education" href="https://support.google.com/docs/threads?hl=en&thread_filter=(detail:docs_package:docs_education)" style="color: #1a73e8; text-decoration-line: none;">Education</a></div>
</div>
<span style="color: #0000ee; font-family: "roboto" , "arial" , sans-serif;"><span style="font-size: 14px; white-space: nowrap;"><u><br /></u></span></span>
<span style="color: #0000ee; font-family: "roboto" , "arial" , sans-serif;"><span style="font-size: 14px; white-space: nowrap;"><u><br /></u></span></span></div>
</div>
</div>
</div>
</div>
</div>
<iframe height="800" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vTADedAM2dMgXoO_ozVc6H_Mz5CT4G2cHUArn8tQWXd-TuvMZmIVAeGtED3ilkTFiCZVPh63q2pTV_s/pubhtml?widget=true&headers=true" width="836"></iframe></div>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com1tag:blogger.com,1999:blog-8807805591547673516.post-73784834201396462922019-03-15T11:08:00.000-07:002019-03-15T11:08:03.076-07:00yogi_Conditionally Format Cells Where Entry After Fourth Dash In Rows 2 and 3 Do Not Match<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: "times new roman";">
<span dir="auto" style="border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="color: blue; font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><i>Google Spreadsheet Post #2590</i></span></span></div>
<div style="color: #222222; font-family: "times new roman";">
<span style="color: #222222; font-family: "times new roman"; font-size: small; font-style: italic;"></span><br /></div>
<div>
<div style="color: #222222; font-family: "times new roman";">
<i><span style="color: blue;"><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Yogi Anand, D.Eng, P.E.</span><span style="font-family: "times" , "times new roman" , serif; font-size: small; text-align: center;"> ANAND Enterprises LLC -- Rochester Hills MI</span></span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><span style="color: blue;"> </span> </span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"> </span><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Mar-15-2019</span></i></div>
<div>
<span style="color: #222222; font-family: "times new roman";">question by: John Borges</span><br />
<div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;">https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/eJraFBCs9N0;context-place=forum/docs</u><br />
<span style="background-color: white; color: rgba(0, 0, 0, 0.85); font-family: Roboto, Arial, sans-serif; font-size: 17px;">Finding Occurrences Where Two Threads in a Multi-lined Cell Do Not Match</span></div>
</div>
</div>
</div>
</div>
</div>
<iframe height="1200" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vTDcME2d-jS9tWVGHOr3uI7G7twR6y-T7beV4nbxIfslryu8272wWn0DrbvbSzHRxGtl-UCCgHOQE_9/pubhtml?widget=true&headers=true" width="836"></iframe></div>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com0tag:blogger.com,1999:blog-8807805591547673516.post-61436700489638404612019-02-09T12:27:00.000-08:002019-02-09T12:29:29.781-08:00yogi_For IDs In Column A Import Info For Associated Entities In Columns E:H While Making Manual Edits In Columns B C D I<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: "times new roman";">
<span dir="auto" style="border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="color: blue; font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><i>Google Spreadsheet Post #2589</i></span></span></div>
<div style="color: #222222; font-family: "times new roman";">
<span style="color: #222222; font-family: "times new roman"; font-size: small; font-style: italic;"></span><br /></div>
<div>
<div style="color: #222222; font-family: "times new roman";">
<i><span style="color: blue;"><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Yogi Anand, D.Eng, P.E.</span><span style="font-family: "times" , "times new roman" , serif; font-size: small; text-align: center;"> ANAND Enterprises LLC -- Rochester Hills MI</span></span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><span style="color: blue;"> </span> </span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"> </span><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Feb-09-2019</span></i></div>
<div>
<span style="color: #222222; font-family: "times new roman";">question by: ComputerCandy</span><br />
<div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;">https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/WlYpzJnxYeA;context-place=forum/docs</u><br />
<span style="background-color: white; color: rgba(0 , 0 , 0 , 0.85); font-family: "roboto" , "arial" , sans-serif; font-size: 17px;">Import Data per Row/Column, Based on ID Number</span><br />
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
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.)</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<span style="background-color: white; color: #222222; font-family: "roboto" , "arial" , sans-serif; font-size: 13px;">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...</span><br />
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
I made a simplified version of what I'm trying to do that anyone can edit...<br />
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
Master Database List:</div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<a href="https://docs.google.com/spreadsheets/d/1qLCZOtByZFO47gtM2f7oewhgZ_RR2maXZ2_XkQ05Ks4/edit?usp=sharing" rel="nofollow" style="border: 0px; color: #6611cc; cursor: pointer; margin: 0px; padding: 0px; text-decoration-line: none;" target="_blank">https://docs.google.com/<wbr></wbr>spreadsheets/d/<wbr></wbr>1qLCZOtByZFO47gtM2f7oewhgZ_<wbr></wbr>RR2maXZ2_XkQ05Ks4/edit?usp=<wbr></wbr>sharing</a></div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
User Checklist:</div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<a href="https://docs.google.com/spreadsheets/d/1FsGx9YkQiMc6id5L7wguJ-xeGcJx-qHSB3fklBLgPyo/edit?usp=sharing" rel="nofollow" style="border: 0px; color: #6611cc; cursor: pointer; margin: 0px; padding: 0px; text-decoration-line: none;" target="_blank">https://docs.google.com/<wbr></wbr>spreadsheets/d/<wbr></wbr>1FsGx9YkQiMc6id5L7wguJ-xeGcJx-<wbr></wbr>qHSB3fklBLgPyo/edit?usp=<wbr></wbr>sharing</a></div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
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. <b>*If possible, it would be especially helpful if the user could still do normal column sorting.</b></div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
Is this possible? Any help would be very appreciated. Thank you.</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<iframe width=836 height=900 src="https://docs.google.com/spreadsheets/d/e/2PACX-1vQrS9sPZDEDE8zIHBEXojltFsDrdAsyhQ9mO03n5PNFlsMI0n6JA19bB1XG_hk4Jv0Hv4IvP96241-S/pubhtml?widget=true&headers=true"></iframe>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com0tag:blogger.com,1999:blog-8807805591547673516.post-2358322793045933782019-02-07T19:34:00.000-08:002019-02-07T19:39:53.745-08:00yogi_Grouping pivoting and aggregating multiple columns with text<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: "times new roman";">
<span dir="auto" style="border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="color: blue; font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><i>Google Spreadsheet Post #2588</i></span></span></div>
<div style="color: #222222; font-family: "times new roman";">
<span style="color: #222222; font-family: "times new roman"; font-size: small; font-style: italic;"></span><br /></div>
<div>
<div style="color: #222222; font-family: "times new roman";">
<i><span style="color: blue;"><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Yogi Anand, D.Eng, P.E.</span><span style="font-family: "times" , "times new roman" , serif; font-size: small; text-align: center;"> ANAND Enterprises LLC -- Rochester Hills MI</span></span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><span style="color: blue;"> </span> </span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"> </span><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Feb-07-2019</span></i></div>
<div>
<span style="color: #222222; font-family: "times new roman";">question by: Reuben Robbins</span><span style="background-color: white; color: #999999; font-family: "roboto" , "arial" , sans-serif; font-size: 14px; white-space: nowrap;">:</span><br />
<div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;">https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/1s_C0yEOFd4;context-place=forum/docs</u><br />
<span style="background-color: white; color: rgba(0 , 0 , 0 , 0.85); font-family: "roboto" , "arial" , sans-serif; font-size: 17px; white-space: nowrap;">Grouping, pivoting and aggregating columns multiple columns with text</span><br />
<span style="background-color: white; color: #222222; font-family: "roboto" , "arial" , sans-serif; font-size: 13px;">I am trying to rearrange data from a big table with QUERY and IMPORTRANGE. Some of the cells contain text. The formula is:</span><br />
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;">=</span><span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;">QUERY</span><span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;">(</span><span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;">IMPORTRANGE</span><span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;">(</span><span dir="auto" style="border: 0px; color: green; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;">"<a href="https://docs.google.com/spreadsheets/xxxxxxxxxxxxx" rel="nofollow" style="border: 0px; color: #6611cc; cursor: pointer; margin: 0px; padding: 0px; text-decoration-line: none;" target="_blank">https://<wbr></wbr>docs.google.com/spreadsheets/<wbr></wbr>xxxxxxxxxxxxx</a>"</span><span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;">,</span><span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;"> </span><span dir="auto" style="border: 0px; color: green; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;">"Answer_Data!T2:BM"</span><span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;">)</span><span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;">,</span><span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;"> </span><span dir="auto" style="border: 0px; color: green; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;">"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' "</span><span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;">,</span><span dir="auto" style="border: 0px; color: #1155cc; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;">0</span><span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;">)</span><span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;"> </span></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;"><br /></span></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;">Here is a sample sheet that lays out my issues: </span></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;"><a href="https://docs.google.com/spreadsheets/d/1NzTF-n4X5bv3Ey0kykkTH4hubOaF1vZTbK--8CiyWDc/edit?usp=sharing" rel="nofollow" style="border: 0px; color: #6611cc; cursor: pointer; margin: 0px; padding: 0px; text-decoration-line: none;" target="_blank">https://docs.google.com/<wbr></wbr>spreadsheets/d/1NzTF-<wbr></wbr>n4X5bv3Ey0kykkTH4hubOaF1vZTbK-<wbr></wbr>-8CiyWDc/edit?usp=sharing</a> </span></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;"><br /></span></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;"><br /></span></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;">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. </span></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;"><br /></span></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;">Is there another way I can get my data arranged how I want with QUERY and IMPORTRANGE? Or with other formulas?</span><br />
<span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;"><br /></span>
<span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;">--------------------------</span><br />
<span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;">Note:</span><br />
<span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;">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</span><br />
<span dir="auto" style="border: 0px; color: black; font-family: "inconsolata" , monospace , "arial" , "sans" , sans-serif; font-size: 14px; margin: 0px; padding: 0px; white-space: pre-wrap;"><br /></span></div>
<span style="background-color: white; color: rgba(0 , 0 , 0 , 0.85); font-family: "roboto" , "arial" , sans-serif; font-size: 17px; white-space: nowrap;"><br /></span></div>
</div>
</div>
</div>
</div>
</div>
<iframe height="1000" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vR7P-L2S5zTD3e5c45oXmXC4ikIaJgCSxw_pipU9dHFMRXlYpU7S5XoKvIWxbA1ujfdFOcoA3m5aR_O/pubhtml?widget=true&headers=true" width="836"></iframe></div>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com1tag:blogger.com,1999:blog-8807805591547673516.post-20448818512837136672019-02-05T13:59:00.000-08:002019-02-05T13:59:31.162-08:00yogi_Refresh Calc In Cell K27 if Changes Are Made In Cells K3:K15<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: "times new roman";">
<span dir="auto" style="border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="color: blue; font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><i>Google Spreadsheet Post #2587</i></span></span></div>
<div style="color: #222222; font-family: "times new roman";">
<span style="color: #222222; font-family: "times new roman"; font-size: small; font-style: italic;"></span><br /></div>
<div>
<div style="color: #222222; font-family: "times new roman";">
<i><span style="color: blue;"><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Yogi Anand, D.Eng, P.E.</span><span style="font-family: "times" , "times new roman" , serif; font-size: small; text-align: center;"> ANAND Enterprises LLC -- Rochester Hills MI</span></span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><span style="color: blue;"> </span> </span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"> </span><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Feb-05-2019</span></i></div>
<div>
<span style="color: #222222; font-family: "times new roman";">question by: MACK</span><span style="background-color: white; color: #999999; font-family: "roboto" , "arial" , sans-serif; font-size: 14px; white-space: nowrap;">:</span><br />
<div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;">https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/Pd249NVcV8Y;context-place=forum/docs</u><br />
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;"><span style="background-color: white; color: rgba(0 , 0 , 0 , 0.85); font-family: "roboto" , "arial" , sans-serif; font-size: 17px; white-space: normal;">Text zwischen zwei leeren Zellen Zählen</span></u><br />
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;"><span style="background-color: white; color: #222222; font-family: "roboto" , "arial" , sans-serif; font-size: 13px; white-space: normal;">Hallo Helfer und Helferinnen,</span></u><br />
<div style="background-color: white; border: 0px; color: #222222; font-family: roboto, arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: roboto, arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
bin auf der suche nach einer Lösung damit in einem Tabellen-Sheet, wo in <b>Spalte B</b> entweder "<i><b>Ja</b></i>" oder "<b><i>Nein</i></b>" steht und in <b>Spalte A </b>das <b><i>Datum</i></b>, wobei die gesamte Zeile wo das Datum steht leer ist.</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: roboto, arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: roboto, arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
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 "<i><b>Ja</b></i>" zählt und das Ergebnis jedes Tagesabschnittes in der leeren Zelle in Spalte B anzeigt.</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: roboto, arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: roboto, arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
Hoffe die Frage ist verständlich geschrieben :)</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: roboto, arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: roboto, arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
Danke schon jetzt für Eure Hilfe</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: roboto, arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;"></u><br />
<div style="background-color: white; border: 0px; clear: both; color: #222222; font-family: roboto, arial, sans-serif; font-size: 13px; padding: 0px; text-align: center;">
<img alt="Sheet.JPG" height="320" src="https://groups.google.com/a/googleproductforums.com/group/docs/attach/f93b8f906898c/Sheet.JPG?part=0.1&view=1&authuser=0" style="border: 0px; margin: 0px 1em; padding: 0px;" width="199" />das sollte so aussehen <a href="https://www.blogger.com/null" style="border: 0px; color: #1155cc; cursor: pointer; margin: 0px 1em; padding: 0px;"><img alt="Smiley_Danke.jpg" border="0" height="153" src="https://groups.google.com/a/googleproductforums.com/group/docs/attach/f93b8f906898c/Smiley_Danke.jpg?part=0.2&view=1&authuser=0" style="border: 0px; margin: 0px; padding: 0px;" width="200" /></a></div>
</div>
</div>
</div>
</div>
</div>
</div>
<iframe height="750" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vRSAI_Xro0I2QP6n3UfdgmtkOxG6rFVv2KDJGGTcr1O6fVeRNq_xQ_SublntuwXg64OIylbm4WUTQrx/pubhtml?widget=true&headers=true" width="836"></iframe></div>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com0tag:blogger.com,1999:blog-8807805591547673516.post-11349687778224879532019-02-03T17:11:00.000-08:002019-02-05T14:04:00.596-08:00yogi_Count The Number Of 'Ja' In Column B Before the Row Of Date Change In Column A <div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: "times new roman";">
<span dir="auto" style="border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="color: blue; font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><i>Google Spreadsheet Post #2586</i></span></span></div>
<div style="color: #222222; font-family: "times new roman";">
<span style="color: #222222; font-family: "times new roman"; font-size: small; font-style: italic;"></span><br /></div>
<div>
<div style="color: #222222; font-family: "times new roman";">
<i><span style="color: blue;"><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Yogi Anand, D.Eng, P.E.</span><span style="font-family: "times" , "times new roman" , serif; font-size: small; text-align: center;"> ANAND Enterprises LLC -- Rochester Hills MI</span></span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><span style="color: blue;"> </span> </span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"> </span><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Feb-03-2019</span></i></div>
<div>
<span style="color: #222222; font-family: "times new roman";">question by: MACK</span><span style="background-color: white; color: #999999; font-family: "roboto" , "arial" , sans-serif; font-size: 14px; white-space: nowrap;">:</span><br />
<div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;">https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/Pd249NVcV8Y;context-place=forum/docs</u><br />
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;"><span style="background-color: white; color: rgba(0 , 0 , 0 , 0.85); font-family: "roboto" , "arial" , sans-serif; font-size: 17px; white-space: normal;">Text zwischen zwei leeren Zellen Zählen</span></u><br />
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;"><span style="background-color: white; color: #222222; font-family: "roboto" , "arial" , sans-serif; font-size: 13px; white-space: normal;">Hallo Helfer und Helferinnen,</span></u><br />
<div style="background-color: white; border: 0px; color: #222222; font-family: roboto, arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: roboto, arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
bin auf der suche nach einer Lösung damit in einem Tabellen-Sheet, wo in <b>Spalte B</b> entweder "<i><b>Ja</b></i>" oder "<b><i>Nein</i></b>" steht und in <b>Spalte A </b>das <b><i>Datum</i></b>, wobei die gesamte Zeile wo das Datum steht leer ist.</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: roboto, arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: roboto, arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
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 "<i><b>Ja</b></i>" zählt und das Ergebnis jedes Tagesabschnittes in der leeren Zelle in Spalte B anzeigt.</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: roboto, arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: roboto, arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
Hoffe die Frage ist verständlich geschrieben :)</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: roboto, arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: roboto, arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
Danke schon jetzt für Eure Hilfe</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: roboto, arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;"></u><br />
<div style="background-color: white; border: 0px; clear: both; color: #222222; font-family: roboto, arial, sans-serif; font-size: 13px; padding: 0px; text-align: center;">
<img alt="Sheet.JPG" height="320" src="https://groups.google.com/a/googleproductforums.com/group/docs/attach/f93b8f906898c/Sheet.JPG?part=0.1&view=1&authuser=0" style="border: 0px; margin: 0px 1em; padding: 0px;" width="199" />das sollte so aussehen <a href="https://www.blogger.com/null" style="border: 0px; color: #1155cc; cursor: pointer; margin: 0px 1em; padding: 0px;"><img alt="Smiley_Danke.jpg" border="0" height="153" src="https://groups.google.com/a/googleproductforums.com/group/docs/attach/f93b8f906898c/Smiley_Danke.jpg?part=0.2&view=1&authuser=0" style="border: 0px; margin: 0px; padding: 0px;" width="200" /></a></div>
</div>
</div>
</div>
</div>
</div>
</div>
<iframe height="750" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vR-v7GZ1rfIAYlWBAqB8f_1SmLFzkQMSCXeG6mQh5AClHr14NUm5ojMOJmjcHme0nQ3m78_al6odtF-/pubhtml?widget=true&headers=true" width="836"></iframe></div>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com0tag:blogger.com,1999:blog-8807805591547673516.post-34490174700248413232019-02-02T14:55:00.000-08:002019-02-02T14:58:43.717-08:00yogi_Conditionally Format A:B If There is Number In Column B And Format Column B For "Ja" and "Nein"<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: "times new roman";">
<span dir="auto" style="border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="color: blue; font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><i>Google Spreadsheet Post #2585</i></span></span></div>
<div style="color: #222222; font-family: "times new roman";">
<span style="color: #222222; font-family: "times new roman"; font-size: small; font-style: italic;"></span><br /></div>
<div>
<div style="color: #222222; font-family: "times new roman";">
<i><span style="color: blue;"><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Yogi Anand, D.Eng, P.E.</span><span style="font-family: "times" , "times new roman" , serif; font-size: small; text-align: center;"> ANAND Enterprises LLC -- Rochester Hills MI</span></span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><span style="color: blue;"> </span> </span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"> </span><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Feb-02-2019</span></i></div>
<div>
<span style="color: #222222; font-family: "times new roman";">question by: MACK</span><span style="background-color: white; color: #999999; font-family: "roboto" , "arial" , sans-serif; font-size: 14px; white-space: nowrap;">:</span><br />
<div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;">https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/Pd249NVcV8Y;context-place=forum/docs</u><br />
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;"><span style="background-color: white; color: rgba(0 , 0 , 0 , 0.85); font-family: "roboto" , "arial" , sans-serif; font-size: 17px; white-space: normal;">Text zwischen zwei leeren Zellen Zählen</span></u><br />
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;"><span style="background-color: white; color: #222222; font-family: "roboto" , "arial" , sans-serif; font-size: 13px; white-space: normal;">Hallo Helfer und Helferinnen,</span></u><br />
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
bin auf der suche nach einer Lösung damit in einem Tabellen-Sheet, wo in <b>Spalte B</b> entweder "<i><b>Ja</b></i>" oder "<b><i>Nein</i></b>" steht und in <b>Spalte A </b>das <b><i>Datum</i></b>, wobei die gesamte Zeile wo das Datum steht leer ist.</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
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 "<i><b>Ja</b></i>" zählt und das Ergebnis jedes Tagesabschnittes in der leeren Zelle in Spalte B anzeigt.</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
Hoffe die Frage ist verständlich geschrieben :)</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
Danke schon jetzt für Eure Hilfe</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;"></u><br />
<div style="background-color: white; border: 0px; clear: both; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; padding: 0px; text-align: center;">
<img alt="Sheet.JPG" height="320" src="https://groups.google.com/a/googleproductforums.com/group/docs/attach/f93b8f906898c/Sheet.JPG?part=0.1&view=1&authuser=0" style="border: 0px; margin: 0px 1em; padding: 0px;" width="199" />das sollte so aussehen <a href="https://www.blogger.com/null" style="border: 0px; color: #1155cc; cursor: pointer; margin: 0px 1em; padding: 0px;"><img alt="Smiley_Danke.jpg" border="0" height="153" src="https://groups.google.com/a/googleproductforums.com/group/docs/attach/f93b8f906898c/Smiley_Danke.jpg?part=0.2&view=1&authuser=0" style="border: 0px; margin: 0px; padding: 0px;" width="200" /></a></div>
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;"><span style="background-color: white; color: rgba(0 , 0 , 0 , 0.85); font-family: "roboto" , "arial" , sans-serif; font-size: 17px; white-space: normal;"><br /></span></u>
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;"><br /></u></div>
</div>
</div>
</div>
</div>
</div>
<iframe height="750" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vQgmtMvbbz1w3j9Wd8UQMahDyf5_W70BBB2aHDM3XOOTFb74e7yXmWvsWYojxYA1DLMx0lLPkGM-4P1/pubhtml?widget=true&headers=true" width="836"></iframe></div>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com0tag:blogger.com,1999:blog-8807805591547673516.post-65815406507471345202019-02-02T14:05:00.000-08:002019-02-02T14:05:00.027-08:00yogi_Conditionally Format Column B If Date Is Less_Than_7_days Less Than 1 Month Or More Than 1 Month Away<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: "times new roman";">
<span dir="auto" style="border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="color: blue; font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><i>Google Spreadsheet Post #2584</i></span></span></div>
<div style="color: #222222; font-family: "times new roman";">
<span style="color: #222222; font-family: "times new roman"; font-size: small; font-style: italic;"></span><br /></div>
<div>
<div style="color: #222222; font-family: "times new roman";">
<i><span style="color: blue;"><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Yogi Anand, D.Eng, P.E.</span><span style="font-family: "times" , "times new roman" , serif; font-size: small; text-align: center;"> ANAND Enterprises LLC -- Rochester Hills MI</span></span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><span style="color: blue;"> </span> </span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"> </span><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Feb-02-2019</span></i></div>
<div>
<span style="color: #222222; font-family: "times new roman";">question by: Laurence Browne</span><span style="background-color: white; color: #999999; font-family: "roboto" , "arial" , sans-serif; font-size: 14px; white-space: nowrap;">:</span><br />
<div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;">https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/wzFydy5DY5c;context-place=forum/docs</u><br />
<span style="background-color: white; color: rgba(0, 0, 0, 0.85); font-family: Roboto, Arial, sans-serif; font-size: 17px;">Conditional format - Robot system for due dates</span></div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="background-color: white; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px;">Hi </span><div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
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</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
Red - 1 week away</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
Amber - 1 month away</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
Green - over 1 month away</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
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. </div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
Thank you for any help you can provide.</div>
</div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="background-color: white; color: rgba(0, 0, 0, 0.85); font-family: Roboto, Arial, sans-serif; font-size: 17px;"><br /></span></div>
</div>
</div>
</div>
</div>
</div>
<iframe height="600" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vSVWiQ3GAl7sZ3PYPw_Gnh7VHpxMCvz0GXbnVU4iMe3sSfNJ44_llnhUYN0zU3nEjjvoHCKXibqGNrA/pubhtml?widget=true&headers=true" width="836"></iframe></div>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com0tag:blogger.com,1999:blog-8807805591547673516.post-28416434072987899252019-02-02T10:04:00.000-08:002019-02-02T10:04:11.393-08:00yogi_For The Entities In Column B Pull The Latest Instance Of Entity And The Amount In Column C<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: "times new roman";">
<span dir="auto" style="border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="color: blue; font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><i>Google Spreadsheet Post #2583</i></span></span></div>
<div style="color: #222222; font-family: "times new roman";">
<span style="color: #222222; font-family: "times new roman"; font-size: small; font-style: italic;"></span><br /></div>
<div>
<div style="color: #222222; font-family: "times new roman";">
<i><span style="color: blue;"><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Yogi Anand, D.Eng, P.E.</span><span style="font-family: "times" , "times new roman" , serif; font-size: small; text-align: center;"> ANAND Enterprises LLC -- Rochester Hills MI</span></span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><span style="color: blue;"> </span> </span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"> </span><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Feb-02-2019</span></i></div>
<div>
<span style="color: #222222; font-family: "times new roman";">question by: Harristweeter</span><span style="background-color: white; color: #999999; font-family: "roboto" , "arial" , sans-serif; font-size: 14px; white-space: nowrap;">:</span><br />
<div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;">https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/Bvu4QmTcxL8;context-place=forum/docs</u><br />
<h1 class="BOXPGHD-C-zb" dir="ltr" style="background-color: white; border: 0px; color: rgba(0, 0, 0, 0.85); display: inline-block; font-family: Roboto, Arial, sans-serif; font-size: 17px; font-weight: 500; line-height: normal; margin: 0px; max-height: 10000px; padding: 0px; vertical-align: middle;">
Find Most Recent Entry for a Given Category, and Return Third Column Information</h1>
</div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="background-color: white; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px;">Good Morning,</span><div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
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.</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
For Example the table may look like this:</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<span style="background-color: #cccccc; border: 0px; margin: 0px; padding: 0px;"> A B C D</span></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<span style="border: 0px; margin: 0px; padding: 0px;">12/2/18 Bank A #123456 $101.50</span></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<span style="border: 0px; margin: 0px; padding: 0px;">12/5/18 Bank B #789101 $8,890.02</span></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<span style="border: 0px; margin: 0px; padding: 0px;">12/10/18 Bank C #121314 $0.01</span></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
01/10/19 Bank A #123456 $10.78<span style="border: 0px; margin: 0px; padding: 0px;"><br /></span></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
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).</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
I'm stumped. Any advice?</div>
</div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="color: rgba(0, 0, 0, 0.850980392156863); font-family: Roboto, Arial, sans-serif;"><span style="font-size: 17px;"><br /></span></span></div>
</div>
</div>
</div>
</div>
</div>
<iframe height="500" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vQGpQaktIZvGyiLDguLN6lHYFdek2b7-7EGw-19UFJ8gvsxiluUh1_WVKwlRXSNFvQqw_IhSBwqeNGZ/pubhtml?widget=true&headers=true" width="836"></iframe></div>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com0tag:blogger.com,1999:blog-8807805591547673516.post-86572016889570325932019-01-30T09:37:00.000-08:002019-01-30T09:37:17.926-08:00yogi_WorkAround For How To Lookup Values Across Multiple Worksheets To Return Sheet Names <div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: "times new roman";">
<span dir="auto" style="border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="color: blue; font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><i>Google Spreadsheet Post #2582</i></span></span></div>
<div style="color: #222222; font-family: "times new roman";">
<span style="color: #222222; font-family: "times new roman"; font-size: small; font-style: italic;"></span><br /></div>
<div>
<div style="color: #222222; font-family: "times new roman";">
<i><span style="color: blue;"><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Yogi Anand, D.Eng, P.E.</span><span style="font-family: "times" , "times new roman" , serif; font-size: small; text-align: center;"> ANAND Enterprises LLC -- Rochester Hills MI</span></span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><span style="color: blue;"> </span> </span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"> </span><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Jan-30-2019</span></i></div>
<div>
<span style="color: #222222; font-family: "times new roman";">question by: Erin Sublette</span><span style="background-color: white; color: #999999; font-family: "roboto" , "arial" , sans-serif; font-size: 14px; white-space: nowrap;">:</span><br />
<div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;">https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/y8kWoymOxRA;context-place=forum/docs</u><br />
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;"><span style="background-color: white; color: rgba(0, 0, 0, 0.85); font-family: Roboto, Arial, sans-serif; font-size: 17px; white-space: normal;">How To Vlookup Values Across Multiple Worksheets To Return Sheet Names</span></u><br />
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;"><span style="background-color: white; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; white-space: normal;">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. </span></u><br />
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
On the Employee Info tab, I would like column B to reflect the sheet name (department) those workers are in. </div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;"></u><br />
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<a href="https://docs.google.com/spreadsheets/d/1GjZD9xg0ee2wRvag5E2_qCaTms4vZDQPH2NfTzb2rFU/edit?usp=sharing" rel="nofollow" style="border: 0px; color: #6611cc; cursor: pointer; margin: 0px; padding: 0px; text-decoration-line: none;" target="_blank">https://docs.google.com/<wbr></wbr>spreadsheets/d/<wbr></wbr>1GjZD9xg0ee2wRvag5E2_<wbr></wbr>qCaTms4vZDQPH2NfTzb2rFU/edit?<wbr></wbr>usp=sharing</a></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
</div>
</div>
</div>
</div>
</div>
</div>
<iframe height="650" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vRNIdiA44uMjD8RMJDaAQqK-JMq6h1Z83oi3in7lS-mYi9guaevgCJ6xMGsj9icHa8yUWA5tpXqgeMf/pubhtml?widget=true&headers=true" width="836"></iframe></div>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com0tag:blogger.com,1999:blog-8807805591547673516.post-72669968927541592722019-01-28T19:18:00.001-08:002019-01-28T19:22:29.378-08:00yogi_Conditionally Format Column B If Date is Between 0 to 2 2 to 4 and 4 Months Past <div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: "times new roman";">
<span dir="auto" style="border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="color: blue; font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><i>Google Spreadsheet Post #2581</i></span></span></div>
<div style="color: #222222; font-family: "times new roman";">
<span style="color: #222222; font-family: "times new roman"; font-size: small; font-style: italic;"></span><br /></div>
<div>
<div style="color: #222222; font-family: "times new roman";">
<i><span style="color: blue;"><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Yogi Anand, D.Eng, P.E.</span><span style="font-family: "times" , "times new roman" , serif; font-size: small; text-align: center;"> ANAND Enterprises LLC -- Rochester Hills MI</span></span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><span style="color: blue;"> </span> </span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"> </span><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Jan-28-2019</span></i></div>
<div>
<span style="color: #222222; font-family: "times new roman";">question by: Konrad24</span><span style="background-color: white; color: #999999; font-family: "roboto" , "arial" , sans-serif; font-size: 14px; white-space: nowrap;">:</span><br />
<div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;">https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/y8kWoymOxRA;context-place=forum/docs</u><br />
<h1 class="BOXPGHD-C-zb" dir="ltr" style="background-color: white; border: 0px; color: rgba(0, 0, 0, 0.85); display: inline-block; font-family: Roboto, Arial, sans-serif; font-size: 17px; font-weight: 500; line-height: normal; margin: 0px; max-height: 10000px; padding: 0px; vertical-align: middle; white-space: nowrap;">
Condition Format</h1>
<div class="BOXPGHD-C-W" style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px 68px 0px 0px;">
<div style="border: 0px; margin: 0px; max-height: 10000px; overflow: auto; padding: 0px;">
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<div dir="ltr" style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
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</div>
</div>
</div>
</div>
<div class="BOXPGHD-C-G" style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 15px 0px 0px; max-height: 10000px; padding: 15px 0px 0px;">
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
</div>
<div class="gwt-HTML" style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
</div>
</div>
<div class="BOXPGHD-C-Z" style="align-items: center; background-color: white; border: 0px; color: #222222; display: flex; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px 0px 24px;">
<div class="BOXPGHD-C-Bb" style="border: 0px; flex: 1 1 0%; margin: 0px; max-height: 10000px; min-width: 0px; padding: 0px;">
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<iframe height="800" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vStj4E9VBopMhAiBLsb0AwJHgUAHNdE2GXSS4RkoRr3EhsdB5vFY7KNJnlblDqA7pLQ0PpCYBWSWEDo/pubhtml?widget=true&headers=true" width="836"></iframe></div>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com0tag:blogger.com,1999:blog-8807805591547673516.post-70965325231135402942019-01-23T16:33:00.000-08:002019-01-23T16:33:27.792-08:00yogi_Count Row By Row How Many Numbers In Each Row Are Smaller Than The Numbers In Row Below<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: "times new roman";">
<span dir="auto" style="border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="color: blue; font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><i>Google Spreadsheet Post #2581</i></span></span></div>
<div style="color: #222222; font-family: "times new roman";">
<span style="color: #222222; font-family: "times new roman"; font-size: small; font-style: italic;"></span><br /></div>
<div>
<div style="color: #222222; font-family: "times new roman";">
<i><span style="color: blue;"><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Yogi Anand, D.Eng, P.E.</span><span style="font-family: "times" , "times new roman" , serif; font-size: small; text-align: center;"> ANAND Enterprises LLC -- Rochester Hills MI</span></span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><span style="color: blue;"> </span> </span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"> </span><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Jan-23-2019</span></i></div>
<div>
<span style="color: #222222; font-family: "times new roman";">question by: mooflower</span><span style="background-color: white; color: #999999; font-family: "roboto" , "arial" , sans-serif; font-size: 14px; white-space: nowrap;">:</span><br />
<div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;">https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/GiYRp6j8aGA;context-place=topicsearchin/docs/category$3Ahow-do-i</u><br />
<span style="background-color: white; color: rgba(0, 0, 0, 0.85); font-family: Roboto, Arial, sans-serif; font-size: 17px;">Count number of cells in a range whose value exceeds a cell value located above it</span><br />
<span style="background-color: white; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px;">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.</span><br />
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
Example (comma separated values):</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
4, 5, 4, 3, 4</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
5, 5, 3, 3, 5</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
The number of times the lower range exceeds the value above it: 2 (the first and last entries)</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
Thanks !</div>
<span style="background-color: white; color: rgba(0, 0, 0, 0.85); font-family: Roboto, Arial, sans-serif; font-size: 17px;"><br /></span></div>
</div>
</div>
</div>
</div>
</div>
<iframe height="800" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vT6Fi6sH0bTcysxbL95FXKXwn4Sk2kLYZYXA_hTkrPosBnZnrJCdzsCIElV3nU2_MhiGMECMMyFgEvb/pubhtml?widget=true&headers=true" width="836"></iframe></div>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com0tag:blogger.com,1999:blog-8807805591547673516.post-39950063050147964752019-01-20T14:54:00.000-08:002019-01-24T18:38:27.467-08:00yogi_Compute Row By Row Running Average Of Numbers In Column B<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: "times new roman";">
<span dir="auto" style="border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="color: blue; font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><i>Google Spreadsheet Post #2580</i></span></span></div>
<div style="color: #222222; font-family: "times new roman";">
<span style="color: #222222; font-family: "times new roman"; font-size: small; font-style: italic;"></span><br /></div>
<div>
<div style="color: #222222; font-family: "times new roman";">
<i><span style="color: blue;"><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Yogi Anand, D.Eng, P.E.</span><span style="font-family: "times" , "times new roman" , serif; font-size: small; text-align: center;"> ANAND Enterprises LLC -- Rochester Hills MI</span></span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><span style="color: blue;"> </span> </span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"> </span><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Jan-20-2019</span></i></div>
<div>
<span style="color: #222222; font-family: "times new roman";">question by: Viktor Petrovich</span><span style="background-color: white; color: #999999; font-family: "roboto" , "arial" , sans-serif; font-size: 14px; white-space: nowrap;">:</span><br />
<div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;">https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/rszAV_rtfwE;context-place=forum/docs</u><br />
<span style="background-color: white; color: rgba(0 , 0 , 0 , 0.85); font-family: "roboto" , "arial" , sans-serif; font-size: 17px;">daily average</span><br />
<div class="BOXPGHD-C-W" style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px 68px 0px 0px;">
<div style="border: 0px; margin: 0px; max-height: 10000px; overflow: auto; padding: 0px;">
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<div dir="ltr" style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
Hi<br />
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
I have a simple sheet and which create new row every day with date (A) and daily amount (B) </div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
Need the formula to put daily average in (C)</div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
appreciate any help with that</div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<a href="https://docs.google.com/spreadsheets/d/1KXOZG9s33_xYn_t0JNfwDHH1w73J0N1I9VjRJwYiuss/edit?usp=sharing" rel="nofollow" style="border: 0px; color: #6611cc; cursor: pointer; margin: 0px; padding: 0px; text-decoration-line: none;" target="_blank">https://docs.google.com/<wbr></wbr>spreadsheets/d/1KXOZG9s33_xYn_<wbr></wbr>t0JNfwDHH1w73J0N1I9VjRJwYiuss/<wbr></wbr>edit?usp=sharing</a></div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
</div>
</div>
</div>
</div>
<div class="BOXPGHD-C-G" style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 15px 0px 0px; max-height: 10000px; padding: 15px 0px 0px;">
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<div __gwtcellbasedwidgetimpldispatchingblur="true" __gwtcellbasedwidgetimpldispatchingfocus="true" class="BOXPGHD-tb-a" style="border: 0px; display: inline-block; margin: 0px; max-height: 10000px; padding: 0px; position: relative;">
<div class="BOXPGHD-kb-c" style="border: 0px; height: 134px; margin: 0px; max-height: 10000px; padding: 15px 35px 15px 0px; width: 229px;">
<div aria-label="Attachment: Clip2net_190120235641.png" class="BOXPGHD-kb-b" data-card-id="0" gwtuirendered="gwt-uid-6965" style="background-color: whitesmoke; border: 1px solid rgb(229, 229, 229); color: #777777; display: inline-block; font-size: 12px; height: 120px; margin: 12px 12px 0px 0px; max-height: 10000px; outline: none; padding: 0px; position: relative; vertical-align: top; width: 180px;" tabindex="0">
<div class="BOXPGHD-kb-g BOXPGHD-kb-m" style="background: rgb(0, 0, 0); border: 0px; color: white; display: inline-block; font-weight: bold; height: 120px; left: 0px; margin: 0px; max-height: 10000px; opacity: 0.4; padding: 0px; position: absolute; top: 0px; width: 180px; z-index: 1;">
</div>
<div class="BOXPGHD-kb-g BOXPGHD-kb-n" style="background: none; border: 0px; color: white; display: inline-block; font-weight: bold; height: 120px; left: 0px; margin: 0px; max-height: 10000px; opacity: 1; padding: 0px; position: absolute; top: 0px; width: 180px; z-index: 1;">
<div class="BOXPGHD-kb-i" style="border: 0px; display: inline-block; margin: 10px 0px 0px; max-height: 10000px; padding: 0px; text-align: center; width: 35px;">
<img class="BOXPGHD-b-J" src="https://ssl.gstatic.com/docs/doclist/images/mediatype/icon_1_image_x16.png" style="border: 0px; height: 16px; margin: 0px; padding: 0px; width: 16px;" /></div>
<div class="BOXPGHD-kb-k" style="border: 0px; display: inline-block; margin: 10px 0px 0px; max-height: 10000px; padding: 0px; vertical-align: top;">
<div class="BOXPGHD-kb-j" style="border: 0px; font-size: 14px; margin: 0px; max-height: 10000px; overflow: hidden; padding: 0px; text-overflow: ellipsis; white-space: nowrap; width: 140px;">
Clip2net_190120235641.png</div>
<div class="BOXPGHD-kb-r" style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
5 KB</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<span style="background-color: white; color: rgba(0 , 0 , 0 , 0.85); font-family: "roboto" , "arial" , sans-serif; font-size: 17px;"><br /></span></div>
</div>
</div>
</div>
</div>
</div>
<iframe height="500" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vQyRcPvWhwG58uZ_ysszgG_5EuCV0PKM8bEt76ofOajVbjOGKCvnVcqlSYSh6CEQ9L6ym4OvdK8AXfI/pubhtml?widget=true&headers=true" width="836"></iframe></div>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com0tag:blogger.com,1999:blog-8807805591547673516.post-10654450565072979202019-01-15T17:58:00.000-08:002019-01-15T17:59:45.529-08:00yogi_Collect Multiple Attributes For Unique Entities<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: "times new roman";">
<span dir="auto" style="border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="color: blue; font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><i>Google Spreadsheet Post #2579</i></span></span></div>
<div style="color: #222222; font-family: "times new roman";">
<span style="color: #222222; font-family: "times new roman"; font-size: small; font-style: italic;"></span><br /></div>
<div>
<div style="color: #222222; font-family: "times new roman";">
<i><span style="color: blue;"><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Yogi Anand, D.Eng, P.E.</span><span style="font-family: "times" , "times new roman" , serif; font-size: small; text-align: center;"> ANAND Enterprises LLC -- Rochester Hills MI</span></span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><span style="color: blue;"> </span> </span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"> </span><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Jan-15-2019</span></i></div>
<div>
<span style="color: #222222; font-family: "times new roman";">question by: Niwrutti Sawant</span><span style="background-color: white; color: #999999; font-family: "roboto" , "arial" , sans-serif; font-size: 14px; white-space: nowrap;">:</span><br />
<div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;">https://www.blogger.com/blogger.g?blogID=8807805591547673516#editor/target=post;postID=1065445056507297920</u><br />
<span style="background-color: white; color: rgba(0 , 0 , 0 , 0.85); font-family: "roboto" , "arial" , sans-serif; font-size: 17px;">Need to collect duplicates with different values</span><br />
<div class="BOXPGHD-C-W" style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px 68px 0px 0px;">
<div style="border: 0px; margin: 0px; max-height: 10000px; overflow: auto; padding: 0px;">
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<div dir="ltr" style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
I want to fetch data from different sheets using importrange and want the values of the same data in one cell as mentioned in the sheet.<br />
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<a href="https://docs.google.com/spreadsheets/d/1GKfeJwUcZrmp1WL0nWEVnIm4SjY9yxbdPpYfwFB-W7E/edit#gid=0" rel="nofollow" style="border: 0px; color: #6611cc; cursor: pointer; margin: 0px; padding: 0px; text-decoration-line: none;" target="_blank">https://docs.google.com/<wbr></wbr>spreadsheets/d/<wbr></wbr>1GKfeJwUcZrmp1WL0nWEVnIm4SjY9y<wbr></wbr>xbdPpYfwFB-W7E/edit#gid=0</a></div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
I have a huge database and I want to club the duplicates and add all their respective values in single cell.</div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
</div>
</div>
</div>
</div>
<div class="BOXPGHD-C-G" style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 15px 0px 0px; max-height: 10000px; padding: 15px 0px 0px;">
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
</div>
</div>
<div class="BOXPGHD-C-Z" style="align-items: center; background-color: white; border: 0px; color: #222222; display: flex; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px 0px 24px;">
<div class="BOXPGHD-C-Bb" style="border: 0px; flex: 1 1 0%; margin: 0px; max-height: 10000px; min-width: 0px; padding: 0px;">
</div>
</div>
<span style="background-color: white; color: rgba(0 , 0 , 0 , 0.85); font-family: "roboto" , "arial" , sans-serif; font-size: 17px;"><br /></span></div>
</div>
</div>
</div>
</div>
</div>
<iframe height="600" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vSEPObcTGns9WTW3TzKxXAHDoemJK7Rysc-v8OZN76jAw2MSPR78RiDsX5ffM8Wyawa2l7Xgj5O7flF/pubhtml?widget=true&headers=true" width="836"></iframe></div>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com0tag:blogger.com,1999:blog-8807805591547673516.post-1897355275316747122019-01-13T17:11:00.000-08:002019-01-13T17:11:11.740-08:00yogi_how to subtract only the decimal amount for multiple purchases per user specification <div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: "times new roman";">
<span dir="auto" style="border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="color: blue; font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><i>Google Spreadsheet Post #2578</i></span></span></div>
<div style="color: #222222; font-family: "times new roman";">
<span style="color: #222222; font-family: "times new roman"; font-size: small; font-style: italic;"></span><br /></div>
<div>
<div style="color: #222222; font-family: "times new roman";">
<i><span style="color: blue;"><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Yogi Anand, D.Eng, P.E.</span><span style="font-family: "times" , "times new roman" , serif; font-size: small; text-align: center;"> ANAND Enterprises LLC -- Rochester Hills MI</span></span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><span style="color: blue;"> </span> </span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"> </span><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Jan-13-2019</span></i></div>
<div>
<br />
<div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="color: #222222; font-family: "times new roman";">question by: mystery82</span><span style="background-color: white; color: #999999; font-family: "roboto" , "arial" , sans-serif; font-size: 14px; white-space: nowrap;">:</span><br />
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;">https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/qCRsQHxs1wc;context-place=forum/docs</u><br />
<span style="background-color: white; color: rgba(0, 0, 0, 0.85); font-family: Roboto, Arial, sans-serif; font-size: 17px;">How do I subtract only the decimal amount?</span><br />
<div class="BOXPGHD-C-W" style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px 68px 0px 0px;">
<div style="border: 0px; margin: 0px; max-height: 10000px; overflow: auto; padding: 0px;">
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<div dir="ltr" style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
I am trying to create a Budget sheet that puts the remaining change back into my available amount.</div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
I have attached a link below that should explain everything I am trying to achieve there are graphics so it may load</div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
slow.</div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
Example:</div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<a href="https://docs.google.com/spreadsheets/d/1xlykW0OWK5gSQ4IMqLqoL8aopW9AVUWoZWLCwnW30_c/edit?usp=sharing" rel="nofollow" style="border: 0px; color: #6611cc; cursor: pointer; margin: 0px; padding: 0px; text-decoration-line: none;" target="_blank">https://docs.google.com/<wbr></wbr>spreadsheets/d/<wbr></wbr>1xlykW0OWK5gSQ4IMqLqoL8aopW9AV<wbr></wbr>UWoZWLCwnW30_c/edit?usp=<wbr></wbr>sharing</a></div>
</div>
</div>
</div>
</div>
<div class="BOXPGHD-C-G" style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 15px 0px 0px; max-height: 10000px; padding: 15px 0px 0px;">
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
</div>
<div class="gwt-HTML" style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
</div>
</div>
<span style="background-color: white; color: rgba(0 , 0 , 0 , 0.85); font-family: "roboto" , "arial" , sans-serif; font-size: 17px;"></span><br />
<div class="BOXPGHD-C-Z" style="align-items: center; background-color: white; border: 0px; color: #222222; display: flex; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px 0px 24px;">
<div class="BOXPGHD-C-Bb" style="border: 0px; flex: 1 1 0%; margin: 0px; max-height: 10000px; min-width: 0px; padding: 0px;">
</div>
</div>
<span style="background-color: white; color: rgba(0 , 0 , 0 , 0.85); font-family: "roboto" , "arial" , sans-serif; font-size: 17px;"><br /></span></div>
</div>
</div>
</div>
</div>
</div>
<iframe height="600" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vR3N_AnIXDo-pU-pTgtK6rlH7Wn7h-9Q9Rwx9g1CtzX3u-AsBLzcb7qXWCBtZ1T5qkA2dwzPjSbbnS6/pubhtml?widget=true&headers=true" width="836"></iframe></div>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com0tag:blogger.com,1999:blog-8807805591547673516.post-60356000761714514252019-01-13T08:18:00.001-08:002019-01-13T08:18:34.394-08:00yogi_Counting number of times a value shares a row with a specific value<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: "times new roman";">
<span dir="auto" style="border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="color: blue; font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><i>Google Spreadsheet Post #2577</i></span></span></div>
<div style="color: #222222; font-family: "times new roman";">
<span style="color: #222222; font-family: "times new roman"; font-size: small; font-style: italic;"></span><br /></div>
<div>
<div style="color: #222222; font-family: "times new roman";">
<i><span style="color: blue;"><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Yogi Anand, D.Eng, P.E.</span><span style="font-family: "times" , "times new roman" , serif; font-size: small; text-align: center;"> ANAND Enterprises LLC -- Rochester Hills MI</span></span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><span style="color: blue;"> </span> </span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"> </span><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Jan-13-2019</span></i></div>
<div>
<br />
<div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="color: #222222; font-family: "times new roman";">question by: Teksura</span><span style="background-color: white; color: #999999; font-family: "roboto" , "arial" , sans-serif; font-size: 14px; white-space: nowrap;">:</span><br />
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;">https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/2dN8LeFhyFY;context-place=mydiscussions</u><br />
<span style="background-color: white; color: rgba(0, 0, 0, 0.85); font-family: Roboto, Arial, sans-serif; font-size: 17px;">Counting number of times a value shares a row with a specific value.</span><br />
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
What I'm trying to do on Front is make C3 hold an ARRAYFORMULA that checks the Run Data sheet for me. I input a Character name in C2, and it should check the sheet and return the number of times the Characters listed in B4:B on Front appare in the same row in the Run Data Sheet.<br /><br />Got the rest of it worked out on my own, but I'm struggling on this one. Not quite sure what I'm doing wrong.</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
Edit: sorry, fixed my sharing.</div>
<span style="background-color: white; color: rgba(0, 0, 0, 0.85); font-family: Roboto, Arial, sans-serif; font-size: 17px;"><br /></span>
<span style="background-color: white; color: rgba(0, 0, 0, 0.85); font-family: Roboto, Arial, sans-serif; font-size: 17px;"><br /></span></div>
</div>
</div>
</div>
</div>
</div>
<iframe height="700" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vR1gkpSMk0xBsCyMJObyXt3xBV8Q0cpCdigb2HpnqNZFI9IqqBvtr7ip3O0-7pJEQf1K5xYuLklPIDK/pubhtml?widget=true&headers=true" width="836"></iframe></div>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com0tag:blogger.com,1999:blog-8807805591547673516.post-81166024053917769452019-01-12T17:39:00.000-08:002019-01-12T17:39:13.577-08:00yogi_Using Alternative QUERY And FILTER Functions With Criteria As 'In-line strings' and 'Cell references (from dropDowns)'<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: "times new roman";">
<span dir="auto" style="border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="color: blue; font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><i>Google Spreadsheet Post #2576</i></span></span></div>
<div style="color: #222222; font-family: "times new roman";">
<span style="color: #222222; font-family: "times new roman"; font-size: small; font-style: italic;"></span><br /></div>
<div>
<div style="color: #222222; font-family: "times new roman";">
<i><span style="color: blue;"><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Yogi Anand, D.Eng, P.E.</span><span style="font-family: "times" , "times new roman" , serif; font-size: small; text-align: center;"> ANAND Enterprises LLC -- Rochester Hills MI</span></span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><span style="color: blue;"> </span> </span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"> </span><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Jan-12-2019</span></i></div>
<div>
<br />
<div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="color: #222222; font-family: "times new roman";">question by: Adner Colon</span><span style="background-color: white; color: #999999; font-family: "roboto" , "arial" , sans-serif; font-size: 14px; white-space: nowrap;">:</span><br />
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;">https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/6U0oGfKCsQg;context-place=mydiscussions</u><br />
<span style="background-color: white; color: rgba(0, 0, 0, 0.85); font-family: Roboto, Arial, sans-serif; font-size: 17px;">Hi I need help with if and function in Google Sheets</span><br />
<span style="background-color: white; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px;">Hi,</span><br />
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
I hope this question finds you all well. I am trying to compare two columns to create a list. However, the two columns may have a different response. For example, in the D column, we may have either three answers; "Yes, I can," "Sometimes, I can work," or "No, I"m not available. The same for column K where we may have either three responses; "I'm nice with it," "I'm alright," or "I' need help." Column C is the name of the workers.</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
I am trying to use the "if" and "and" function to compare the two columns. The formulas, I have tried are as follow:</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
=if({d2="Yes, I can",d2="Sometimes, I can work",K2="I'm nice with it",K2="I"m alright",C2,"")</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
it works, but it gives me workers who not available to work at a certain but who is I'm nice with it.</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
=if(and({d2="Yes, I can",d2="Sometimes, I can work",K2="I'm nice with it",K2="I"m alright"),C2,"")</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
but I keep getting errors.</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
My goal is if D have either "Yes, I can" or "Sometimes I can work" AND if K="I'm nice with it" or "I'm alright" then enter the name in column C., Please advise.</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
Thank you,</div>
<div style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px;">
Adner</div>
<span style="background-color: white; color: rgba(0, 0, 0, 0.85); font-family: Roboto, Arial, sans-serif; font-size: 17px;"><br /></span>
<span style="background-color: white; color: rgba(0, 0, 0, 0.85); font-family: Roboto, Arial, sans-serif; font-size: 17px;"><br /></span></div>
</div>
</div>
</div>
</div>
</div>
<iframe height="900" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vTzdGF625csrIoBvP-yK5CyHN7S6oHx12CqnqZghY5BxcxM929VAfU_zsigS0DhG3OpUv-oSEz5laO9/pubhtml?widget=true&headers=true" width="836"></iframe></div>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com0tag:blogger.com,1999:blog-8807805591547673516.post-75323577623930382062019-01-11T13:07:00.000-08:002019-01-11T13:07:22.735-08:00yogi_Pull Data From Another Tab Based On Any Combination Of Three DropDowns<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: "times new roman";">
<span dir="auto" style="border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="color: blue; font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><i>Google Spreadsheet Post #2575</i></span></span></div>
<div style="color: #222222; font-family: "times new roman";">
<span style="color: #222222; font-family: "times new roman"; font-size: small; font-style: italic;"></span><br /></div>
<div>
<div style="color: #222222; font-family: "times new roman";">
<i><span style="color: blue;"><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Yogi Anand, D.Eng, P.E.</span><span style="font-family: "times" , "times new roman" , serif; font-size: small; text-align: center;"> ANAND Enterprises LLC -- Rochester Hills MI</span></span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><span style="color: blue;"> </span> </span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"> </span><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Jan-11-2019</span></i></div>
<div>
<br />
<div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="color: #222222; font-family: "times new roman";">question by: Kenny Buckles</span><span style="background-color: white; color: #999999; font-family: "roboto" , "arial" , sans-serif; font-size: 14px; white-space: nowrap;">:</span><br />
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;">https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/EZ6OBGDrjPI;context-place=mydiscussions</u><br />
<span style="background-color: white; color: rgba(0 , 0 , 0 , 0.85); font-family: "roboto" , "arial" , sans-serif; font-size: 17px;">AVERAGEIFS Formula with Data Validation Field Criteria</span><br />
<div class="BOXPGHD-C-W" style="background-color: white; border: 0px; color: #222222; font-family: roboto, arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px 68px 0px 0px;">
<div style="border: 0px; margin: 0px; max-height: 10000px; overflow: auto; padding: 0px;">
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<div dir="ltr" style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
Is it possible to have an AVERAGEIFS formula where the criteria is pulled from a data validation field. But have the formula ignore if one of the criteria is blank?<br />
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
For example - I have Criteria A, Criteria B, Criteria C and in my formula I want to pull the average from a range if it's just Criteria A, or if it's Criteria A+B, or if it's Criteria B+C. I'm basically trying to provide filtering using data validation for a chart.</div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
Thanks.</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<iframe height="700" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vSofjFby1j2q9PX_IA_buv-h2oNcJEBXT3-VjaTDXWrBcUrL-yHAqy49WBB0TNYda_Nb26w16H53O8v/pubhtml?widget=true&headers=true" width="836"></iframe></div>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com0tag:blogger.com,1999:blog-8807805591547673516.post-72565382997798102562019-01-10T14:25:00.000-08:002019-01-10T14:25:32.350-08:00yogi_Pull Data From Another Tab Based On Combination Of Two Out Of Three DropDowns<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: "times new roman";">
<span dir="auto" style="border: 0px; color: black; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="color: blue; font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><i>Google Spreadsheet Post #2574</i></span></span></div>
<div style="color: #222222; font-family: "times new roman";">
<span style="color: #222222; font-family: "times new roman"; font-size: small; font-style: italic;"></span><br /></div>
<div>
<div style="color: #222222; font-family: "times new roman";">
<i><span style="color: blue;"><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Yogi Anand, D.Eng, P.E.</span><span style="font-family: "times" , "times new roman" , serif; font-size: small; text-align: center;"> ANAND Enterprises LLC -- Rochester Hills MI</span></span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"><span style="color: blue;"> </span> </span><span class="Apple-style-span" style="font-family: "times" , "times new roman" , serif; font-size: x-large; text-align: center;"> </span><span style="font-family: "times" , "times new roman" , serif; text-align: center;">Jan-10-2019</span></i></div>
<div>
<br />
<div>
<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="color: #222222; font-family: "times new roman";">question by: Kenny Buckles</span><span style="background-color: white; color: #999999; font-family: "roboto" , "arial" , sans-serif; font-size: 14px; white-space: nowrap;">:</span><br />
<u style="color: #0000ee; font-family: roboto, arial, sans-serif; font-size: 14px; white-space: nowrap;">https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/EZ6OBGDrjPI;context-place=mydiscussions</u><br />
<span style="background-color: white; color: rgba(0, 0, 0, 0.85); font-family: Roboto, Arial, sans-serif; font-size: 17px;">AVERAGEIFS Formula with Data Validation Field Criteria</span><br />
<div class="BOXPGHD-C-W" style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px 68px 0px 0px;">
<div style="border: 0px; margin: 0px; max-height: 10000px; overflow: auto; padding: 0px;">
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<div dir="ltr" style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
Is it possible to have an AVERAGEIFS formula where the criteria is pulled from a data validation field. But have the formula ignore if one of the criteria is blank?<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
For example - I have Criteria A, Criteria B, Criteria C and in my formula I want to pull the average from a range if it's just Criteria A, or if it's Criteria A+B, or if it's Criteria B+C. I'm basically trying to provide filtering using data validation for a chart.</div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
<br /></div>
<div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
Thanks.</div>
</div>
</div>
</div>
</div>
<div class="BOXPGHD-C-G" style="background-color: white; border: 0px; color: #222222; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 15px 0px 0px; max-height: 10000px; padding: 15px 0px 0px;">
<span style="color: rgba(0, 0, 0, 0.85); font-size: 17px;">-----------------------------------------------------------------------------------------------------------</span><div style="border: 0px; margin: 0px; max-height: 10000px; padding: 0px;">
</div>
</div>
<div class="BOXPGHD-C-Z" style="align-items: center; background-color: white; border: 0px; color: #222222; display: flex; font-family: Roboto, Arial, sans-serif; font-size: 13px; margin: 0px; max-height: 10000px; padding: 0px 0px 24px;">
<div class="BOXPGHD-C-Bb" style="border: 0px; flex: 1 1 0%; margin: 0px; max-height: 10000px; min-width: 0px; padding: 0px;">
</div>
</div>
<span style="background-color: white; font-family: Roboto, Arial, sans-serif; font-size: 17px;"><span style="color: red;">I am not sure how AVERAGEIFs figures in this -- however following is my proposed solution</span></span><br />
<span style="background-color: white; color: rgba(0, 0, 0, 0.85); font-family: Roboto, Arial, sans-serif; font-size: 17px;"><br /></span></div>
</div>
</div>
</div>
</div>
</div>
<iframe height="700" src="https://docs.google.com/spreadsheets/d/e/2PACX-1vShpgoGtyLQ3jMqjpLSkVEDBcWtEk3Fi0tmh4RgsZ3wV_-BU6h0T8UU9ORArjjiQUdEWy3MJnUNnhmx/pubhtml?widget=true&headers=true" width="836"></iframe></div>
Yogi Anandhttp://www.blogger.com/profile/10862524957172908621noreply@blogger.com0