yogi_Auto Update Rearranged Data Received Via Form Responses

Google Spreadsheet   Post  #2385

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

question by: Jasom Maki
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/RAJLVBJGq0k;context-place=forum/docs
Reference cell from form response tab in another sheet

I know there must be a solution to this challenge I am having. I have read quite a few forum threads that get close to solving my issue but I have not found a solution that really works. I know others are having the same issue as me. I am hoping someone can help.

I work in a department that collects event details from other departments so that I can advertise them through various channels. I want to build in some efficiencies to the current process. I created a Google Form where others can submit event details. I would like to populate the repsonses into predefined columns I have in another sheet.

Unfortunately I can not simply reference a cell from the responses sheet because as new responses come in it adds a new row to the responses sheet and does not send that data to the sheet where I want it to be referenced.

In my research I have seen the indirect function which seems to work however i cannot drag the formula across multiple cells. I am not very savvy with formulas. I am happy to provide more detail, please ask. I  think this thread could be very helpful for others if we are able to find a solution. Thank you

my sheet is:

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

and my form is:

https://docs.google.com/forms/d/e/1FAIpQLSdMlikxE1kAtfwujpWpjjbU5nxmloOf-H8gxpOWm0kDDQPcNA/viewform?usp=sf_link 

yogi_Conditionally Format Column B Based on Bands Of Days To Completion

Google Spreadsheet   Post  #2384

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

question by: Mike Surgey
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/RAJLVBJGq0k;context-place=forum/docs
Conditional Format For a Series of Date Ranges
Hi everyone,

https://docs.google.com/spreadsheets/d/1hxAYX7vcInovxauyd4nrX-r-0mEiz2ZjIGdfaUA8Yzw/edit?usp=sharing

I have an order date and a deadline, which is 21 days after the order date.

I would like to use conditional formatting to highlight column C for 4 scenarios.

- days 1-7 a colour

- days 8-14 another colour

- days 15-21 different colour

- all days after the deadline Red

All help much appreciated

yogi_Entries in Column A In Two Columns With Period (.) As The Delimiter

Google Spreadsheet   Post  #2383

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

question by: Joan Sw
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/QLQ0I5yElnM;context-place=mydiscussions
Split function not working right with numbers and the letter E

I have a variety of number/alphanumeric combinations in a column.

See column C below.

I use this formula in Column E: =IF(IFERROR(SPLIT(C262,"."),"") <> "", SPLIT(C262,"."),"")

The column format for C is Number --> Automatic.

For some reason it does NOT like to split properly on the 9E-1.01 values.

What can I do to resolve this?

Thank you.

yogi_Multi-Criteria Count In Sheet1!C8 From Table In DATA!A:C

Google Spreadsheet   Post  #2382

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

question by: stratanancy13
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/jznujQRpC9o;context-place=forum/docs

Counting cells based on specific criteria from multiple cells - help needed

Hello all,

I am stumped on this and really need help.

I have a spreadsheet pulling data into one tab from two other tabs.  I need to count number of cells in column C on the data tab, but only cells that that contain the word February in column C AND in column B contain National (which should be 7).  Spreadsheet example is via the link below.  See the cell highlighted yellow, and the associated comment

https://docs.google.com/spreadsheets/d/1y5acJ6oWVY9zxhWn2KcLcof4q78OCnJIC6NoujOHz7A/edit?usp=sharing

Your help is greatly appreciated!

Thank you!

Nancy

yogi_use QUERY to pull a specific day of the weeks information from a timestamp

Google Spreadsheet   Post  #2381

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

question by: Elgran MacBeth
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/bajgA4L1NE0;context-place=forum/docs

How do I use =QUERY to pull a specific day of the weeks information from a timestamp

How do I use =QUERY to pull a specific day of the weeks information from a timestamp submitted by google forms. That way I can make a Monday-Friday set of cells on another sheet and have the correct information for that day pull into it. I've been using this formula and changing it here or there where needed, but I cannot figure out what I would need to do to make that jump to a more complex formula. 

=QUERY('Master Layouts'!A2:I, "Select *")

I'm very new to using Spreadsheets and have mostly been able to get by with premade stuff I've found online so far. Any help would be greatly appreciated. 

yogi_look up a value in a cell that has multiple values in that cell

