yogi_Use Different Currency Symbols In Google Spreadsheet

                                          Google Spreadsheet   Post  #1226
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 01, 2013
user Rochelle Ray (http://productforums.google.com/forum/?zx=mwi1lx7ihzkq#!category-topic/docs/spreadsheets/hNIgFLpGKvU)
Format currency not on list
I am working on a sheet that includes both Australian and US dollars.  In excel, I could format the US dollars with USD and the Australian dollars with AUD.  How can I do that in google sheets?
-------------------------------------------------------------------------------------------------------------------------

yogi_Based On dropDown Selection In Sheet1!B1 LookUp The Values In Cells B2 And C2

                                          Google Spreadsheet   Post  #1225
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 31, 2013
user blixel (http://productforums.google.com/forum/?zx=j31hqv3nfivh#!mydiscussions/docs/Z8W-45RIq2A)
How to fill a cell based on drop down selection?
I have 2 sheets in a spreadsheet.

On Sheet1 I have a drop down menu to select 1 of the planets. (Mercury, Venus, Earth, and so on)

On Sheet2, I have the radius and mass of each planet  in a list.

When I select a planet from the drop down menu, I want the target radius and target mass cells to be updated accordingly.

So if I select Mars from the drop down menu, then on Sheet2, the target radius becomes B7, and the target mass becomes C7. I don't know how to update the target based on the menu drop down selection.

Here is a copy of the spreadsheet for your consideration.

https://docs.google.com/spreadsheet/ccc?key=0Au6ynjm4RtfudEs5Q2taVk9OZGpXX2pZTGZVM1U1SHc&usp=sharing

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

yogi_WorkAround to Correctly Apply PROPER Function To A String In Google Spreadsheet

                                          Google Spreadsheet   Post  #1224
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 31, 2013
user Chris Palsgrove (http://productforums.google.com/forum/?zx=mwi1lx7ihzkq#!category-topic/docs/spreadsheets/-r1p-PRSdyU)
PROPER() function capitalizing more than expected
When using the PROPER() function, it seems to be capitalizing anything that follows a non-alphabetical character instead of a space or other whitespace, which in a lot of cases is undesirable.
Examples:
"4th grade" becomes "4Th Grade"
"It's a boy" becomes "It'S A Boy"
"I'm here" becomes "I'M Here"
"Se7en" becomes "Se7En"
"Ke$ha" becomes "Ke$Ha"

In all of the above cases, it's obvious that there is one letter that gets capitalized that was not intended to be capitalized. There are a few cases where this behavior might be desirable, but that would be the exception, I believe, not the rule.
Examples:
"O'connor" becomes "O'Connor"
"L'enfant plaza" becomes "L'Enfant Plaza"

It seems that given the wording on the description of the PROPER() function in the Google Spreadsheet Function List, the behavior of the function should be changed to capitalize the first alphabetical character immediately following a whitespace character instead of immediately following a non-alphabetical character. That is, unless I'm missing something and this is the desired behavior for a reason. Thanks for looking into this!
-------------------------------------------------------------------------------------------------------------------------

yogi_From Table All Shows_Status Rearrange Shows By Their Status Or Combination Of Phrases In Status Column

                                          Google Spreadsheet   Post  #1223
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 31, 2013
user Daltonj (http://productforums.google.com/forum/?zx=7m7yxfezh113#!mydiscussions/docs/wq1vyN8K0C4)
How to arrange column alphabetically and move adjacent cells with it

Hello

 

I'm trying to make a spread sheet to keep track of the tv shows I watch. I watch a lot. It throws me for a loop when they switch out fall shows for summer shows. Especially, if they're adding new shows to the roster. Ideally, I'd have the main sheet or at least the main area where I'd list shows currently on the air.

 

Let's say that the list of shows currently on air is in column 'A'. In column 'B', I'd have a status, such as 'On Air'. Each cell of column 'B' would have a drop down menu and the options would be: on air, on break, or cancelled. I'd like to be able to choose 'On Break' and have it take that show and put it in, say, column 'D'.

Column 'D' is a list of all the shows that are off for the season. Column 'E' would be where I'd put the date they are supposed to come back on the air.

 

I would keep track of the return dates myself and enter them manually. I'd want the spreadsheet to check the dates, hopefully all them time, but at least everytime I open the spreadsheet and send me an email reminder of any shows coming on in a set period of time, say the next 2 weeks. Also, on or after the day they're back on the air I'd like it to automatically move them to the 'On Air' section in column 'A'.

 

If I choose that the show is cancelled, I'd like it to move to column 'G' and stay there. It would just be a list of the shows that are off. I could manually delete it or just leave it.

 

My issue in this question is whether or not it's possible to organize, column 'A' for example and to take the status with it. I don't know that's it's an issue with the scenario I described, but if I had mixed statuses in one column it could concievable move just the show names and not their particular status. It wouldn't matter if I set it up as described, but in future projects I may not and if I want to have this whole thing verticle it'll be an issue now.

 

That's basically what I want to know. If you have info in column 'A' and info that goes with it in column 'B', can you auto alphabatize column 'A' and have the adjacent cells in column 'B' go with them in what might not be alphabetical order in column 'B'.

 

Sorry if that is confusing. If you tell me, I can clarify. Also, do you see any other issues with this set-up. If so, let me know. Also, can anyone tell me the code to have the info swap around like I'm talking about? When I choose 'On Break' in column 'B', have it move the show name to column 'D' and make the adjacent cell in column 'E' show 'On Break'? If you don't want to answer here, I'll create a new post with that specific question. Thank for all the help.

---

Here is the link, sorry:

 

https://docs.google.com/spreadsheet/ccc?key=0AvfzK4iYecZ7dFNaVU8zQWV0VFM0QzlIa3BJOE1WMEE&usp=sharing

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

yogi_Compute Date Entry Deadline As First Day Of Second Quarter After The Interview Date

                                          Google Spreadsheet   Post  #1222
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 31, 2013
user Kim B. (https://productforums.google.com/forum/?hl=en-US#!category-topic/docs/spreadsheets/6FYyWdYzSis)
If command using part of a date
I'm using Google spreadsheet on Chrome/Windows 7.

I have several spreadsheets to track data for a research grant.  I have a column that lists the date an interview was completed in mm/dd/yyyy format entered as text.  I want to create another column with a quarterly data entry deadline based on the interview date.  So, if the interview date is between January 1st and March 31st, the deadline is returned as July 1st of the same year, if the interview date is between April 1st and June 30th, the deadline is returned as October 1st of the same year.  For example, if the interview date is entered as 3/4/2011, the deadline will be returned as 7/1/2011.  If the interview date is entered as 4/13/2013, the deadline will be returned as 10/1/2013.  However, if the interview date is between July 1st and September 30th or October 1st and December 31st, the deadline will be January 1st or April 1st of the next year, respectively.  For example, if the interview date is entered as 9/23/2012, the deadline will be 1/1/2013.

Any ideas on how to accomplish this?

Thanks in advance for any help!

KB

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

yogi_Count Number Of Cases For Specified Group Where Status Column Contains Specified Phrase

                                          Google Spreadsheet   Post  #1221
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 30, 2013
user Clare-Noel Holinghaus (http://productforums.google.com/forum/?zx=7m7yxfezh113#!category-topic/docs/spreadsheets/oMozBKn-1mY)
Wildcard workaround with additional filtering
I'm trying to figure out a formula that will help me figure out how many cases that were assigned to a group contain "Closed" as a part of their current status. The problem I'm running into is that Google Spreadsheets does not have a wildcard symbol. I'm trying to use workaround functions, but I don't know how to combine them with the additional filtering to limit the result to only cases completed by a particular group.

For example, below I would be trying to figure out how many cases completed by Group B contain "Closed" as a part of the status.

Case               Group                Status
1                     A                       Closed: Complete
2                     B                       Open: Needs Follow Up
3                     A                       Open: Work In Progress
4                     B                       Closed: Complete
5                     B                       Closed: Canceled
6                     B                       Open: Work In Progress

The formula should return "2" in the above example.

I can't separate out the Status descriptors, so I am just looking for one formula that can get at this intersection of data.

The two formulas I have been looking at are =(COUNTIF( arrayformula(ISNUMBER(search(criteria, range))),TRUE)) and =COUNTIF(FILTER(array ,range=criteria), criteria)
If anyone knows a good way to combine these two, or can figure out an alternate formula, I would be very appreciative!

Thanks!

Clare

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

yogi_Fix The User Formula For Multiple Criteria Count To Ignore Counting Blank Cells

                                          Google Spreadsheet   Post  #1220
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 30, 2013
user rajvivek (http://productforums.google.com/forum/?zx=7m7yxfezh113#!category-topic/docs/spreadsheets/5k-NERoJugo)
How to let the formula ignore the blank cells?
The formula at this spreadsheet (in cell D3) is taking into consideration all cells including the blank cells, and giving result a part of which is undesirable (as shown in cell F10).

How could i edit the formula so that it simply ignores the blank cells and the out (in column F) should be for the actual data and not for the blank cells?

Thanks!

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

yogi_Pull The PaidOut Amounts And Post Those In A Separate Sheet For Each Category

                                          Google Spreadsheet   Post  #1219
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 29, 2013
user pravrama  (http://productforums.google.com/forum/?zx=3yy16sps27xr#!mydiscussions/docs/hyTGEFYh3Ww)
Copying cell contents to another sheet

Hi all,

I wanted copy cell contents from one sheet to different sheets depending on the category selected.  For example, if I entered a value and categorized it as 'construction costs', i wanted the value to copy to the 'construction costs' sheet.  Any ideas how I can do that?  Thanks so much for any help!!

---

Thanks Yogi for your reply. 

here's a link to my sample spreadsheet https://docs.google.com/spreadsheet/ccc?key=0AkXtt1y1fFrsdExhbU5Pd2dwbzdleWt1VkM3dlVOZHc&usp=sharing

I'm trying to figure out 2 things:

(1) In 'Paid Out', how to create a drop down box in column A where I can choose Lawyer, Architect or Construction

(2) Once I choose a category, I want to copy the 'Amount', 'Reason' and 'Date' columns over to its respective sheet. 

The first point isn't as critical if we can accomplish the second in an easier way.  Thanks, I appreciate your help with this.

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

yogi_Pull Into Column C Unique Names From Strings Of Data In Columns A And B

yogi_Query Data In Responses Sheet And Pull Data From Select Columns Based On Latest Date In Ascending Order

                                          Google Spreadsheet   Post  #1217
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 29, 2013
user user Alfred Lazarus (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/how-do-i/desktop/qJyhoqXa6LE)
Formula to get latest Value
Hi All,

https://docs.google.com/spreadsheet/ccc?key=0AjA1J4T5598RdFh6VEJLeEh6QTVHajVWUTlMamFrS1E#gid=14

i am having issues trying to run an array formula to return the most recent date and status for a particular value. The current log sheet should pull the lastest date it was updated and the latest status, i tried using an array formula it works for single cells but I have to drag it each time there is a new entry, how can I get the formula to automatically get applied to all cells in Current Log A column, pulling data from the Node history sheet?

Thanks Guys,

PS: I tried using Yogi Anands formula from another post but that dint help either :(

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

yogi_Query Data in Range From A29 to D For A Month Specified In cell C2

                                          Google Spreadsheet   Post  #1216
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 28, 2013
user user jsarch (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/BbmjgU4sdyQ)
Filtering Data w/ Query
I know query is exactly the tool I need to use, I just have no idea how to use it!

I have the following query function:

=query(B29:D;"select B,sum(D) where B <> '' group by B label sum(D) '' ")

and it works fine, except now, I need to filter the data in column B based on what is in column A, before the query works its magic. I'd typically just use Filter, I'm thinking that ALL of this can probably be done with query, I just have no idea how.

https://docs.google.com/spreadsheet/ccc?key=0AkRGrGvH7D6edHNyTlVNTnNLZ3NHWnhIU0s4WDk2dEE#gid=92

You can see the query function in C6, and it's adding up all the hours in rows 29 and below, except I want to filter those hours based on the date in column A. And there is a drop down menu in C2 that I choose which date I'm looking at.

Does anyone have any ideas?

Thanks!

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

yogi_Insert Into a Spreadsheet Cell An Image Derived From Google Maps

                                          Google Spreadsheet   Post  #1215
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 27, 2013
user user Julia Kreppel  qustion by Bash and ethauvin (http://productforums.google.com/forum/?zx=qauj613l7zna#!category-topic/docs/spreadsheets/Uoxhv0KIJuE)


Julia kreppel:

Hi folks

I have a spreadsheet and want to display an image derived from google maps.

The URL of the image is http://maps.google.com/maps/api/staticmap?markers=color:red|Karlsruhe&zoom=8&size=280x140&sensor=false - it works fine

Wenn using =image("http://maps.google.com/maps/api/staticmap?markers=color:red|Karlsruhe&zoom=8&size=280x140&sensor=false",3), I get an error: No image was found at the URL...

The strange thing is: It was working in the beginning...and I found a Shhet from someone who is using this comamnd in the very same way in order to display a map. When I reopened my document and changed locations, I got the error

Here is my document

https://docs.google.com/spreadsheet/ccc?key=0Akwdvyv4mRG7dDlrSUVEVGNqODNEeWdxd3BMVVkzMWc

any ideas?

---

Bash

Same Problem here, did you end up finding a solution?
The image did appear for me before in the Cell then, it started showing that same error.

The url is accessible and I can see the image in a separate window.

---

ethauvin

Same problem here, anybody ever found a solution that actually works?

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

yogi_MultiConditional Sum Involving Dates Entered As TEXT

                                          Google Spreadsheet   Post  #1214
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 27, 2013
user wetabax (http://productforums.google.com/forum/?zx=qauj613l7zna#!category-topic/docs/spreadsheets/ZotKtEcevQo)
problem with function SUM together with FILTER

It seems that Google Docs doesn't offer the SUMIFS function. Instead, it was suggested to use a workaround combining SUM with FILTER

I'm trying to sum values filtering months in a specific year.

I've created a formula like this: =sum(FILTER(H:H,A:A>="01/02/2013",A:A<="28/02/2013")) but something must be wrong, I have an error message.

H = values A = dates

I made another test, creating two cells with the dates. E.g. cell L3 with the date 01/02/2013, and cell M3 with the date 28/02/2013

Formula used is: =sum(FILTER(H:H,A:A,">="&L3,"<="&M3)) - Didn't work as well.

Can anybody find what is wrong in it? (the DD/MM/YYYY is setted as correct = portuguese language - so it's not MM/DD/YYYY).

thanks.

I've shared by link the Spreadsheet, only for view, if necessary.

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

date formats available in Portugese are YYYY-MM-DD or YY-MM-DD ... so to key-in dates in DD/MM/YYYY format I used date entries as TEXT in DD/MM/YYYY format

yogi_Merge And Sort Data From Food And Drink Budget Sheets And Then Identify The Source Of Each Row Entry

                                          Google Spreadsheet   Post  #1213
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 27, 2013
user WillCallag (http://productforums.google.com/forum/?zx=qauj613l7zna#!category-topic/docs/spreadsheets/6y0O84QtZzs)
Sourcing imported information

Here are the files pertaining to my question:

Master Budget:     https://docs.google.com/spreadsheet/ccc?key=0AihYKqlqWCgsdHNocGR5ckdoOElwVExxcjVCSTVWTmc#gid=0

My Drink Budget:  https://docs.google.com/spreadsheet/ccc?key=0AihYKqlqWCgsdGxuZHlOcV9zc09EdXROem0tTUZUQ0E#gid=0

My Food Budget:  https://docs.google.com/spreadsheet/ccc?key=0AihYKqlqWCgsdHZSRUoyTG92NlUxeVFwbnZmYW96OXc#gid=0

Master budget has 3 tabs: Totals, Food Budget and Drink Budget.

- Food Budget and Drink Budget are imports from the other sheets.

- Totals contains a merge of food budget and drink budget.

Now, the sheets may be called "Food Budget" and "Drink Budget" but the file names are respectively "My Food Budget" and "My Drink Budget". Therefore, under Source in the Master Budget, I want each value to show its source (either from My Food Budget or My Drink Budget).

How do I go about doing this?

Thanks,

Will

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

yogi_Pull From Sheet1 Records That Are Within The Date Range Of Today And Next 28 Days

                                          Google Spreadsheet   Post  #1212
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 27, 2013
user Alistair Fraser (http://productforums.google.com/forum/?zx=twarxl97ddra#!mydiscussions/docs/e12jwAERI4o)
How to set up a sheet to display selected rows from another sheet?
We would like to set up a sheet to display a list of rows from another sheet, which contain dates within the range: from today for the next 28 days.  These dates are in one of the columns of the source sheet.  The display sheet will look the same are the source sheet but just show the rows within the date range.
---
Thanks Yogi

I've tried to work it out, I think it's a QUERY, but not sure ...

Here a link to a sheet I've done to explain what I'm trying to do

https://docs.google.com/spreadsheet/ccc?key=0Agk9K8AL3znNdG82NDZkUEUyRGxmUG9DUEJWWDg3Rmc&usp=sharing 

Best Wishes

Alistair

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

yogi_In A LineChart Show Every Date Entered In Column A On The Horizontal Axis

                                          Google Spreadsheet   Post  #1211
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 27, 2013
user Aileen Cruz (http://productforums.google.com/forum/?zx=twarxl97ddra#!mydiscussions/docs/a9QMn6spP1Y)
need help creating a line graph from a spread sheet

Here is my problem, while i can create the horizontal axis doesnt include all the dates i have on my sheet, for my project requirement, i need to show all dates on the graph, please help!

---

https://docs.google.com/spreadsheet/ccc?key=0AgFM3v3SBTvydHpVVlJ0cEwtSmpmVGxaWVo2TnFJN3c&usp=sharing

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