yogi_Conditionally Format Cells In C2 To C If FollowUp Date Is Blank And It Has Been More Than 30 Days Since Appointment Date

                 Google Spreadsheet   Post  #1812

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

post by  Si Leydon:

(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/kWXqDRJ2Di8)

Hi, I need a cell on google sheets to highlight red if a date hasn't been entered in it within 30 days of the date in another cell. Can you tell me how to do this please?

Thanks for your help.

---

https://docs.google.com/spreadsheets/d/1GexCXCIz0HRYeSPEZCxBkFFgPVWSF3YWM1ndEfs9-ps/edit?usp=sharing

This is the link to the dummy sheet, basically i want the follow up appointment field to highlight red when it is blank and 30 days after the date in the original appointment. Once a date is entered in the follow up box I then want it go back to the original colour. Is that possible?

Thanks again for your help.

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

yogi_Compute Stats Of People Invited To Party And The Guests They Brought Along

                 Google Spreadsheet   Post  #1811

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

post by  Michael Bolton:

(https://productforums.google.com/forum/#!mydiscussions/docs/0x93AGetsx4)
Guest Attendance for monthly group dinner
I coordinate a monthly singles group dinner on the first Thursday of every month. I would like to have a spreadsheet the shows the name/s of the persons attending along with any guests they may bring (up to 3) I would like to be able to print a name badge with a bar code on a label for each person attending. I would then scan their label when they arrive to indicate that they attended. Scanning would populate the spreadsheet. I have a bar code scanner that works on my laptop or perhaps I could use my cell phone.. I would like a column for the date and time they arrived or if they were a no show. I would like a total for the amount of guests  that attended and also a total for the no-shows. The scenario is this: The guests RSVP for the dinner up to 2 days before the event and I add them to the spreadsheet. On the morning of the event I print a name tag with a barcode for each person that RSVP'd. At the beginning of the event I check in each gust as they arrive and dive them their name tag at the same time scanning it. The spreadsheet populates the information. Can anyone help me,It would make my job checking them in so much easier and faster. Thanking you in advance for you help and cooperation. Mikey  
PS: Each person will have a permanent bar code so that the following month I could just scan the bar code and it will print a new name tag.Would I need to set up a data base for that ?? I will also need advise on how to do that if needed.. 
---
Hi Yogi, link to spreedsheet
https://docs.google.com/spreadsheets/d/1uza99_YhZnV9ibCCjzuhhUpE51iXzs2wLlLsUsBv2n4/pubhtml

The first column is the barcode ID that I will assign to each member, I will scan their bar code on the name badge I have prepared for them. Once column A is populated with their ID # I would like Column B, F G and H to populate automatically. I would like to have space for 60 attendees. The other 2 items which will update automatically is the Total number of guests attending B+C+D+E providing they are marked "y" in column I. Then a total of no shows which would be the sum of "n"'s in column I. I don't know if I would need additional sheets for the data to be stored as I will be using this each month and would like to save ID #'s and names, email address of all members.to be used again.
Thanks, Mikey
---
https://docs.google.com/spreadsheets/d/1uza99_YhZnV9ibCCjzuhhUpE51iXzs2wLlLsUsBv2n4/edit?usp=sharing
--------------------------------------------------------------------------------------------------------------------------------------------

yogi_Convert Date in MM/DD/YYYY Style to DD/MM/YYYY Style

                 Google Spreadsheet   Post  #1810

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

post by  tidoko:

(https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!mydiscussions/docs/zSxgl70lFq8)
Formula to change day, month, year order
Hi Folks,

I currently have a list of dates: 10/23/2014 (month/day/year). I'd like to change it to be (day/month/year), is there a formula to do so? And I tried to edit the cell date format, but the date is a result from an existing formula, so it does not do anything. 

---

Thanks Yogi, do you work for google? I see your answers here often, and they're quite good. The formula you offered "TEXT(<date>,"dd/mm/yyyy")" works but it does not switch the day and month. Is seems to simply convert the existing cell into text. 

I'm using the older google sheets if that matters. The cell which I'd like to switch the date order on is polled by the following formula

(1) =arrayformula(if(LEN(mbnaRAW!A$2:A)>0;mbnaRAW!A$2:A;""))

(2) This results in the following cell date "10/23/2014"

(3) When trying to shift the day and month via "=TEXT(<date>,"dd/mm/yyyy")" is swill shows "10/23/2014"

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

yogi_Count The Number Of Times A Title is Submitted Accepted Rejected

                 Google Spreadsheet   Post  #1809

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

post by  Zac622:

(https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!category-topic/docs/spreadsheets/vz_TPOzvbO0)
I have values that are either accepted, denied or pending. How do I count each?

I know next to nothing about google spreadsheets or excel so I'm learning as much as I can. Here is my problem.

Background: I'm a creative writer and I want to track where my pieces get submitted to, how many times they get submitted, how many times they are accepted/rejected and when. I have most of that working for me but...

Right now I have it so when I enter the title of something, it adds it to a list and starts counting how many times that title occurs which works great.

However, I need to manually enter when a piece of mine is accepted or rejected, and until I enter one of those, it automatically puts it as pending and starts counting how long it has been since I sent something in. The problem I have is that I want to be able to see in my list how many times something is accepted AND rejected AND how many are currently pending.

I simply cannot figure this out. The problem comes within the fact that the data I enter for new titles will be in random locations. Can someone help please I've wracked my brain for three days.

https://docs.google.com/spreadsheets/d/1iAuR9ikaXRP9IUAfL3vVKOPeV2Wy8oF5ehNPgM353Z8/edit?usp=sharing

Thank you.

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

yogi_Make Used Choices Unavailable In Subsequent DropDown List

                 Google Spreadsheet   Post  #1808

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

post by  KenjiMac

(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/69v0fGnrevI)
Hide Selected Items from subsequent Drop Down Lists

I need some help to create a voting ballot. I am a long way from an expert with spreadsheets.

We have 10 products. We want to vote to reduce it to 6.

Here is a rough overview:

I want to have 6 question positions where users select from the list of 10.

In question 1, 10 choices.

In question 2, 9 choices (previous choice hidden from choice)

In question 3, 8 (since previous two choices are subracted.

I have created the products as a list on second sheet.

I have created a drop down using Data > Validation > List from range.

I found this for doing it in excel. If it is similar, can someone provide an simple how-to.

http://www.contextures.com/xlDataVal03.html

Mahalo for your assistance.

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

yogi_Row By Row Sum Of Points In Range E2 Through K

                 Google Spreadsheet   Post  #1807

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

post by  roddyt:

(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/4tJy7SN7QH8)
Equation errors in duplicate sheets
I am having major issues dealing with a spreadsheet to help me grade my Fantasy Football equations for my 8th grade classes. 
 I have the students enter the stats via form, then I enter the formula so that the spreadsheet will determine the amount of points the students should have (saves me from having to compute each equation 100s of times).
My problem erupts when I duplicate the sheet, then clear the previous week's stats so that I can look at one week at a time.  Below is a picture of what happens when students begin entering stats for the next week.  If I delete the info from the columns, then go back and copy the formulas from the duplicate page, it works until someone new enters data.
What am I doing wrong?  I do not want to resize columns, write new formulas, and do a new form each week to separate the data for my student competition.
I know this is a novice mistake, but I would appreciate any help.
Thanks!
TR

PS - Ref Says Error: Array result was not expanded because it would overwrite data in E52.
----------------------------------------------------------------------------------------------------------------

yogi_From List Of All Students Pull Names Of Those Who Are Absent (Have Not Signed Out)

yogi_Conditionally Format Cells In Range B3 To AF To Have Black BackGround If Entry In Row Of Following Column Houses Have

                 Google Spreadsheet   Post  #1805

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

post by  Arkelsa Trager:

(https://productforums.google.com/forum/#!mydiscussions/docs/P1MJz6YgSOs)
Help Please, I don't understand formulas!

Ok, So, I am using google docs, spreadsheet, and I never did understand formulas very well.

What I would like to do, is have a drop down menu in Column C with 2 Options, Have and Want.

I would Coumns B and A to change Font and Background Based on the value of Have or Want in Column C.

There are a total of 70 rows, and the columns go into AG, If possible, I would like to replicate the formula to be used every 3 columns, only changing the value of the first 2 columns before it, So A/B are effected by C, D/E are affected by F, G/H are affected by I, and so forth

---

What I am trying to accomplish is, with the drop down menus in column C, there are 2 options, Have and Need

I would like to change the formatting of Column B, when Have or Need is chosen.

When Have is Chosen, I would like Column B to be blacked out (Black text on a Black Background), When Need is Chosen, I would like it to display how it does now, Black text on White Background.

I have a feeling it'll just be easier and faster (for me) to just do it manually LOL...

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

in the following solution applying Conditional Formatting formula to a single cell B3 can format the entire range B3 To AF3

yogi_Compute Stats For Weekly HIGHs And Weekly LOWs For Specified TICKERs

                 Google Spreadsheet   Post  #1804

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

post by  Solarmatt:

(https://productforums.google.com/forum/#!mydiscussions/docs/8knFh81-3OM)
Working with aggregating functions and GOOGLEFINANCE function

Firstly, where can I find exhaustive instructions on how to work with ARRAYFUNCTION and other array functions? The canned Google instructions gives only one example about creating hyperlinks. I want to do math!

Second, I have a specific "how-to" question but want to learn in general. I have a spreadsheet (https://docs.google.com/spreadsheets/d/1MAUcUM6nhW9naOVDwXaPWGP1SAFMKWpel4K-xrUY6WA/edit?usp=sharing)

where I want to add a column that returns a "TRUE" if the trading range (or high - low) of the stick listed in column A is greater than any of the weekly trading ranges since the date in column P.

All the data can come from: GOOGLEFINANCE(A3, "high", P3, TODAY(), "weekly") and GOOGLEFINANCE(A3, "low", P3, TODAY(), "weekly"), or GOOGLEFINANCE(A3, "all", P3, TODAY(), "weekly")

But I do not know how to perform operations (in this case take the absolute value of the difference between low & high) across two columns in that [unseen] array and then compare the recent calculated value with the history of calculated values.

Thank you.

---

a) I want to add a column that returns a "TRUE" if this week's trading range (absolute value of this week's high - this week's low) for the stock whose ticker is found in column A is greater than the trading range of any prior week since the date found in column P.

b) All of the cells in a new column. Each row's value would be based on the stock from column A and date from column P.

c) Please see either my attached screenshot and/or the second page of my linked spreadsheet. This is the manual version of doing what I want done for one stock ("AAPL") from 8/1/2014 to today. If this week's range would have been larger than any of the other's in the array then "TRUE" is returned.

Attachments (1)

Screen Shot 2014-10-23 at 5.03.15 PM.png

61 KB   View   Download

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

yogi_Compute Time Worked By Each Employee From Time Sheet Of In And Out Hours

                 Google Spreadsheet   Post  #1803

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

post by  Jimmy DeBlasio1:

(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/how-do-i/chrome-os/chrome-browser/6XPNVYzMXH0)
Calculate Time Worked

Hi there, I'm trying to calculate hours worked per week by each employee. Our pay cycles our Wednesday - Tuesday. 

Sample data can be found here a long with my desired result. 

https://docs.google.com/spreadsheets/d/1tqmnu0OIwaLrupL_exvwQ2E7ICmF7yw5sxy0J1oq9uo/edit?usp=sharing

Thanks! 

Jimmy 

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

yogi_Conditionally Format Cells A5 To W23 -- Color BLUE If Cell Is Greater Than Or Equal To Z3 And Color RED If Cell Is Also Greater Than Or Equal To AA3

                 Google Spreadsheet   Post  #1802

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

post by  Baheru Mengistu:

(https://productforums.google.com/forum/#!mydiscussions/docs/9SuoUiiRSQg)

I'm having a little trouble using the Conditional Formatting.  I have an array of numbers over several columns, and depending on their value, I'd like the font color to be blue or red.  Specifically, there will be two cells elsewhere in the spreadsheet that will have varying numbers (e.g., 16 in cell X1 and 24 in cell X2, or ).  If each of the numbers in columns A through J are between the values of X1 and X2, they should be blue.  If they're equal to or higher than the value of X2, they should be red.

Because X1 and X2 are not static numbers, I needed to use two Custom Formulas.  Unfortunately, I can only seem to get one of the conditions to apply at any time.

Any help would be greatly appreciated.

---

Hi Yogi

https://docs.google.com/spreadsheets/d/1siBeXhSDxnOIQiTPaLvVTuZQJNl4yeDCjnoG4Uit-JA/edit?usp=sharing

On the PSS sheet, I need to have the numbers in each cell from A5 to W23 compared to Z3 and AA3.  If each number in the range is equal to or greater than the number in Z3, it should be blue.  However, if it is also equal to or greater than the number in AA3, it should be red instead.

The numbers in Z3 and AA3 are the result of a VLOOKUP from sheet DCL, based upon the input in cell A1.  Essentially, as A1 changes, Z3 and AA3 change, and consequently, so should the colors of the numbers in A5:W23.

I thought that using the following custom formulas over the range of A5:W23 should work:

=A5>=$Z$3 --> Blue font

=A5>=$AA$3 --> Red font

The first one certainly works, but it looks like the second one does not, even when it's the only condition.  I'm not sure why that should be the case.

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

yogi_Pull Unique Vales From Field1 And The Corresponding Value Of Their Latest Instance In Field2

                 Google Spreadsheet   Post  #1801

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

post by  M Umair

(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/lNt13E-jq90)

Google spreadsheet: Retreving only last row from multiple finds against given query

Hi

 

Consider the following table

 

xyz      cancel

abc      active

xyz      active

 

query against xyz will bring

 

xyz      cancel

xyz      active

 

I am only interested in last entry (which is the latest data) and more specifically in state (i.e active)

 

Is there any way to perform this query using google query?

 

Thanks.

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

In the following is presented one way

yogi_Formula In Cells Of Column D For Groups Of Times Between 16th_and_4th_Hour and 4th_and_16th-Hour

                 Google Spreadsheet   Post  #1800

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

post by  James Lynch1023:

(https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!category-topic/docs/spreadsheets/crFDlJJmED8)

How do i get sum total of separate columns based off date/hour? Google sheets

Hello, first off as the question title says i'm trying to find out a way to add up a column, based off hours in another column. 

I write down information on tickets in rows, containing:" (a1)date/(b1)time/(c1)amount" now what i'm trying to do is get from 16:00-4:00 "amounts" summed up through the time, not based on cell identity. 

i want to have a reoccurring 12 hour cell that adds up 4:00-16:00 then from 16:00-4:00. Some 12 hour periods there are less rows then others, is why i'm trying to get a total of amount based of the time periods.

for example:

      A        B          C        D

1  10/19    18:40      150

2  10/19    21:15      132 

3  10/19    23:50      152

4  10/20     1:20       128   d4(This is where I want a function to get the sum of c1-c4, based off 16:00-4:00)

5  10/20     5:30       100

6  10/20    15:55      213   d6(This is where I want a function to get the sum of c5-c6 based off 4:00-16:00)

Sorry for improper used of terminology, would appreciate any answer's or other ways of tracking this.

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

yogi_Convert Text (Quiz Result) To A Number In A Computed Column Next To 'TimeOfDay'

yogi_Formula In Cell I11 For Cells I11 To I31 To Pull Values From Column W In Sheet Items -- Adding Week(s) If Looked Up Value Is A Number

                 Google Spreadsheet   Post  #1799

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

post by  simonjgd:

(https://productforums.google.com/forum/#!mydiscussions/docs/Ktmab8IgN-U)
VLLOOKUP CODE WRONG

This code:

=VLOOKUP(G11,Items!$R$2:$Y$223,6,IF(W2="Y","In Stock",IF(W2=1,"1 Week",IF(W2=2,"2 Weeks",IF(W2=4,"4 Weeks",IF(W2=8,"8 Weeks"))))),false)

gives this:

Error: Wrong number of arguments to VLOOKUP. Expected between 3 and 4 arguments, but got 5 arguments

Can you advise?

Thanks

---

Hi Yogi,

https://docs.google.com/spreadsheets/d/1REORszzugKu6it7nCARa3JoVPAd7CzzkyM2j3MXyTUU/edit?usp=sharing

cell I11.

Using VLOOKUP, I am trying to populate row I with appropriate data from 'Items!W'

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