Saturday, August 31, 2013

yogi_Pull Into yogi_Result Corresponding Values For URL Visits And Sell Numbers

                                          Google Spreadsheet   Post  #1351
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug 31, 2013
user Ramarosoa Faneva (http://productforums.google.com/forum/?zx=1nlqkgk0rsyr#!category-topic/docs/spreadsheets/5GoLpHpfODM)
Vlookup content
Hi All,

I m searching a solution to use the vlookup in cells content. Below you have an example.

Ex : 
Sheet 1 content : URL + Visitors
Sheet 2 content : SKU + Sales

Specific : the URL contains the SKU and she is uniform.

My purpose : search if the URL contains the SKU, give me the URL and the visitors.

I other program I can use vlookup("*"&C1&"*"........) but i don't know how to do it in the google spreadsheet.

Ty for your help
---
Hi Yogi,

There is the link for an example : Link

The purpose is to complet the sheet "result" just by dragging the formula.

(I don't know realy well how can i explain it to you but look the file and you understand automaticly the purpose of the my question).

Ty for your help.
-------------------------------------------------------------------------------------------------------------------------------------------------------

Friday, August 30, 2013

yogi_LookUp Address Of Entity In Cell B1 From A table In Another Sheet

                                          Google Spreadsheet   Post  #1350
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug 30, 2013
user Xavier Cordova question by Gheoghe: (http://productforums.google.com/forum/#!mydiscussions/docs/8TITC26rzaw)
ImportRange depending on option chosen from a dropdown list
7 posts by 4 authors
Short question:

I have a dropdownlist (names) and I would like that when I choose one name (list is in Sheet1 in column A) , the adress will show automatically in the cell bellow cell containing the dropdown (the adresses are in Sheet 1 and Column B).
And also: what if I have in column B -Street / column C- House No / Column D-Code / Column E - State or Country)

How can I get this done?
Gheorghe
---
HI,

been busy, but here we go now. Some comments for it.

I use this for invoicing some online form used by my customers. 
As you see I get the ready to print sheet ( Coaches). However I need to get the Adress under the name. The list is in Sheet called Adress See column B


Greets
G
------------------------------------------------------------------------------------------------------------
 

Thursday, August 29, 2013

yogi_Count Instances Of A Specified Character In Column B When Column A Equals Another Specified Value

                                          Google Spreadsheet   Post  #1349
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug 29, 2013
user amj05 (http://productforums.google.com/forum/?zx=2cathjan7ij1#!category-topic/docs/spreadsheets/zgI4vGlrwSo)
Count instances in an array if
If I have two columns that look like this:

AX, Y
BY
CX, Z
BX, Y, Z
DZ
AY, Z
AX, Y, Z

Is there a formula to count the total number of times Y appears to the right of A (whether or not Y appears with anything else)? The only way I know involves way too many lines (especially since there will be more variables than in this example). Thank you!
----------------------------------------------------------------------------------------------------------------------------------------------------

Wednesday, August 28, 2013

yogi_Given An Attribute Value Find The Matching Name Of Item From Another Sheet

                                          Google Spreadsheet   Post  #1348
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug 28, 2013
user Peter Nolan (http://productforums.google.com/forum/?zx=rjvzs6hyvk66#!mydiscussions/docs/-y1Rvopvfbg)
extracting information from tables and creating sorted lists without using the sort feature
I've created a spreadsheet that has a stock portfolio listed along with the various attributes of the stocks in the portfolio i.e.: name, price, cost, fair market value, gain/loss, % of potfolio etc. On a separate worksheet in the same spreadsheet I'm trying to created a list to identify the stocks that represent the highest percentage of my portfolio, the one with the highest gains, the ones with the highest losses etc.. I can get that information but I can`t get the adjoining cells to link so that I know the stock's name. I've found the google sort feature has caused me problem where I've had to re-enter all my information again and doesn't work very well as it messes up my information. 

For instance, to list the stocks that represent the highest percentage of my portfolio(found in column D of worksheet A, I've done the following:

On worksheetB I would put the following formula referencing worksheetA

In WorksheetA the % of the portfolio is listed in column D(range D1:D120); The stock names are in cells C1:C120. The following formula in worksheetB gets me the highest and second highest % but how do I get the stock names to follow in the adjoining cells?

In cell B1 i put the following:  =large(worksheetA!d1:d120,1) This gives me the largest % in range d1:d120.
In cell B2 i put the following:  =large(worksheetA!d1:d120,2) This gives me the second largest % in range d1:d120.

How do I get the corresponding stock names into cells A1 and A2?

Thanks
---
--------------------------------------------------------------------------------------------------------------------------------------


yogi_Set Up Formula To Print Only Chosen Cells Within A Google Spreadsheet

                                          Google Spreadsheet   Post  #1347
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug 28, 2013
user felin (http://productforums.google.com/forum/?zx=rjvzs6hyvk66#!mydiscussions/docs/bV54mfIHJk4)
How do I create a formula(s) to print only chosen cells within my Google Spreadsheet??
Hello there.

I just created a fairly complicated (to me anyhow) chore sheet for my family.

There are 3 of us who share in and trade off on the chores so that is what makes it complicated. If I can attach it here I will. Not sure if am allowed to do that.

I did one whole month at once. Each week is a little bit different.

Here is what I'd like to be able to do if it is possible.

For my daughter, for eg., I'd like to be able to create some formula that would allow me to see only her chores and all of the dates that correspond, etc. and THEN be able to print only one of the weeks at a time. I do not know how to operate formulas so am pretty lost.

PLEASE help if you can.

Really need this ability so that I can print each persons own personal chore sheet out each week.

Thanks so much!!

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

Tuesday, August 27, 2013

yogi_Query With Totals Max And Latest Transaction For Each Unique Credit Card

                                          Google Spreadsheet   Post  #1346
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug 27, 2013
user AlexSFr (http://productforums.google.com/forum/?zx=qk7tzpbpz0qo#!category-topic/docs/spreadsheets/fN4r-xZ1Omc)
Query with totals for each unique entry
So, I have a spreadsheet that has a list of credit card numbers along with dates of transactions made, that looks similar to this:
 
Card No           Purchase By                Amount                  Date
321352             George                        $35                        02/13/13
321352             George                        $70                        05/13/13
485352             Bill                               $76                        07/13/13
485352             Bill                               $50                        08/13/13
321352             George                        $70                        08/23/13
 
I would like a query that shows the total transactions made on each card, and what and when the last transaction was made. Example:
 
Card No              Total Transactions            Last Transaction                   Date of Last Transaction
321352                $175                                 $70                                        08/23/13
485352                $126                                 $50                                        08/13/13
 
Using query with sum and max I can get the values for Total Transactions and Date of Last Transaction, but I can't get the Last Transaction value to show up.
 
Any help would be appreciated.
Thanks.
-----------------------------------------------------------------------------------------------------------------------------------------------

yogi_Setup Formula For Pace In Column H To Be Computed based On Relation With Entries In Column E

                                          Google Spreadsheet   Post  #1345
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug 27, 2013
user AWade North (http://productforums.google.com/forum/?zx=8lqk2iedo462#!mydiscussions/docs/_CDvE6C40xc)
http://productforums.google.com/forum/?zx=8lqk2iedo462#!mydiscussions/docs/_CDvE6C40xc
I have attached a screen shot of my spreadsheet, I am trying to have it populate 2 cells with the selection in 1 cell. any ideas
Attachments (1)
Spreadsheet SC1.jpg
226 KB   View   Download


Sunday, August 25, 2013

yogi_Compute Weekly High Using Google Finance For Specified Ticker Symbol

                                          Google Spreadsheet   Post  #1344
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug 25, 2013
user Andrew Sallans (http://productforums.google.com/forum/?zx=duk3bwersepo#!category-topic/docs/spreadsheets/N1J0-ZDJU1Y)
Finding a weekly high in Google Finance
Anybody have any idea how to automatically calculate a weekly high for a given ticker symbol?  I currently have the formula =googlefinance("SYMBOL","high",(today()-30),today()) working to list all high marks per day over the past 30 days, but I now would like them grouped automatically into weeks.  Any ideas?
---------------------------------------------------------------------------------------------------------------------------------------

yogi_Chart Data In Column A In Specified Order Using Specified Character

                                          Google Spreadsheet   Post  #1344
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug 25, 2013
user anonynus (http://productforums.google.com/forum/?zx=duk3bwersepo#!category-topic/docs/spreadsheets/4ITm0o5eaX8)
How do I get a chart to show my data in order of value.
I am creating a chart and I would like the graph to show the data in order of it's value. As an example, if I entered:
1
4
7
4
6
7
9
2
4
6
I would expect to see something like:
-
--
----
----
----
------
------
-------
-------
---------

Can anyone tell me if this is possible and if so how to do it?
---------------------------------------------------------------------------------------------------------------------

yogi_Count Instances Of Specified Word or Phrase In A Column

                                          Google Spreadsheet   Post  #1343
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug 25, 2013
user Richard McLaughlin (http://productforums.google.com/forum/#!mydiscussions/docs/tBS7TBvOels)
Counting instances of text in cells
Good afternoon everyone.  I am attempting to write a formula that will simply count the number of cells with a specified text, but count the number of times that text occurs within a specified cell range.  Here is what I have so far:


Any help you can provide would be fantastic. Thanks.

KR,

Rick
---------------------------------------------------------------------

Saturday, August 24, 2013

yogi_WorkAroud For Highlighting A Cell Where The Cell Contains Lowercase Letter(s)

                                          Google Spreadsheet   Post  #1342
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug 24, 2013
user Fred Drake (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/dpdvXF7geFY)
Conditional formatting: Testing for a lower-case letter
I have a sheet with alphanumeric codes in one column, where any letters may be upper or lower case, but it's significant which.

When a cell contains a lower-case letter, I'd like to change to formatting of the cell.  Is there a way to express this without writing 26 formatting rules for "a" - "z"?  Can we use regular expressions or character classes in the test expressions in some way?

Thank you!
---------------------------------------------------------------------------------------------------------------

Friday, August 23, 2013

yogi_Pull Price Based On An Item From A File Dependent on Embelishment Type And Characteristics Such As No Of Colors And Quantity

                                          Google Spreadsheet   Post  #1341
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug, 23 2013
user puma10 (http://productforums.google.com/forum/?zx=nudwadbs2itd#!category-topic/docs/spreadsheets/c7bWrNaIapQ)
How to calculate price from a price matrix
Hello all,

I am trying to create a price calculator from a price matrix.  I would like to enter some values into a few input fields.  These values would act as coordinates on a matrix to pull the price.  Here is a static version of what I'm trying to accomplish. 


I'm not sure where to even begin with this.  I tried nested if statements and realized that was probably not the most efficient way.  Would I use a an array and loop through somehow?

Look forward to your advice! 
------------------------------------------------------------------------------------------------------------------------------------------------------

Wednesday, August 21, 2013

yogi_Extract Matching Values For Multiple Rows In Another Sheet For Different Columns

                                          Google Spreadsheet   Post  #1340
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug, 21 2013
user Daniel Eng ... via Google+
Hi Yogi.
i am trying to send emails with attachments from a spreadsheet but i am looking for a good way to update which attachments should be sent.
for example.
One sheet (filelist) contains a filename list and a idnumberlist (id:s for google drive files)

            a                                 b
a1       filelist                        idlist
a2       file1          0B64xvjHVr1V7VHZuMS15azl4SzQ
a3       file2          1BIHsiL-yWEjdCHqMedu7sIrMA2WX7fRCXPbnowO_aJ4

another sheet contains form inputs and than it searches for the idnumber acording to the input in c and d and gives the idnumber in e and f
        a                   b                                 c                         d                             e                             f
a1   name          email                         attachment1   attachment2      searchid1           searchid2
a2   Daniel         abc@email.com     file1                    file2                      =idnumber file1  =idnumber file2
a3   ruben          ruben@email.com  file2                    file1                      =idnumber file2    =idnumber file1
a4   Oskar         def@email.com       file1                    file2                      =idnumber file1    =idnumber file2

when i tried to add the same cars several times in you car query it only displayed it ones in the search column. and in this one you see file1 shows up for example 2 times in the c column and the idnumber for file1 (0B64xvjHVr1V7VHZuMS15azl4SzQ) should be displayed twice in the e column.
also when i tried the query in 2 sheets i couldnt get it working because it querys only from one sheet.

Looking forward to your help.

Cheers
-Daniel
---
Hey Yogi
here is  the link to the spreadsheet:
https://docs.google.com/spreadsheet/ccc?key=0Aq4xvjHVr1V7dEFPMzdGYkVnS2Fmc3UzV1FsYU1ES0E&usp=sharing

As you see i want display an idnumber in F and G for each line file found in D and E and if the cell is empty than the search cell is empty.

Made some comments and such so you see easier.
Thanks Alot
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sunday, August 18, 2013

yogi_Multiply Numbers in A Column (Open Ended) With Numbers In A Row (Open Ended) Essentially Making A TimesTable

                                          Google Spreadsheet   Post  #1339
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug, 18 2013
user rastiers (http://productforums.google.com/forum/?zx=ctp7wu7ycmyz#!category-topic/docs/spreadsheets/MPK7YeWeYU4)
Multiplying and repeating a func for numbers in a row by numbers in a column
how do a easily multiply numbers in a row like 1 2 3 4 5 6 7 8 9 by numbers in a column 1 2 3 4 5 6 7 8 9... essentially making a times table.

thanks for any help!
----------------------------------------------------------------------------------------------------------------------------

yogi_Calculate Monthly Subtotals Of Transactions By Category

                                          Google Spreadsheet   Post  #1338
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug, 18 2013
user cko (http://productforums.google.com/forum/?zx=ctp7wu7ycmyz#!category-topic/docs/spreadsheets/mSQmoJA8wIA)
How to calculate monthly subtotals of transactions by category
I'm trying to make a spreadsheet where I can enter in categorized transactions on one sheet and then have a second sheet that takes those transactions and adds up monthly subtotals for 
each category.

I've put most of it together in this example doc, but am not sure how to do the monthly and category sums on the second sheet.  Can anybody help with a working formula that does this?

Hopefully, this can be done in a way that is not hardcoded to a particular year so I could just duplicate to start tracking a new year.

Thanks in advance.

(let me know if you need write access)
--------------------------------------------------------------------------------------------------------------------------------------------------------------

Saturday, August 17, 2013

yogi_Sum Up Numbers After Importing CSV-File

                                          Google Spreadsheet   Post  #1337
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug, 17 2013
user PooBoy (http://productforums.google.com/forum/?zx=urjhu9nw87rq#!category-topic/docs/spreadsheets/eGQK67SVT1I)
After Importing CSV-File, containing numbers not usable
Hello,

I imported a CSV-File with "tab-stops" as delimiters. The sheet seems to look proper. But when trying to use the numbers (i.e. for summing up), it fails.
A zero is shown.

When I edit the cell with the number it works. The edit is only inserting a space behind the number and removing it.

Is something going wrong during the import process?

Here is the sheet and the original file I used:

Thank you very much for you help!

Best
PooBoy

PS: is my other stuff on my GoogleDrive safe, which I did not share with the provided link above?
---------------------------------------------------------------------------------------------------------------------------------------------------