yogi_Conditionally Format DashBoard!G1:G15 If Attendance!G2:G Does Not Have Today's Date

Google Spreadsheet   Post  #2441

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-30-2018

question by: Zatin
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/Szwi02eSqek;context-place=mydiscussions
Conditional Format Column based on Dates in Another Tab
Hi,

In DashBoard!G1:G15 range I need to apply conditional format.

Click here for Sample Sheet

Condition:

If in Attendance!G2:G range <> Today() date. 

Dates are in dd/mmm/yyyy (27-Apr-2018) or (27/04/2018)

ISSUE DESCRIPTION:

What I plan to achieve is if today's date is missing from the Range G2:G of the Attendance sheet, conditional formatting should be applied on a different Tab (DashBoard) of the same spreadsheet. The range for the formatting to be applied is G1:G15.

The sample sheet shows the intended results.

I managed to come up with the below custom formula but it fails to work.

=INDIRECT(CONCATENATE("Attendance!G:G",COLUMN()))<>Today()

Please Help.

Thanks!

Zatin

yogi_Count Service Calls For 'This Week' 'Last Week' 'This Month' 'Last Month' For Referenced Date

Google Spreadsheet   Post  #2440

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-30-2018

question by: DallasJosph Helou
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/AgbBXkVczb4;context-place=mydiscussions
Trying to count cells in a column today Between a time range
Hello, 

I am trying to count cells in a column for today between a time range like 08:00:00 and 16:30:00. 

I have managed to find the formula for  to count cells for today. How do add a time period? 

=COUNTIFS('Form Responses 1'!A:A,">="&TODAY(),'Form Responses 1'!A:A,"<"&TODAY()+1)

I am also looking for a way to count Cells for:

• this week
• this month
• last week

Any help would be greatly appreciated. 

yogi_Sort (Rearrange) Sheet1 By Columns C to O In Ascending Order

Google Spreadsheet   Post  #2439

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-29-2018

question by: skelem
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/khbT-CVd2fs;context-place=mydiscussions

How do I sort the column headers?

My spreadsheet has outgrown the original header organization, and I would like to sort the columns alphabetically by the column headers (row 1), and have all the data in the other rows remain associated with their headers.

When I select a dozen columns, and select Data, the options are to sort the rows based on the data in one or more columns. Not what I need. If I select Data->Sort Range, it will sort the rows based on the data in one or more columns. Still not what I need.

Here's a link to my Google Spreadsheet. I want to sort columns C through O aphabetically by their header, which is in row 1.

yogi_Create a running total based only on the most recent lowest number

Google Spreadsheet   Post  #2438

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-29-2018

question by: Sugar-Wookiee
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/udf9j-AD2zM;context-place=forum/docs

How to create a running total based only on the most recent lowest number?
Hey guys, 

yogi_Record Value Of A Changing Entity By Date using A Formula Based Approach

Google Spreadsheet   Post  #2437

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-29-2018

question by: Maxime Biver
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/HgYJsGPqCcM;context-place=mydiscussions

Spreadsheet, auto filing process according to the date
Hey guys, 

I'm struggling a bit with gSheet :

I have a value in cell, lets say A1 that is changing often, its the value of my port folio.

I would like to do another spreadsheets that is picking up the value in that celle (A1) every day for example and fill the cell B1 on the 1st of may, then B2 on the 2nd of may etc etc

How can I do that ? I was trying with IF and NOW but it looks like they don't go together and I'm not able to auto copy and paste the value in cell A1.

Thank you for you help !

yogi_Auto-populate table with dynamically-generated row duplicates

Google Spreadsheet   Post  #2436

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-28-2018

question by: Thibaut Delarbre
https://productforums.google.com/forum/#!topic/docs/XSbRtuAp_Yc;context-place=forum/docs

Auto-populate table with dynamically-generated row duplicates
Hi everyone,

I'm trying to automatically create rows in a table (n°2) based on a "row template" defined in another table (n°1).  In table n°1 I define 3 variables that are used to create the "copies"  

1) Start date: The date of the first copy

2) Cycle: Used to define the date of the following copies. For example if Start date = 1-Jan-2018 and Cycle = 1 month, date of the following copy should be 1-Feb-2018

