yogi_Automatically Sort Data In Rows Of A Table Based On Values In Another Column

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Peter-27 said:
How do i autosort values in a table?
On the results page there are three tables.
1st table is to input data.
2nd table turns the inputed data into different numbers (required for what i'm trying to do)
3rd table is the same as the second, but i want it to automatically sort based on the Total, T column.
How do i get this autosort?
------------------------------------------------------
In sheet named Results, TABLE C is sorted based on values in range T16:T26 in TABLE B
I have considered the required sort options for TABLE C to be ascending, descending, or restore based on range T16:T26 in TABLE B.

yogi_Separate Entries In A Document Into Different Sheets Based On Entry In A Specified Column

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Macgriff said:
How do I sort a large document into different sheets based on a column? Ex. Sheet sorting based on city
I currently have a huge document with 8 colums. I want to sort column D (City) into different sheets. Is this possible?
---------------------------------------------------
I created a separate sheet named after each unique entry in column D data of Sheet1 and then I used the Filter function to extract all records matching the value in column D of Sheet1 with the entry in cell A1

yogi_Print Mailing Labels From A Spreadsheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

new.clogger21 said:
print mailing labels from a spreadsheet
-------------------------------------------------------------
Google spreadsheet does not have a built in feature to print mailing labels ... so
1) I can use a function such as the INDEX function to setup mailing labels in multiple lines from each row of spreadsheet
2) set up the labels in multiple columns
3) set up a number of labels in each column optionally with a blank row between each label to be printed
4) in using mailing label paper such as (AVERY, HP, etc.) one needs to try a draft print on plain paper first
5) make adjustments in row heights and/or blank rows, and column widths to make sure the labels print correctly
6) once everything is fine tuned the way one wants one can go for final printing on mailing label paper

in the following illustration I set up formulas for one 5 line label and then I copied the formulas in 2 columns of 10 labels per column. For a different setup, the formulas will need to be adjusted to suit

yogi_Determine Name Of An Area From X And Y Coordinates

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

marklancs said:
determine name of an area from x.y co-ords
Imagine a a square 100 x 100.
x=1 to 50/y=1 to 50 is area 1.
x=1 to 50/y=51 to 100 is area 2.
x=51 to 100/y=1 to 50 is area 3.
x=51 to 100/y=51 to 100 is area 4.
X co-ords are in column A, Y co-ords are in column B other static info in C and D. I need column E to calculate Area Name when new co-ords entered.
There is probably an easy way of doing this. The only one i can think of is a lot of If's along the line on if x>0 and x<51 and y>0 and y<51 then ...
--------------------------------------------------------------

here is an image showing the data with X and Y coordinates in columns A and B and the desired results in column C ...

yogi_Increment Data In One Sheet By Checking Against Existing Data In Another Sheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

MemberCarDan said:
Take data result from one sheet and check against existing in another sheet
One sheet - example2 - has an existing list of unique customer codes based on first two letters of last name, first letter of first name and then occurrence 01, 02, etc. (customer, Bobby Boatmaker would be BOB01).
Sheet example1 is where new customers get entered. I have worked out the formula to read the first and last names and convert to and ID code (not in example). What I can't get is:
I need to check my new customer ID code against the existing list (example2) and then 1) if there is no current entry, put the new code in OR 2) if there is an entry, read the entry and add the next occurrence (e.g., new customer is BOB, already have BOB04, so adds BOB05).
I've been trying to work it out with a VLOOKUP or query but I'm not good enough :( yet :). Any pointers appreciated.
---------------------------------------------------
I have assumed the ID codes in sheet named New are to be developed using first two characters of Last Name and first Character of First Name and then a numeric code is to be appended by checking against the ID codes that already exist as documented in sheet named Existing.

yogi_Create Questions In Google Forms With Specified Multiple Attributes

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

djgroves03 said:
Creating a multiple choice question in forms with write-in answers
I'm creating a survey in Forms and I want to have a question that asks something like: "What percentage or your clients speak the following languages?" and then have a series of languages listed, giving the responder the ability to fill in a percentage next to each language.
For example, the survey provides the following options: "english," "spanish," "mandarin," and "french" and the responder is able to fill in "65%," "25%," "0%" and "10%" next to each one.
How do I do this?
---------------------------------------------------------
Not a write-in answer ... but my following solution is a simple workaround that should do the job. I used the Scale type question

in the following I present the Form that I created for this ...


and here is the spreadsheet

yogi_Extract Data Between Two Specified Characters With The Character Appearing At Different Places In A String

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Lit said:
How do I extract specifc data between two ' apostrophes when there are four apostrophes at different places in the cell?
My data has the following text in cell column A, cells 2 through 600.
xxxxxxxxxx['K0143M678Z'] = '0';
I need to only pull out the data starting after the first apostrophe which is after the first "[" (box bracket).
The data after the apostrophe changes i.e., it may become '1x34xs24xx' and won't always be "K" but it will more than likely stay limited to 10 characters
----------------------------------------------------------------
In the following I present my convoluted solution for a generalized case in regard to the character used and the starting and ending position of the character.