Google Spreadsheet   Post  #2380

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

question by: Colby Lehew
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/iFyRXjoR4Jk;context-place=mydiscussions
How to Vlook up a value in a cell that has multiple values in that cell

I have a sheet that records shipping & receiving.  I want to cross reference that all the scheduled orders have shipped. However, multiple orders go out on the same truck.   This means there are multiple orders in the same cell.  It also contains how many cases we received which might complicate the situation. I want to be able to Vlook up individual PO to confirm they were sent out. For example, I want to look up 4323753 ( the 3rd one). Bonus if I can get it to say Pass instead of just repeating the number or fail instead of N.A. Is this possible with multiple numbers in the same cell?  I will want the range to be the entire column as this will be an ongoing process. 

yogi_Compute Sum By Day With Dates In Descending Order

Google Spreadsheet   Post  #2379

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

question by: Peter Kandell
https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!topic/docs/tP-pSgJJ3Jc;context-place=forum/docs
Sum per date

Hi.

Can anyone please help me? I want to sum the values of a separate date. Ex. one day I have 4 amounts I want to sum and another day I have 2 amounts I want to sum. In my example I want a formula that creates this Sums automatically.

Thanks a lot in advance.

https://goo.gl/fEUwSe

yogi_From MASTER Sheet Create Sheet For Each SubCategory by TYPE

Google Spreadsheet   Post  #2378

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

question by: Eliza J
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/2mjCBsjKMfQ;context-place=mydiscussions

Have Categories Sorted Into Subsheet, EXCLUDE based on 1 field

Hi all - 

I have set up this spreadsheet to sort into the Subsheets based on "TYPE" from the master list. I would like a 2nd level of sorting, which would tell it to EXCLUDE any field from the MASTER that has "NO" Listed under "PREFERRED". 

For Instance, only TYPE: "PHOTOGRAPHER" + PREFERRED: "YES" Would sort into the "PHOTOGRAPHER" Subsheet. 

Thank you!

https://docs.google.com/spreadsheets/d/1tJA3foleN9ygoI2_Py2KAReViHvR3BBQ_GHES1apS34/edit?usp=sharing

yogi_Compute Average $ Per Night For Specified Entity From Data For Number Of Days(column C) And Dollars(column H)

Google Spreadsheet   Post  #2377

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

question by: Curt Ranta
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/9LOX3I-sLRo;context-place=mydiscussions
Custom formula to look up value in column, then use adjacent column values to create ave.
Hello, here is my question:

https://cl.ly/3p3O0i2F3Q2n

Here is my spreadsheet:

https://docs.google.com/spreadsheets/d/13KYfWXEbwkZfv-uq36LyHktZ4pUOALtlvEi9kDQW9y4/edit?usp=sharing

yogi_Compute Average Of Hours Worked By WeekDay From Table Of Date and Hours

Google Spreadsheet   Post  #2376

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

question by: KentuckyFriedIdiot
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/sZRKQEZVFeg;context-place=forum/docs
Averaging Hours Worked By Day Of Week

Hi guys,

I've been logging the days I work and the hours worked on each date. I'd like to get the averages for each day of the week. Here is my sheet 

https://docs.google.com/spreadsheets/d/16I3TlgKYG0SJMYEl-_Y8OBTqHjlFiLOmkY3mv_B28CU/edit?usp=sharing

Zeros are days not worked, I'm not sure if those would distort the average and if so, how to filter them out. Thanks so much!

yogi_Conditionally Format Column A by Alternate Day Rows

Google Spreadsheet   Post  #2375

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

question by: BDC_pgh
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/4E0-OhzNw2Q;context-place=forum/docs
Google Sheets - Alternate field color from grey to white, every other day

Hello :-)

Here's the sheet:

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

Right now I am using conditional formatting to achieve the effect I want, but I have it set up to alternate field color from white to grey for only about a week (see image).

How can I have the date fields alternate between grey and white, every other day, indefinitely?

There's got to be an easier way to achieve this than what I have set up currently.

Thank you!

yogi_Rearrange Data In Columns A:D To Data In DataBase Style

Google Spreadsheet   Post  #2374

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

question by: Alexis Matson
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/uja_PxRx9mk;context-place=forum/docs
Automating the reformatting of multiple data points in a single cell separated by commas

Hello,