3) Repeat: Number of row copies. 

I've made the example spreadsheet, which includes the expected output "hard-coded", i.e without formulas. It would be great 

if you could add the formula for the dynamically-generated output in table n°3. Thank you!

EDIT 25-April:  I've found at this thread which gives great inspiration and used it to come with a solution in sheet "Solution1". However that solution has several shortcomings which I would love your help to fix! 

a) I had to use several inelegant "support" columns

b) These extra columns don't auto-expand with arrayformula, meaning I'll have to drag and drop every now and then

c) It's using vlookup, which I don't particularly like because the columns indexes in {1,2,3}. are "hardcoded" and won't update dynamically if I move columns around

yogi_From Sentences In Column A List Each Unique Word (case sensitive) And Its Frequency

Google Spreadsheet   Post  #2435

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-28-2018

question by: Simott Kwilinski
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/iggDEzgkjqI;context-place=mydiscussions
Counting all words in a column

I have a very large column (A) of sentences, with varying punctuation, capitalization, etc. It is around 20,000 rows and 150,000 words long, meaning any manual solution is impossible. I'm basically trying to produce a list of these words. I'm not sure if this is possible in sheets at all, but if so I would like either a cell formula or a script.

I would like to create a list of every word used in this column, and put it in column B. So B:B would be a list of words such as "the", "and", "hello", "world", etc. Each word should only appear once in column B. In column C, I would like a tally of how many times each of these words are used (I already have a formula for counting word usage, I just need something to produce a list of words).

For my purposes, a word is any string of characters separated by spaces, punctuation or the beginning/end of a sentence. So " apple ", " wqehotasjclfkadsnqjlgea ", and " hello;" are all words. Having the formula be case insensitive would be preferable, but not required.

yogi_Create A List Of Random Numbers Between 1 and 11 With 80 Percent Between 1 and 10 and 20 Percent of 11s

Google Spreadsheet   Post  #2433

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-27-2018

question by: _Drew C
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/n5pfPqzAAHc;context-place=forum/docs

Random number generator with known group results

I need to generate a random number between 1 and 11 for hundreds of cells while capping the number of each value.

I.E. If I need to sort 1000 individual units, I want values 1-10 to have 80 instances and 11 to have 200. Is this possible?

yogi_Extracting a sum amount from dates with unequal #s of cells

Google Spreadsheet   Post  #2432

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-27-2018

question by: Adam Dufour
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/WnDqAPWkusA;context-place=mydiscussions
Extracting a sum amount from dates with unequal #'s of cells

I manually assign a date to my spreadsheet for everytime I use it. 

As you can see in this photo, I put in the "Date" on the A column, and the space between days varies a lot. I have a conditional formatting on A.

I am trying to get the sum of "G" (Total Profit) per day. I'm guessing I'll need to use an IF function, but I have no clue how to tell it to only select the G values within that day.

Thanks for the help! :)

FOR CLARIFICATION:

I've taken Yogi's advice and made a mock spreadsheet with a few clarifications. Here's the sheet: https://docs.google.com/spreadsheets/d/1VZg1Iln1VCFiHoWCXkhLz3p4_T5t1OMAgtoPQyb7j8c/edit?usp=sharing

a) What I'm trying to accomplish: Automatic sum of values in the G column of "Sheet A" according to the day they correspond to. I've highlighted the values in G with different colours to show which values I am hoping to sum up as one day.

b) In which cell/sheet? The sum needs to be in "Sheet B" at C1, C2, and C3 according to the three days they are labelled with.

c) Show us your expected result. In "Sheet B", there are values labelled with "Expected results for C1/C2/C3:" to show what the sum should be. I got those values from adding up the values in the G column according to their days.