yogi_Enter Data In Columns A,B,C And Present Sorted In Columns F,G,H Of Specified Block Size

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

SusanASarandon said
How Do I Sort All The Info Alphabetically On The Entire SS At Once?
I have columns A-D filled with names and I can sort them alphabetically, but only by each column. The reason I didn't use just one column was so that I could see everything on my screen at once. How can I Sort all the data A ---> Z so
that the columns are 25 lines long and go alphabetical like A1-A25 to B1-B25 to C-1 to C-25 and so on?
-------------------------------------------------------------------
I have taken a case where the data can be entered say in columns A,B, and C in any number of rows ... then data entered in columns A,B, And C taken together is sorted in ascending order ... and then presented in columns F, G, and H in the specified Block size.

Yogi_Count Text Votes In A Column To Find The Winner In The Associated Category

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

cbrentlane said:
Counting text votes in a column to find the winner?
I created a form with GoogleDocs to vote for superlatives for kids at a camp I am working at.
Now, I want to figure out how to harness the power of the spreadsheet to find the winner in each category
---------------------------------------------------
In the following ... In the ResponseTable associated with the Form submittals, I inserted a computedField (WINNER) column for each category as shown with brown colored background

yogi_Copy Rows From One Sheet And Paste Into Columns Of Other Sheets

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

thesillyme said:
How do I use the values in a column of Sheet1 to paste into separate cells in other sheets?
I don't know if this is possible, but..
Basically, I have a mailing list on Sheet1. It includes names and addresses for businesses that I want to put as the addressee in separate letters.
At first, I was going to try to link each cell in a document, but that seemed less likely to be able to do. So, I put my letter in cells in Sheet2. Now I'm hoping I can duplicate this sheet several times and insert the values into a couple of cells in each sheet.
Is this possible? Or am I looking at a more manual fix?
------------------------------------------------------
Here we go ... I have transposed the information from cells B2:B4 of Sheet1 to cells A1:C1 of Sheet2, and Sheet3. I would not be surprised that user thesillyme wants to do more than just transposing cells B2:B4 to cells A:C1 of different sheets.

yogi_Compute Average Of Numbers In Specified Columns By Row

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

bztkd said:
Averaging Columns
I have a spreadsheet with 6 columns.

I need Column F to be the average of Columns C,D and (sometimes) E. If E is not to be included in the average the cell will be left blank. Columns C,D, and E are all numbers. How do I go about doing this?

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

yogi_Combine Data From Different Columns Corresponding To A Row With A Specified Separator

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Antagonasty said:
I'm using Chrome an Windows 7
I need to create a google doc with a huge amount of data for my bosses. They are easily intimidated by technology and don't want to use anything other than Google Docs. What I need to do is:
Take a huge list of names, and sort a huge amount of data about them including 2 addresses and a big list of dates for each name.
What I would like is to make a column for dates, and within each cell put between 1-50 dates. However, to keep the cells with 50 dates from making the spreadsheet too unwieldly, In my imagination, what would be awesome is if after a certain amount of data, the cell stopped showing that there was more automatically. Maybe if you clicked on it, it would become visible.
If there's a way to do this, awesome. If there's a better suggestion one of you has, that would be even better. I'm at a total loss here.
------------------------------------------------------------------
Let me see if I understand what you are intending to do ...

yogi_Mark Items In A List That Are Duplicated -- Not The First Occurrence Of An Item But All Subsequent Occurences

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

alexadw2008 said:
Find duplicates and add word "Duplicate" to colum next to the duplicate text.
----------------------------------------------------------------------

yogi_ Sum A Range Based On Criteria

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

qipaco25 said:
Have been breaking my head over this for some time and hope some one could help me out. The best to describe what I'm trying to do is through an example.
I have the following sheet setup ...
---- A--------B----------C
1---BR2------234--------702
2------------345
3------------123
4---MBR------213--------423
5------------210
6---BR4------124--------203
7-------------34
8-------------45
9---HALL-----234--------234
... I would like for Column C to automatically add a range of values from Column B where Column A begins with a value (ex. BR2 and just before a new value MBR). So C1 = B1:B3. Of course all values and number of blank rows in Column A would be dynamic, based on other parameters.
I hope this makes sense and looking forward to some good ideas!
----------------------------------------------------------------
Following solution is one way ...
wherein I added an intermediary column C

yogi_Highlight A Cell Based On A Condition

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

