yogi_Using Query Function When A Item To Be Searched For Contains Quotation Mark

             Google Spreadsheet   Post  #1872

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

post by  ScottKI:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!category-topic/docs/spreadsheets/RCXcfDihHfo
Trouble using QUERY with a cell containing a quotation mark

I'm working on an investing spreadsheet. For part of my XIRR formula, I'm using the QUERY command below. Column C contains the name of the stock. Column A contains the transaction dates.

=query(Transactions!A$2:R,"select A WHERE (B='Buy' OR B='Sell' OR B='Div') AND C contains 'Intel' ")

This formula works well for companies like Intel, Apple, Google, etc. However, if the company name contains a quotation mark, as below, it fails.

=query(Transactions!A$2:R,"select A WHERE (B='Buy' OR B='Sell' OR B='Div') AND C contains 'McDonald's' ")

It also runs into the same problem if I reference its cell. How do I let the spreadsheet know that the quote is part of the text that it is supposed to search for?

Thanks for the help!

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

yogi_Count Of Specified Attribute Choice (frequency of choice from a column) by Gender

             Google Spreadsheet   Post  #1871

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

post by  Amanda Stanciulescu:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!category-topic/docs/spreadsheets/KkvKSmk8nrs
Graphing Frequencies in Google sheets
So I ran a survey and put all my replies into a table. For this questions I want to use to of my results gender and post secondary education. So I want a graph that shows how many females chose uni, etc is there any way to do this???
--------------------------------------------------------------------------------------------------------------------------------------------

after creating a table of  count of Ideal Path Choice by Gender one can chart the data as desired

yogi_Calculate Total Hours Scheduled In Employee Schedule With Text Representing Days Off

             Google Spreadsheet   Post  #1870

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

post by  Joseph Hipp:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!category-topic/docs/spreadsheets/7vKPKqPovkc
Calculate total hours scheduled in Employee schedule w/ text representing days off

Ok, I am trying to create a spread sheet to make my employee schedule on. When I have a requested day off, I want to be able to enter text into the appropriate collum to indicate this. However, this causes an error with my formula.

Also, I tried formatting the appropriate cells to time, but when I enter values, it reverts back to plain text :( I need the time format, because I have some hours which are halves (i.e. 22:30)

Here is what I have, can someone please help me make the formulas work?

https://docs.google.com/spreadsheets/d/1CRZiHtry_IWj1SZu7TIy7IWupBVU3OldIl3Qq9lElmo/edit#gid=0

Thank you in advance.

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

yogi_Array Formula In Cell C37 Based On Values In Cells B37 To B With A Static Header Value In C37 and C38 (From Cell C36)

             Google Spreadsheet   Post  #1869

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

post by  Raivis.:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/83OzEZ7iWwA
Array formula returns same value after refreshing the page

Hi,

I am completely lost on this one.

This is the formula ={"Trade";"Trade";ARRAYFORMULA(if(ISBLANK(B39:B),,VLOOKUP($B39:$B,'Work Scope Item List'!$A4:$N,column('Work Scope Item List'!C2),FALSE)))} entered in C37 and below you can see that after the page refresh it returns the same thing all over again.

BUT, for example, if I go to C44 and delete the contents, then the array formula refreshes with the correct result. Any suggestions?

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

to my knowledge what you are experiencing is not a bug in Google spreadsheet -- it is your formula in cell C37 (and D37:F37) that need fixing ...

presented in the following solution is one way to do what you are trying to accomplish -- note how I have altered your formula ever so slightly to make it do what you want

yogi_Given A Table Containing Dates And States Compute Count By State(s) For Specified Week Numbers

             Google Spreadsheet   Post  #1868

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

post by  Alex Paras:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/DbNOmaDtbhA
Getting Weekly Results in Google Spreadsheet

'm trying to get an agents weekly results. I tried countifs but, it doesn't seem to work. The error I get is "Countifs are of different sizes" 

Link: https://docs.google.com/spreadsheets/d/1OqP4krC2xSAQHuYs8MIwsnNzZTyLa7asvYrQO--sSBw/edit?usp=sharing

My Goal:

Agent1 Summary

                   State1          State2            State3

Week 1        count          count               count

Week 2        count          count               count

Week 3

Week 4

Hope someone can help.

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

yogi_Conditionally Format Cells B2 To B For Names In Column A Matching List of Names In Cell B2 Of Sheet1

             Google Spreadsheet   Post  #1867

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

post by  Travisneedshelp:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/jDfRUj8zZmQ
Conditional "if" function across multiple sheets please help me

Hello All, 

I'm not Excel or Sheet savvy enough to figure out the formula I need to complete this function. Basically I have a list of names separated by commas in
Sheet 1, cell b2. I then need a conditional formatting formula that will search this list of names in sheet 1; and, in sheet 2, insert the word "present" if a specific person's name appears in the list. If not, it should mark "absent." 

To be more clear, 

Names in Sheet 1 cell b2: Bob, Billy, Brandy, Brad, Sarah. 

List of individual names in sheet 2 will appear as such (in individual cells): 

Bob

Billy

Brandy

Brad

Sarah

Then in the cell adjacent to each name I need a formula to reference the list in sheet 1 and apply the present or absent conditional formatting. 

Please please help me. I've tried for hours but can't get anything to work

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

yogi_Compute Stock Price A Specified Number Of Days Ago And The Price Movement over Time

             Google Spreadsheet   Post  #1866

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

post by  Xoron101:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/jDfRUj8zZmQ
Is there a way to choose the price from X days ago?

is there a way to retrieve a historical price for X days ago?

IE, I'd like the close price from 180 days ago (which would change everyday).  I'm trying to calculate the price movement over the past x days.

So the formula I'll be using is [(Today's Price) - (Historical Price from 180 Days ago)] / Today's Price = Price Movement over the past 180 Days.

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

