yogi_Compute Sum Of Hours For Persons By 'Name' And 'Surname' From Specified Date (say TODAY) Onward

Google Spreadsheet   Post  #2532

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

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

Advanced SUMIF taking into account dates
Hello!!
As a continuation of the following post: https://groups.google.com/a/googleproductforums.com/d/msgid/docs/8763004d-6d90-4574-86a8-498021b955c4%40googleproductforums.com.

I'm trying to do SUMIFs across a whole sheet with two cells conditions.

This is the timings of people on day by day basis, and I want to get a summary within that spreadsheet that calculates the timings for each individual (they have name and surname across two different cells) but now I would like for the QUERY function to take into account the current date (e.g. A16:C22).

e.g. https://docs.google.com/spreadsheets/d/1T8p0VZ1VhTBA72LNTMwyYujy1o6WsDV5r_AW_lAmVQ8/edit?usp=sharing

Ben Liebrand mentioned is not a good idea to use merged cells, but how would you design it?

Also, Would it be possible to show the sum values but also add a breakdown of the hours showing the dates (e.g. A8:E13).?

Can anyone help?

Cheers!!!

yogi_Compute Sum Of Hours For Persons By 'Name' And 'Surname' From Specified Date Onward

Google Spreadsheet   Post  #2531

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

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

Advanced SUMIF taking into account dates
Hello!!
As a continuation of the following post: https://groups.google.com/a/googleproductforums.com/d/msgid/docs/8763004d-6d90-4574-86a8-498021b955c4%40googleproductforums.com.

I'm trying to do SUMIFs across a whole sheet with two cells conditions.

This is the timings of people on day by day basis, and I want to get a summary within that spreadsheet that calculates the timings for each individual (they have name and surname across two different cells) but now I would like for the QUERY function to take into account the current date (e.g. A16:C22).

e.g. https://docs.google.com/spreadsheets/d/1T8p0VZ1VhTBA72LNTMwyYujy1o6WsDV5r_AW_lAmVQ8/edit?usp=sharing

Ben Liebrand mentioned is not a good idea to use merged cells, but how would you design it?

Also, Would it be possible to show the sum values but also add a breakdown of the hours showing the dates (e.g. A8:E13).?

Can anyone help?

Cheers!!!

yogi_From 'Form Responses' Sheet Pull Purchases And Assign Invoice No To Purchases

Google Spreadsheet   Post  #2530

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

question by: sanjeev kumar sharma
https://productforums.google.com/forum/#!topic/docs/SavI6EY_qCg;context-place=mydiscussions

Need Help With Google Sheets.

example sheet:

Columns:

A.                       B.                                C.                                D.

cat                      Dog                            Animal                        10001

 

A2                       B2                                C2                              D2

cattt                      D000ogg                    Unknown                    Keep it Blank

A3                       B3                                C3                              D3

cat                      Dog                            Animal                        10002

A4                       B4                                C4                              D4

cattt                      D000ogg                    Unknown                    Keep it Blank

A3                       B3                                C3                              D3

cat                      Dog                            Animal                        10003

I want the Formula to automatically generate the code for "D" Based on "Text key work on column C"

yogi_Pull Latest Data From Specified Fields For Entities In A2:A13

Google Spreadsheet   Post  #2529

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

question by: sanjeev kumar sharma
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/3Lq0kAMoIS8;context-place=forum/docs

automatically pick up details

Hello to all experts. I am again here with a new query. The query is that in warp status sheet, in column a, 3 to 16 are machine numbers. 
And in coumns g:k are their details. I want that in column a each machine number should pick up the last detail of automatically. 

For example-- regarding cell a2 (machine no. 3) the last detail is i21:k21 (yellow coloured area) (cotton 1150 10/27/2018). 
And so on for every machine nhttps://docs.google.com/spreadsheets/d/154uZpHNsenoDKe8aYXz4nRqolE_jRsBUUMSF5v3ZhBc/edit#gid=1999752622umber.

yogi_Conditionally Format D2:D For Past Due Within 15 Days And Within 5 Days

Google Spreadsheet   Post  #2528

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

question by: Anthony Coates
https://productforums.google.com/forum/#!topic/docs/1J2QLZ1QZSc;context-place=forum/docs

Payment Tracker due date conditional formatting
Hi,

Looking for some help with regards to conditional formatting, I am setting up a sheet to track payments, however I am having trouble with conditional formatting, I've googled but can't seem to find what I am looking for..ill try to explain what I want to do, below.

So I have a column (D) that contains the payment due dates (this date could vary depending on circumstances), what I want to do is -

15 days before the payment due date have the cells highlighted yellow

5 days before the payment due date have the cells highlight amber / orange

If its after the due date, then highlight red