stevebb said:
How do I highlight a cell on a condition?
How do I highlight a cell when a certain conditoin is met and keep it highlighted if the condition is not met later?
For Example:
I'm tracking a stock price and I have a sell price set. Then when the sell price is met the cell will change green. How do I keep the cell green even if the price drop and the the condition is no longer met.
-----------------------------------------------------------
The best I can do in Google Spreadsheet with the currently available Conditional Formatting features is to highlight an adjacent cell(s) as shown in the following example.

yogi_Manipulate Multiple Dates By Changing A Qualifier In A Separate Column

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Whathe1 said:
How can I manipulate multiple dates by changing a qualifier in a separate column?
I have a column full of numerous dates (column A). I would like to be able to alter all the dates in column "A" simply by adding a number to a cell located in Column B. For example if in A1 I had July 20 2011, I could change the date in A1 to July 21, 2011 simply by adding a 1 to the predetermined cell in Column B and if I wanted the date in A1 changed to July 22,2011 I would obviously change the cell in Column B to "2".
My Goal here is to basically add some temporary scalability and effectively alter hundreds of dates in Column A without having to manually adjust them.
Hopefully I've explained this clearly enough and I would appreciate any assistance that could be given.
-----------------------------------------------------------
What we need here is an array formula ... even though it is a trivial issue, it is best illustrate with the following example in Sheet1 and Sheet2

yogi_Apply Formula To Multiple Cells In A Column

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

lulae said:
how do i apply this formula to multiple cells in a column?
=(C3*D3)+E3
I would like the formula to apply to rows 3-50, 50 being =(C50*D50)+E50
many rows are not complete yet and it still needs to function
I tried various things but I'm not quite grasping how to write an arrayformula, assuming that is how to do
this.
----------------------------------------------------------
Yes, you indeed need an array formula ... and the following illustration will show how to use the array formula. And information if some cells is missing ... no matter -- the formulas will still work and if and when the missing information is filled-in the formulas will update themselves.

yogi_For Golf Handicap SumUp Score For Specified Number Of Last Games Played

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

rdwestny said:
To calculate handicaps, I need a formula to sum the last five golf scores of league member to calculate handicaps. In the example below, I need to sum Player X's last five games, which were played on 07/07/11, 06/16/07, 06/09/11, 05/26/11, and 05/12/11.
Date Player X
04/14/11 80
04/21/11 70
05/05/11
05/12/11 65
05/19/11
05/26/11 82
06/02/11
06/09/11 75
06/16/11 78
06/23/11
06/30/11
07/07/11 80
-------------------------------------------------------------------------
here we go ...

yogi_Create Registration Form That Groups Name Age And Email Fields

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

TMMMod said:
Howto group name, age, email fields on a form
I am creating an event registration form wherein a member can register name, age-group for each of attending family member and a common email and phone number for the whole family.
So my question is how to group name,age-group fields and show multiple such sets on same form.
For e,g. Form should look like
Name1, Age-group1
Name2, Age-group2
Namex, Age-groupx
Contact Email
Contact Phone
[SUBMIT]
Name -- Text field
Age group- multiple choice (<18, 18 and above) Is it possible to do such a form using Google docs speard sheet ? I would appreciate any guidance, sample url etc.
-----------------------------------------------------------------
in the following I present the Form that I created ...


and here is the spreadsheet ... the brown colured columns Are the calculated fields that I inserted in the spreadsheet subsequent to the creation of the Form ...
I created a Form wherein I had the user fill in Name and AgeGroup together as part of 1 question ... then I created calculated fields in the spreadsheet to separate the AgeGroup formulatically

yogi_Embed A Sheet Of A Spreadsheet And Not Display The Sheet Number(Name)

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

fredvolkman said:
I would like to embed a spreadsheet into a web page without displaying the sheet number. Is there a way to tweak the code so that the sheet number is hidden?
----------------------------------------------------------
You can publish contents from a sheet of a spreadsheet without displaying the sheet number(name) provided ...
1) you select only a single sheet
2) you select a specific range to be published
3) in the code generated to publish as an HTML page
you change from widget=true to widget=false

In the following illustration, I chose only Sheet3 and range A1:F2 of Sheet3 to be published ...

yogi_Create An On-Line Price Calculator

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

gatewaygreening said:
How can people interact with an embedded spreadsheet?
I've got an embedded spreadsheet here:
http://www.gatewaygreening.org/our-programs/resources/vegetable-market-price-calculator.html
It's essentially a caclulator, and in it's original form people can type in a vegetable, the poundage, and then see what the market value is. Is there a way to allow people to type into this spreadsheet to produce those calculations with the embedded version?
--------------------------------------------------------
One will not be able to type directly in the spreadsheet. One way to utilize the produce and price data in your spreadsheet is to create a Form through which a user can submit the Produce Hrvested and Pounds Harvested ... then calculations can be performed in the ResponseTable sheet and the results from the last submittal can be presented via a sister sheet that sort the data in the ResponseTable in descending order and displays data for only the last submittal.