yogi_Sort Entries By An Attribute That Occurs Most Often

             Google Spreadsheet   Post  #1865

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

post by  GregCu:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!category-topic/docs/spreadsheets/7yvIYNMr-E0
how to sort based on the number of times something occurs

I am trying to sort a set of data bassed on how many times a name occurs ex-

Bill   1234

Bill   1333

Joe   1221

greg   1222

greg   1545

greg    1546

so when the sort is finished the data will display like so

greg   1222

greg   1545

greg    1546

Bill   1234

Bill   1333

Joe   1221

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

yogi_Compute Stats For Teams In Column A Based On Attributes In Column B And Counts By Attributes In Column C

             Google Spreadsheet   Post  #1864

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

post by  GregCu:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!mydiscussions/docs/6tx5Kl1L6j0
Calculate the sum of instances in a string expressed in Column B as product of Column C
Reposting using a non-corp account. 

Hi Yogi

Finally figured out why I couldn't share.

Here is a link to the spreadsheet: https://docs.google.com/spreadsheets/d/1da49GMUp5HYzds_1plQUjjqmrGeucHBokeeqlq6-Mr8/edit?usp=sharing

Thanks for your help and patience!

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

yogi_Flag Entries In 'X-Sheet' With X Based On Status Of Items In Sheet Named 'Calls In'

             Google Spreadsheet   Post  #1863

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

post by  Alex DiBlasio:
https://productforums.google.com/forum/#!mydiscussions/docs/SJ8ODDPRE8E
Auto populating from a drop down menu

Hello,

I'm not sure if this request is actually possible, but it would be awesome if someone could help me out.

I've attached the file I'm trying to work with as a reference and so no one is confused.  The way this works now, is we put in a store number into the "Calls In" tab, and then go to the "X-Sheet" tab, put in the same store number, and put an "x" in the corresponding column to what the store called about (the top of the "X-Sheet" is labelled with all issues that a store can call about).

I've just added a drop down menu at the end of the row in the "Calls In" tab, so after we enter the store and the issue, we can choose an issue from the drop down menu (call correspond to the issues at the top of the "X-Sheet").  Basically, what I'm asking, is if it's possible to have the drop down menu linked to the "X-Sheet" tab, so that when an issue is chosen from the drop down menu, an "x" appears in the same column on the "X-Sheet."

