yogi_WorkAround For How To Lookup Values Across Multiple Worksheets To Return Sheet Names

Google Spreadsheet   Post  #2582

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

question by: Erin Sublette:

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/y8kWoymOxRA;context-place=forum/docs
How To Vlookup Values Across Multiple Worksheets To Return Sheet Names
I would like to search multiple sheets in a workbook for a name of someone and then in the cell specify the sheet name. Below is a link to my document. 

On the Employee Info tab, I would like column B to reflect the sheet name (department) those workers are in. 

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

yogi_Conditionally Format Column B If Date is Between 0 to 2 2 to 4 and 4 Months Past

Google Spreadsheet   Post  #2581

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

question by: Konrad24:

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

Condition Format

I'm trying to create a sheet that highlights dates in a cell that are in between a specific date range. For example if a date is between 0-2 months of the current date it will highlight the cell green. 2-4 months of current date will highlight yellow and 4 months and beyond red. Help would be greatly appreciated. Thanks

yogi_Count Row By Row How Many Numbers In Each Row Are Smaller Than The Numbers In Row Below

Google Spreadsheet   Post  #2581

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

question by: mooflower:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/GiYRp6j8aGA;context-place=topicsearchin/docs/category$3Ahow-do-i
Count number of cells in a range whose value exceeds a cell value located above it
I'm trying to count the number of cells in a range that exceed the value of a cell located just above it.  CountIf doesn't fit, would one of the database functions do it, such as DCOUNTA?  I could of course add it all up using a ternary IF, but that will be a lot of IF statements for a long range.

Example (comma separated values):

4, 5, 4, 3, 4

5, 5, 3, 3, 5

The number of times the lower range exceeds the value above it: 2   (the first and last entries)

Thanks !

yogi_Compute Row By Row Running Average Of Numbers In Column B

Google Spreadsheet   Post  #2580

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

question by: Viktor Petrovich:

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/rszAV_rtfwE;context-place=forum/docs
daily average

Hi

I have a simple sheet and  which create new row every day with date (A) and daily amount (B) 

Need the formula to put daily average in (C)

appreciate any help with that

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

Clip2net_190120235641.png

5 KB

yogi_Collect Multiple Attributes For Unique Entities

Google Spreadsheet   Post  #2579

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

question by: Niwrutti Sawant:

https://www.blogger.com/blogger.g?blogID=8807805591547673516#editor/target=post;postID=1065445056507297920
Need to collect duplicates with different values

I want to fetch data from different sheets using importrange and want the values of the same data in one cell as mentioned in the sheet.

https://docs.google.com/spreadsheets/d/1GKfeJwUcZrmp1WL0nWEVnIm4SjY9yxbdPpYfwFB-W7E/edit#gid=0

I have a huge database and I want to club the duplicates and add all their respective values in single cell.

yogi_how to subtract only the decimal amount for multiple purchases per user specification

Google Spreadsheet   Post  #2578

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

question by: mystery82:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/qCRsQHxs1wc;context-place=forum/docs
How do I subtract only the decimal amount?

I am trying to create a Budget sheet that puts the remaining change back into my available amount.

I have attached a link below that should explain everything I am trying to achieve there are graphics so it may load

slow.

Example:

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

yogi_Counting number of times a value shares a row with a specific value

Google Spreadsheet   Post  #2577

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

question by: Teksura:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/2dN8LeFhyFY;context-place=mydiscussions
Counting number of times a value shares a row with a specific value.

What I'm trying to do on Front is make C3 hold an ARRAYFORMULA that checks the Run Data sheet for me. I input a Character name in C2, and it should check the sheet and return the number of times the Characters listed in B4:B on Front appare in the same row in the Run Data Sheet.

Got the rest of it worked out on my own, but I'm struggling on this one. Not quite sure what I'm doing wrong.

Edit: sorry, fixed my sharing.

yogi_Using Alternative QUERY And FILTER Functions With Criteria As 'In-line strings' and 'Cell references (from dropDowns)'

Google Spreadsheet   Post  #2576

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-12-2019

question by: Adner Colon:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/6U0oGfKCsQg;context-place=mydiscussions
Hi I need help with if and function in Google Sheets
Hi,

I hope this question finds you all well. I am trying to compare two columns to create a list. However, the two columns may have a different response. For example, in the D column, we may have either three answers; "Yes, I can," "Sometimes, I can work," or "No, I"m not available. The same for column K where we may have either three responses; "I'm nice with it," "I'm alright," or "I' need help." Column C is the name of the workers.