In the following I present the Form through which a user submits the data


The data submitted from the Form is logged into ResponseTable where in some additional columns perform the needed calculations as shown in Sheet1 which also uses the data from Produce Harvested and Market Price data as presented in Sheet2. Finally the data from the last submittal only is displayed in Sheet1a. It will need a refresh of the browser for the last submitted data to be displayed in Sheet1a embedded in this blog post.
Sheet1 and Sheet2 can be optionally hidden so that only Sheet1a will be displayed, and if so desired the embedded sheet code can be modified to display only the contents of range A1:E2 without displaying the Spreadsheet title and the sheet number(name).

yogi_Create A Form To Place An Order By Specific Attributes

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

mereh said:
How can a create a form for a clothing company if I want to specify the number of units to order next to each style?
I am trying to set up an order form for a clothing company. I want to know if there is a way to format the form so that a customer can order more than 1 unit of a specific size & style. In google forms it doesn't seem to give me this option as it is only check boxes, multiple choice etc that you can choose to format your answer in. Please let me know if anyone has suggestions on how I could have a style number with sizes and boxes listed underneath where you could order 2 of size small and 5 of size large. thanks!
------------------------------------------------------
In the following solution I used a Form using just only Text style questions ... and then I added a compputed column L in the spreadsheet to compute the Order Amount

In the following, I present the Form that I created ...


and here is the spreadsheet ...

yogi_Set Up Spreadsheet So That New Entries From A Form Appear On The Top

Google Spreadsheet

noratorious said:
How do I set up my spreadsheet so that new entries from a form appear on the top?
I have a google spreadsheet which is attached to a form.
I have made the spreadsheet and form public and embedded them on my Google site so that visitors may input their information which is added to the spreadsheet when they submit (and refresh the page). Visitors can also view the spreadsheet and see what others have added.
I want to be able to alter the settings of how the form inputs new submissions onto the spreadsheet so that new submissions appear at the top as opposed to at the bottom. How do I do this?
------------------------------------------------------------------
In the following , I have named the sheet receiving the data form Form submittals as ResponseTable, and then I created a sheet named sisterSheet which pulls the data from ResponseTable and I use the SORT function to have the data presented in descending order. I can optionally hide the ResponseTable sheet if necessary.

here is a simple Form I had set up for purpose of presentation:
<iframe src="https://spreadsheets6.google.com/spreadsheet/embeddedform?formkey=dGRLaTQzNFJ5QUF0bHN3QmpVanA5S0E6MQ" width="760" height="623" frameborder="0" marginheight="0" marginwidth="0">Loading...</iframe>

and here is the spreadsheet ... wherein I can hide the ResponseTable sheet if necessary

yogi_Calculate Points For Golf League Singles And Doubles Based On Games Won And Games Tied

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

atomictoyguy said:
Calculate Points In a Row Based on Score
I am trying to find a formula that would calculate these two formulas for me.
Situation 1 (Singles Disc Golf League):
Rules are as follows you get 1 point for attending, 1 point for each person you beat, and 1 point for each person you tie.
Situation 2 (Doubles Disc Golf League):
Rules are as follows you get 2 points for attending, 2 points for each team you beat, and 1 point for each team tied. 
---------------------------------------------------------------------

yogi_ Auto Sort Data By Multiple Columns

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Zorkian said:
How can I have a spreadsheet auto sort by multiple collumns?
So, I have a spreadsheet that I work on daily, and I want to have it auto-sort rows alphabetically based on multiple collumns. How would I go about doing this in google docs?
the basic layout is that there are many data sets, with information for each individual organized in a row. the first few collums give some sorting information, and the rest just needs to stay with that information.
any tips?
I guess Ill make a little drawing to demonstrate what i mean
Data1 - Data2 - Data3 - Name -   information
y       y               Alpha    blah
y               y       Bravo    blha
        y       y       Charlie  balh
                y       Delta    bahl
y       y       y       Echo     bhal
y                       Foxtrot  bhla
        y               Golf     hbla
                        Hotel    hbal
and when sorted, I want it to look like this ->
Data1 - Data2 - Data3 - Title -  information
y       y       y       Echo     bhal
y       y               Alpha    blah
y               y       Bravo    blha
y                       Foxtrot  bhla
        y       y       Charlie  balh
        y               Golf     hbla
                y       Delta    bahl
                        Hotel    hbal
the only thing is, the data in the Data1 collumn gets regularly changed, so I want the spreadsheet to autosort (data1 is sort of like a pertinence flag)
------------------------------------------------------------------------
In the following solution, the original data is in column A:E. I have used a formula based approach to recreate the original data in columns G: K in sorted order by column 3 in descending order, by column 2 in descending order, and by column 1 in descending order.