For example, 168 is the first store on the list in "Calls In."  If I go to the end of the row and choose "late" from the drop down menu, I would ideally like an "x" to automatically show up in the "X-Sheet" tab.  

I really really hope this hasn't confused anybody!  And would love some help, even if it's just bad news that this is impossible!

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

yogi_Pick For a Specified Attribute From A Table Next Date Later Than Today

             Google Spreadsheet   Post  #1862

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

post by  Dakir Zo:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/bHAxWIiBJd4
How to: Pick Next Date / wie wählt eine funktion das nächste datum

https://docs.google.com/spreadsheets/d/10E4wzL2oyfe8FQGOyjp_w8NV5Owy2kACCamt81P4Dyw/edit?usp=sharing


I want to give "A3" the Command to pick the next date from the table on the right.

I want to "A3" is a formula that seeks out the next appointment from the right table. That is, from today, the next appointment.

Ich will in "A3" eine Formel haben der mir das nächste Datum aus der rechten Tabelle sucht.

Ich will das in "A3" eine Formel steht die aus der rechten tabelle den nächsten Termin raussucht. Das heißt von Heute aus [=NOW()] der nächste Termin.

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

yogi_Conditionally Format Cells In A2 To A Where Number Of Characters Exceed Specified Number

              Google Spreadsheet   Post  #1861

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

post by  Jessica Eckford:
How do I set a character limit for one column on an google spreadsheet?

Hi there-- I am using google spreadsheets to record the content created by multiple writers within my organization. For the title column, I want to limit the characters to 32 to prevent other writers from going over the limit.

Is there any way to do this, and if the character limit is passed- highlight the individual cell?

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

yogi_Pull From Column A Items Beginning With Entry In Cell G3 Quantities In Warehouses Specified In Cell G5

              Google Spreadsheet   Post  #1860

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

post by  Ajit Tejwani:
https://productforums.google.com/forum/#!mydiscussions/docs/a7J0mTBNUK8
Filter Data

Hello

I use following Sheet for my warehouse record

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

I use it frequently How Can I

1.Select Items > 0 For the selected Warehouse.

Example: W1

2.Find Item For particular Warehouse or all

Example : Item Ud For All Warehouse or Particular Warehouse.

Thanks

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

for the first problem one can use the FILTER command to see the results in-situ or one can use the FILTER function tp pull the results in another location

solution to the second problem is presented in the following

yogi_Query Data in 'Master' And present Results By Load_No Lot_No Sale_Date City No_Shipped

              Google Spreadsheet   Post  #1859

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

post by  KyleB73:
(https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!mydiscussions/docs/UbyuSNA6XYU)
Help: Need to Query then Join and Sort Two columns onto new sheet

Spreadsheet needs to track initial inventory that is purchased in lots, then may be shipped to different locations at different times to a final destination. But I need to be able to track that shipment back to the initial purchase. Please help me with necessary formulas. I have master data, the results I've come up with, then desired results attached.

Thanks a ton

Attachments (1)

Inventory.gsheet

221 B   View   Download

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

yogi_Given Columns Of Dates And Prices Rearrage Price1 Price2 Price3 By Date In Ascending Order

              Google Spreadsheet   Post  #1858

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

post by  Tyler Goldberg:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/BdcJ_-_rJ4Y
Combining sets of data with different timelines

I have multiple sets of data that each have two columns, date and price. The problem is there is one data point for only business days and each year has different business days. Here is an example of the data sets:

Date1     Price1     Date2     Price2     Date3    Price3

1/1            5           1/1           9          1/1         15

1/2            6           1/4          10         1/2         14

1/3            6           1/5          11         1/5         13

1/4            7           1/6          12         1/6         14

So after copying and pasting the data sets next to each other, this is how the data looks. The problem is none of the dates line up. How can I sort the data to where they are all on the same timeline? There are going to be data gaps but that isn't a problem.

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