However if the value of a cell corresponding cell in column E is yes, then highlight the cell green (So if the due date is in D2 and the value Yes is in E2 then D2 highlights green and it ignores any of the other formatting mentioned above,

A link to the document can be found here -

https://docs.google.com/spreadsheets/d/15s2GEtaN-3z2KKMVmqqUgCKQ9mVEufqUqClIw031i4Q/edit?usp=sharing

yogi_High Value Of A Stock In A Specified Period Of Time

Google Spreadsheet   Post  #2527

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

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

Regarding help for spreadsheet formul
Hello,

I have a formula below which works perfectly but need some modifications in the formula below:

=query({googlefinance(A1,"high",today()-180,today())},"Select Max(Col2) Label Max(Col2) ''",0)

I want 180 days back to start from specific date and not from today. So the 180 days should start from suppose 1st August.

Kindly Help.

yogi_ Multi Creiteria Lookup Involving Rows And Columns

Google Spreadsheet   Post  #2526

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

question by: K. Young
https://productforums.google.com/forum/#!topic/docs/0CWUz2d_ypA;context-place=forum/docs

Lookup IF function across rows and columns- is this possible?

I need a formula that will find data from a sheet based on 3 different factors. Those factors are on rows and columns. There isn't a way to get them all in columns or rows- they have to stay where they are. 

I'm not sure if a query can do this or an array function, but the lookups are working because they can only do vertical or horizontal. not sure the best way to approach and would love some input. 

Example Doc: 

Formula in Cell D2 performs a lookup on sheet "Ranges" with the following contingencies:

(if(Ranges!A:A=C2,if(Ranges!1:1=B2,if(Ranges!2:2="50th")))

Current says "NA" because I don't have the right formula. Should return "85"

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

yogi_Rearrange Data In 'Address' Column And 'Cars' Column Of Goals Sheet Per Specification

Google Spreadsheet   Post  #2525

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

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

Query and limit output
Hi all it's difficult to explain so I appreciate any input. I'm trying to query data from one sheet and only show the output once. In sheet/tab "Goals" I have a client name listed a few times and want to query data from another sheet based on that name. However, I only want the data to appear once and then look for the next unique name skipping down to that row.   

I know I can query and have the output populate a list, but that list is aligned with the source data. I have an example sheet that shows the idea, what currently happens with a regular query based on the client name and finally, what I hope it can look like. 

Here is a link to the sheet. https://docs.google.com/spreadsheets/d/17OTd6oPV0Y84qPvo_K7AsCPMyHcapkq77SorIlOWDUo/edit?usp=sharing

Thanks for any ideas/input.  

yogi_Conditionally Format Cell B4 For Specified Values Of B4/B2

Google Spreadsheet   Post  #2524

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

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

How to color a cell based on value percentage?

I have a table that shows:

row 1 the expected spending on a given period, 

row 2 the actual spending in an given period 

row 3 the remaining resources on a given period. 

I've managed to format the cell in row 3 to highlight by color if the amount reaches a certain value. (ex more than 50 is green, less than 50 or 0 is yellow and lower than 0 is red.)

is it possible to format the cell in that way that the color changes if the percentage of row 1 is reacded? (Ex more than 12% is green, between 12% and 0% is yellow and lower than o% is red).

Example

yogi_Compute Years Months Days Since Member Join Date 1

Google Spreadsheet   Post  #2523

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

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

count from date - teammember since date
Hello.

I use Google Sheets for my team.

There is a list of all teammembers.

I want to make that:

User1   |    joined on 10-09-2018   |   teammember since X years, X months, X days 

How can I make the "teammember since"-feature?
With =DATEIF it doesnt work.

sry for my bad English.

Thank you.

yogi_Compute Years Months Days Since Member Join Date

Google Spreadsheet   Post  #2522

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

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

count from date - teammember since date
Hello.

I use Google Sheets for my team.

There is a list of all teammembers.

I want to make that:

User1   |    joined on 10-09-2018   |   teammember since X years, X months, X days 

How can I make the "teammember since"-feature?
With =DATEIF it doesnt work.

sry for my bad English.

Thank you.

yogi_Return a value from a cell in 1 column only if 2 other cells are consecutively 0 then 1

Google Spreadsheet   Post  #2521

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

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

Return a value from a cell in 1 column only if 2 other cells are consecutively 0 then 1

I have a column (let's call it column E) where each cell in the column is a different time (i.e. 10:44:00 AM, 10:46:00 AM, etc...). I have another column (let's call it column F) where each cell in the column is either a 1 or a 0. What I want to do is get a return from column E only if 2 consecutive cells in column F are specifically a 0 and then a 1. For example:

F3 & F4 would give me E4

F15 & F16 would give me E16

Those would be the only values that I would get back from the columns provided in the above screenshot. Thanks in advance!