With almost universal availability of Internet and the availability of reliable on-line productivity tools, such as Google Docs, individuals as well as companies are switching over to Cloud Computing. In this blog I will post items of interest to my colleagues/patrons/clients.
My sheet keeps sorting the column by month, instead of mm/dd/yyyy. For example:
I want it in chronological order by date, not by the first number. I cannot share the spreadsheet, but here is my formula:
=query(importrange("link","Sheet1!A:AS"),"select Col4, Col18, Col19, Col20, Col21, Col22, Col25, Col26, Col27, Col39 where Col18 contains '1' or Col20 contains '1' or Col21 contains '1' or Col22 contains '1' order by Col39 asc format Col39 'mm/dd/yyyy'",0)
Hi can someone help me. I am trying to create a dependable drop down list in google sheets For example, I have dropdown lists like the ones specified
Column A . Column B Column C . Column D
Apple Fruit Juice $10
Tomato . Vegetable Salad $15
Potato . Vegetable . Salad $15
Chicken Meat Meal $20
Beef Meat Meal $18
Milk Dairy Drink $6
What I would need is if I choose Tomato in column A, The drop down in column B should only show Vegetable Columns C should show only one value Salad and column D dropdown list will have only one value i.e. $15
How do you write a function as a range? In Google Sheets, how do you create a variable range function?
I want to display a data set located somewhere else. So thought to use the INDEX function.
The problem is that I'm creating a template and thus the data set isn't always in the same place. So I thought to write the range for INDEX as an ADDRESS function and a MATCH function. However, no matter what I try I can't make it work.
An example [=index(address(match(J26,B:B),2,4):address(match(J26,B:B)+6,6,4))]
The problem seems to be that a function cannot serve as a variable. The INDEX function returns an error stating that the argument must be a range.
Is there a way to make this work or maybe a better way to get the same result?
How to Consoliadte Multiple Columns into a Single Column
I have a spreed sheet that is a collection of registration information. Each person who registers is allowed to register more than just himself/ herself. Thus I have several columns with names within each registration. I need to create a master registration which is a single column of all the names registered. Is there a way to do this with Pivot tables. I’m familiar with pivot tables but have not been able to extract this master registration list without cut and pasting the information. I have included a sample of my sheet.
Creating a "Query How To" ... stuck on order by Hi All,
I'm creating a short and basic 'how to' guide for beginning query users. Ran into an unexpected issue with 'order by' as it's only working when I have a 'where' included. Please see this Sample Workbook, specifically the 'quick reference' tab and the associated number tab #4.
Feel free to add any simple examples if you've found them helpful. I'm trying to think of the basic ones that are used frequently when beginning to work with query. Maybe add an average or something?
how to generate random list of boolean values with certain distribution of true values In sheets I would like to generate a random list of boolean values, say 100 values, where a certain percentage of those values will be true (ie. approx 60% are True). How can I do this?