I would like to know if it is possible to automate the re-organization of multiple pieces of data separated by commas in the same cell.

This is an example of what I am trying to accomplish:

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

Thanks for any help!

Sam

yogi_Conditionally Format F3:F If The Hyperlink Contained '/btscontentindex'

Google Spreadsheet   Post  #2373

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

question by: Neeve
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/o6X2c3j2SdU;context-place=forum/docs
Conditional formatting

I'd like to create a conditional formatting rule where if any of the cells' formula contain 'bts', they will turn red. Basically the same as 'text contains' but i want it to be able to search the formulas, all of which are hyperlinks.

yogi_Troubleshoot And Fix Formula Using SUM Function For Entries in E2:E22

Google Spreadsheet   Post  #2372

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

question by: Graystrickland
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/pnhEECod1w0;context-place=mydiscussions
=Sum() won't sum, but adding same cells (=a1+a2...) works?!?

I have a sheet where the sum function always returns 0, but if I write a manual formula simply adding the same cells (=a1+a2... etc.) then the function produces the correct answer. This means that the source data is *NOT* formatted as plain text. And I've selected all the source cells and made sure they are, in fact, formatted as numbers. Still the =Sum() function does not work. I am stumped. The only reason that I can imagine Sum() not working is when the source data is formatted as plain text, but that's not the case. here. 

yogi_Compute Number And Percent Of Entities In Column H of 'Form Responses 1' Sheet By Gender

Google Spreadsheet   Post  #2371

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

question by: McKay Gillar
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/jW7sAgDsKW0;context-place=mydiscussions

Is it possible to connect answers to certain questions to answers to other questions?

 

I have a survey that is trying to see what teams people support based on where they're from, and I was wondering if there was a way to connect the data. It also asks for gender, and I think it would be interesting to see for example which teams most women supported or something like that. Is that possible?

yogi_Conditionally Format Color Scale Based On Percent Attendance

Google Spreadsheet   Post  #2370

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

question by: Kristi_chat
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/bqbC9444wzU;context-place=mydiscussions

Conditional formatting working on only part of range

I am attempting to add conditional formatting on a range of cells. The sheet I am adding the conditional formatting to is based off a query. The formatting is only working on columns D and E, and doesn't work on columns F, G, or H.

The data that is pulling from the other sheet are largely calculations. What's interesting is the data in columns D&E on the original sheet that is being queried is actually just normal values (no calculations). 

Can you not have conditional formatting on queried cells that have formulas? Or is there a different way to do this?

edited to add sample document: 

Sample doc. 

yogi_Conditionally Format Projects That Are 'Not Complete' And Those 'In Progress' As Of Today

Google Spreadsheet   Post  #2369

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

question by: BabyGazelle
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/nD2MIoKqoeI;context-place=mydiscussions
Conditional formatting based on value in another column
Column A has ETA dates. I have conditional formatting specifying to turn the cell red if the date is past due. However, in Column B, I have data validation with a list of Statuses, and I want it so that if Complete is select in column B then the color fill for the date in column A is white (instead of red). Since it isn't "past due (it's Complete!).

Howe do I do this?

yogi_Conditionally Format Top 5 Values In Range F5:AM5

Google Spreadsheet   Post  #2368

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

question by: Anna Bayliss
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/iOOUWi1WDZE;context-place=forum/docs
Conditional formatting top 5 values

Please can you advise formula to highlight top 5 values in a range on Google sheet

My data range is F5:AM5

I have tried various formulas from online forum's and the google sheet conditional formatting doesn't have the preset of 'Format only top or bottom ranked values' as is in Excel.

Thanks!

Anna

yogi_Create A Summary Of Expenses By Entity Date And Amount

Google Spreadsheet   Post  #2367

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

question by: Smartinez
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/6Mkpp3nA4vM;context-place=mydiscussions

Using SUMIF for Budget

 

Hello. If anyone can assist me in figuring out why this formula will not work or if I am missing something.  I have tried it on a simpler budget and the formula works but I cannot get it to formulate.

We have a budget per school in our 6399 account however within the account every sport has their own budget which we need to track.  The Itemized budget should feed into the monthly expenses budget as it is entered.

Any assistance is appreciated.  https://drive.google.com/open?id=1bYbsg6kR-iByf17FMTe2