Note: I know I can do =sum('Sheet A'!G1:G5') to get a sum of those values in a different sheet. What I'm looking for is an automated function that'll update for each day, I was guessing I'd need somekind of IF function. 

Thanks a ton!

yogi_LookUp And Put In F2:F21 'Acc/Hour Goal' Values From Q2:Q12 For 'Home Store' Listed In A2:A

Google Spreadsheet   Post  #2431

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-27-2018

question by: Matthew Santibanez
https://productforums.google.com/forum/#!topic/docs/45FzoZSqSf8;context-place=mydiscussions
What formula would resolve taking information from a column and putting a preset?

In this example I am trying to to make the table take the suffix (RB) in Column A and translate that to a designated answer in Column F to $3.45. This is my first complex formula. I can say I have had no luck at this over the last few months I have been playing with this. I am also willing to share the sheet.

yogi_Highlight Duplicate Entries In Column A

Google Spreadsheet   Post  #2430

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-20-2018

question by: Camille De la Pena
https://productforums.google.com/forum/#!topic/docs/45FzoZSqSf8;context-place=mydiscussions
Highlight Duplicate

Hi,

Can anyone help to have the formulas for conditional formatting where all with same code in a column will be highlighted. Please with a sample sheet.

Thanks.

https://docs.google.com/spreadsheets/d/1w3vGoOJiL-MHtRM7DDLhJO8xeoV53vumaQ1xvoKXYsc/edit?usp=sharing

yogi_Rearrange Data of Events and Participants Into Table of Events By Participant And Gender

Google Spreadsheet   Post  #2429

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-16-2018

question by: jpcsoccer
https://productforums.google.com/forum/#!topic/docs/SiaYzZt9CPI;context-place=forum/docs

I would like to list every row that a name appears in

 I have a spreadsheet to organize a track meet.  it goes

Event1  | name 1 | name 2

Event 2 | name 1 | name 3 | name 4

Event 3 | name 2 | name 5

I would like to produce this result

name 1 | Event 1 | Event 2

name 2 | Event 1 | Event 3

name 3 | Event 2

name 4 | Event 2

name 5 | Event 3

the sheet is attached.  would love some help!

yogi_Sort A Column Of Dates In M/D/YYYY Style By Day In Ascending Order

Google Spreadsheet   Post  #2428

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-17-2018

question by: Sabrina3241
https://productforums.google.com/forum/#!topic/docs/P-F5jAm0waA;context-place=forum/docs
Sort by a column that has mm/dd/yyyy - chronological, not by first digit

My sheet keeps sorting the column by month, instead of mm/dd/yyyy. For example:

3/31/2019

3/31/2019

4/1/2018

4/11/2018

4/15/2019

4/15/2019

4/30/2019

5/1/2018

5/1/2018

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)

yogi_Rearrange Data of Events and Participants Into Table of Events By Participant

Google Spreadsheet   Post  #2427

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-16-2018

question by: jpcsoccer
https://productforums.google.com/forum/#!topic/docs/SiaYzZt9CPI;context-place=forum/docs

I would like to list every row that a name appears in

 I have a spreadsheet to organize a track meet.  it goes

Event1  | name 1 | name 2

Event 2 | name 1 | name 3 | name 4

Event 3 | name 2 | name 5

I would like to produce this result

name 1 | Event 1 | Event 2

name 2 | Event 1 | Event 3

name 3 | Event 2

name 4 | Event 2

name 5 | Event 3

the sheet is attached.  would love some help!

yogi_Rearrange Row By Row Combined Entities In Column B By Date In Column A

Google Spreadsheet   Post  #2426

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-16-2018

question by: Captain Chief
https://productforums.google.com/forum/#!topic/docs/Z6EFxr7lEXQ;context-place=forum/docs

I would like to join text from cells in B into a single cell but ONLY IF it matches a part

ADD A REPLY

yogi_From A DropDown List Of Categories Choose Item Selected And Associated Product And Price

Google Spreadsheet   Post  #2425

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-14-2018

question by: Arun K Narayan
https://productforums.google.com/forum/#!topic/docs/71JKWLCZX1U;context-place=forum/docs

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

Is this possible. If yes please help.

yogi_Create A Template For Reporting Data By Specified Month Beginning With Monday In Which First Of The Month Falls

Google Spreadsheet   Post  #2424

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-14-2018

question by: Clark1
https://productforums.google.com/forum/#!topic/docs/lW4V_u7ipDY;context-place=mydiscussions

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?