I am trying to use the "if" and "and" function to compare the two columns. The formulas, I have tried are as follow:

=if({d2="Yes, I can",d2="Sometimes, I can work",K2="I'm nice with it",K2="I"m alright",C2,"")

it works, but it gives me workers who not available to work at a certain but who is I'm nice with it.

=if(and({d2="Yes, I can",d2="Sometimes, I can work",K2="I'm nice with it",K2="I"m alright"),C2,"")

but I keep getting errors.

My goal is if D have either "Yes, I can" or "Sometimes I can work" AND if K="I'm nice with it" or "I'm alright" then enter the name in column C., Please advise.

Thank you,

Adner

yogi_Pull Data From Another Tab Based On Any Combination Of Three DropDowns

Google Spreadsheet   Post  #2575

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-11-2019

question by: Kenny Buckles:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/EZ6OBGDrjPI;context-place=mydiscussions
AVERAGEIFS Formula with Data Validation Field Criteria

Is it possible to have an AVERAGEIFS formula where the criteria is pulled from a data validation field.  But have the formula ignore if one of the criteria is blank?

For example - I have Criteria A, Criteria B, Criteria C and in my formula I want to pull the average from a range if it's just Criteria A, or if it's Criteria A+B, or if it's Criteria B+C.  I'm basically trying to provide filtering using data validation for a chart.

Thanks.

yogi_Pull Data From Another Tab Based On Combination Of Two Out Of Three DropDowns

Google Spreadsheet   Post  #2574

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-10-2019

question by: Kenny Buckles:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/EZ6OBGDrjPI;context-place=mydiscussions
AVERAGEIFS Formula with Data Validation Field Criteria

Is it possible to have an AVERAGEIFS formula where the criteria is pulled from a data validation field.  But have the formula ignore if one of the criteria is blank?

For example - I have Criteria A, Criteria B, Criteria C and in my formula I want to pull the average from a range if it's just Criteria A, or if it's Criteria A+B, or if it's Criteria B+C.  I'm basically trying to provide filtering using data validation for a chart.

Thanks.

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

I am not sure how AVERAGEIFs figures in this  -- however following is my proposed solution

yogi_Conditionally Format A3:G If Cells in column E or F Have Time Value > 18:00

Google Spreadsheet   Post  #2573

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-09-2019

question by: TJ G:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/A9DIl4VtPO0;context-place=forum/docs
Conditional formatting with MM:SS value
Hi,

I'm trying to figure out a custom conditional formatting formula. My goal is: I want to highlight an entire row if I enter into a cell a length of time greater than 18 minutes, or 18:00 (i.e. 18:34, 18:45, etc). 

If I were wanting to highlight a single cell, that is easy enough to do in conditional formatting by choosing a cell for my "apply range", selecting the "greater than" option, and entering 18:00 as a value.

But, If I select a row for my "apply range", then choose "custom formula is" and enter something like =$E3>18:00 where I'd expect a value greater than 18:00 being entered into E3 would highlight the entire row, except it claims the formula is invalid. I'm assuming the colon in the time value is screwing things up, but I do not know of any other way to indicate the length of time in the formula.

Any help?

yogi_Compute Sum Of Entries In Column A Based On MultiColumn Criteria

Google Spreadsheet   Post  #2572

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-08-2019

question by: Zach Finch:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/AyVtRAeGmnA;context-place=mydiscussions
How can I find the value of a cell when two factors are combined?
Sorry for the confusing title.

For work I track fraud and we record indicators that an order is bad in Google Sheets along with the value and other criteria. I've been asked to find the total dollar value for all orders that had a risky IP address combined with another bad sign, such as a brand new email address.

How can I search Google Sheets for only instances in which the "Risky IP" cell is combined with another specific cell like "New Email" in the same row?

Thanks!

Zach

yogi_merge data from incomplete list into complete list

Google Spreadsheet   Post  #2571

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-08-2019

question by: MLDHelp:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/-T3GAl9u0wg;context-place=mydiscussions
How to merge data from incomplete list into complete list?
I want to copy information from an incomplete data set from one sheet into another sheet with a complete set. 

In the complete set sheet, column A has each state (AL, AK, AR, etc) and column B has the district number (1, 2, 3, ...). This sheet has every state and every district included. 

In the incomplete set sheet, column A has each state (AL, AK, AR, etc) and column B has district number (1,2,3,...). Column C has the number of funded projects and column D has the amount of funding that district received from the federal government. BUT, districts that didn't get any money aren't listed. 

So how can I copy column C and D from the incomplete set sheet to the complete set sheet and leave the missing districts blank (or better yet, make them 0)?

Thanks.

yogi_Combine Data From Two Tabs And Sort By Columns In Ascending Order For 'Unit' Values In Row 1

Google Spreadsheet   Post  #2570

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

question by: Xander Gustafson:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/Xw2CHSOCyrY;context-place=mydiscussions
how to sort data sets by column

Hello, I have a document with two tabs. the first tab is where i can put new entries and once completed moved over to the completed tab.  what i am trying to do is keep the column of information in tact as is but sort the whole sheet by the unit number. any help is much appreciated. https://docs.google.com/spreadsheets/d/1Pjdl6GkYDtyL3FomsWUSYNBhP9vAjbFc5ZFoMs9gDgs/edit?usp=sharing

Thanks for you time.

Xander

yogi_Conditionally Format Cells A2:I8 Based On DayOfWeek Today

Google Spreadsheet   Post  #2569

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-06-2019

question by: Lauren Edmondson:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/7I8JCC_1iS4;context-place=forum/docs
Formula for Highlighting Row Based off Value in Cell (Day of the week)
Hello,

I have a spreadsheet set up so that the row for the current day of the week is highlighted on that day. So example, if its Sunday, the whole Sunday row is lit up. It worked all of last year but now I can't get it to work, its displaying that its the wrong day (Monday instead of Sunday). I've tried changing a few things but can't get it to work right. Does anyone know a formula to get this working correctly? Here's the one I'm using now:

=weekday(today())=row(A1)

Applied to the range of the cells that have the day of the week in them. 

Thank you!

yogi_Given IDs in Column A Lookup Value For Specified Fields From Another Tab

Google Spreadsheet   Post  #2568

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-06-2019

question by: Garrett M Yates:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/7I8JCC_1iS4;context-place=forum/docs
Quick question about VLOOKUP formula. Please and thanks! :)
Hello All,

I was very graciously provided with a VLOOKUP formula in a previous post to perform the following results on a "test" sheet:

Workbooks:
Master Copy

Slave Copy (1)

Slave Copy (2)

- I am using an IMPORTRANGE from workbook Master Copy to import only some data to each of the two slave copies.  Slave Copy 1 and Slave Copy 2 will therefore be operating slightly different tasks as well as displaying some custom info tailored to each workbook.

- I'm trying to figure out a formula that will be in place for Slave Copy 1, Sheet2, all of Column B (Item Name) that when a value is entered in a cell for Sheet2, Column A (Item Code) it's corresponding value from Sheet1, Column B will then automatically populate in Sheet2, Column B.

However, when I attempted to replicate it on my actual sheet that I wanted to use, I receive an error.  The actual sheet is listed below and the error is taking place on the second tab.

Actual Sheet

If anyone might be able to help with suggestions, that would be gratefully appreciated!

Thanks in advance! :)  

yogi_Compute The Number Of Times An Event Occurs Based On Multiple Criteria

Google Spreadsheet   Post  #2567

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-06-2019

question by: Ryan Epps:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/tilxpnZ1bxI;context-place=mydiscussions
Whats the right formula for adding the number of times something occurs?
I have the following spreadsheet and I wondered if there was a formula to find out the totals of things like, how many Sunday matinees there are, or how many evening performances. I cant seem to find a formula that works, I have tried =COUNTIF(B4:B,"19:30") and others but they never work, would it be because I have used data validation in column B? any help would be really appreciated.

Date	Time		Totals at a Glance
Monday 21 October	Theatre Dark		Sunday Matinee's
Tuesday 22 October	14:00 & 19:30		Weekday matinee's
Wednesday 23 October	19:30		Evening performances
Thursday 24 October	14:00 & 19:30		Total Performances
Friday 25 October	19:30
Saturday 26 October	14:00 & 19:30
Sunday 27 October	Theatre Dark
Monday 28 October	Theatre Dark
Tuesday 29 October	19:30
Wednesday 30 October	19:30
Thursday 31 October	14:00 & 19:30
Friday 1 November	19:30
Saturday 2 November	14:00 & 19:30
Sunday 3 November	14:00