yogi_Pull Row By Row Matching Items By Month And Category From Import Sheet

Google Spreadsheet   Post  #2008

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-05-2016

post by: smithbc:

https://productforums.google.com/forum/#!topic/docs/P6tiI5QsNZE;context-place=forum/docs
Index Match Multiple Criteria (rows and columns) help

Here is a sheet with my desired results: 

I'm trying to write a formula that would go in the "Forecast" Sheet  that pulls from the "Import" sheet. 

The import sheet is copy and pasted from an accounting system report. 

The forecast sheet is a budget sheet where the budgeted numbers get updated each month with the actuals from that month, but the rest of the columns remain budgeted amounts. We are trying to find a formula that could update these automatically by matching the account name and the month of the data regardless of the format of the accounting system report.

I would like the formula to match on the account name (i.e. Sales, Travel, Office Supplies, etc.) and the month. The accounting system report puts the months in reverse order, and we would prefer not to have to rearrange these each time. 

Any help is greatly appreciated.

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

yogi_Compute Row By Row Running Total For CheckBook Register Where New Transaction Is Posted Above (Rather Than Below) Last Transaction Row

Google Spreadsheet   Post  #2007

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-04-2016

post by: OneBinary:

https://productforums.google.com/forum/#!topic/docs/XnDCCLsWOaI;context-place=forum/docs
How to utilize ArrayFormula to auto-fill formula on new row insert on check book register?
Hello,

I've been doing a ton of reading, but I'm having trouble figuring out how to utilize the ArrayFormula function to automatically fill in a formula when a new row is inserted.

I'm trying to develop a check book register, but one key difference is I want to insert new entries at the top (so that my current balance is always at the top and old transactions get pushed down).  For the Balance field I'm calculating that by: (Previous Balance - New Transaction = Current Balance).  (If a deposit occurred, I enter New Transaction as a negative number).

Now every time I want to insert a new transaction I want to insert a new row at the top and have the Balance formula auto-populate, however for the life of me I can't seem to get it right due to the circular reference error when referencing 'Previous Balance'.

Is anybody able to help me on this?

Check Book Register

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

yogi_For Every 2 Working Days Delay From Date Requested Enhance Priority Level By Numerically Reducing It By 1

Google Spreadsheet   Post  #2006

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-03-2016

post by: chale111111:

https://productforums.google.com/forum/#!topic/docs/Q1NxSMHGAhE;context-place=mydiscussions
How to automatically update my priority level with each passing day?

I created a Request for Information (RFI) sheet which prioritizes from critical(0) to low priority (4). The question that I have is, if an RFI is sent to a subcontractor or the client as a low priority, but if they don't respond in a timely manner, the RFI is now becomes urgent, how can I have it update automatically in the spreadsheet without being a manual process? Is there a macro I can use or will I need to dust off my programming skills, or lack thereof?

For example, say an RFI is submitted to the client with a priority level of 2 and after 3 days of no response, it's going to impact the tight schedule and it should now escalate to level 3. How can I have it automatically up it to level 3 after 3 days without manually entering it myself? Hope that makes sense. 
----------------------------------------------------------------------------------------------------------------

yogi_Split A String Consisting Of Several Sets Of Entries Into Their Respective Set In Columns Separated at ZipCode For Each Set

Google Spreadsheet   Post  #2005

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-02-2016

post by: Daniel Gyimah:

https://productforums.google.com/forum/#!topic/docs/BO_crjos6l4;context-place=forum/docs

Split comma delimited text in one cell into multiple columns and rows
Hi,

I have a string of text separated by commas in one cell, for example:

A1: George, Michael, 49Yrs, Musician, 6ft2in, 240lbs, Bos, 01616, Tony, Douglas, 32Yrs, Boxer, 5ft11in, 145lbs, Bal, 21228, Sam, Mitchell, 46Yrs, Accountant, NY, 10475

I want to separate it so it looks like this:

  A B C D E F I J

George Michael 49Yrs Musician 6ft2in 240lbs Bos 01616

Tony Douglas 32Yrs Boxer 5ft11in 145lbs Bal 21228

Is there a function or code I can use to split the text maybe after the 8th comma, and then I can transpose the data into rows?

Anything will help.

Thanks

Daniel

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

yogi_Compute Number Of Instances Of Leave For Each Person Listed In D2:I2 For Time period Selected In Cell A1

Google Spreadsheet   Post  #2004

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-02-2016

post by: Sricharan P:

https://productforums.google.com/forum/#!topic/docs/7vMWnouBdso;context-place=mydiscussions
Using SUMPRODUCT + SUBTOTAL + OFFSET with Google Spread Sheet

hello every one,

My task is to accomplish Count text based on Criteria (Countif) for only filtered Range

For that i tried Subtotal Function. But, Subtotal Function doesn't host Count if

As a substitute i found following formula

SUMPRODUCT(SUBTOTAL(3,OFFSET(F$2:F$2016,ROW(F$2:F$2016)-ROW(F$2),0,1)),--(F$2:F$2016="Leave"))

it works perfectly in Excel 2013

but, when the same is placed in Google Spread Sheet it shows the error as 

"SUMPRODUCT has mismatched range sizes. Expected row count: 1. column count: 1. Actual row count: 2014, column count: 1."

and returns the value as "#VALUE!" 

is there any means by which i can accomodate this formula into Google Spread Sheet. 

or else is there any substitute to this formula to place it in google Spread sheet.

thank you very much.

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

yogi_Create A DropDown List From Stock Items In A:A Based On Specified Search Criteria

Google Spreadsheet   Post  #2003

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-02-2016

post by: emacrisema:

https://productforums.google.com/forum/#!topic/docs/jyeAVREGN4s;context-place=mydiscussions
Create dropdown options from a single cell's content ?

Is it possible to do that ?

I need the letters in A1 or B1 to become options in a dropdown WITHOUT using split.

I tried to use split but it's simply not feasible.

I  have thousands of rows with hundreds of lines in each cell .

https://docs.google.com/spreadsheets/d/1T7VNgJrF1yr7HKeV-34xiKRyGoScyuxqdfBnleVU4HA/edit?usp=sharing

I saw there is an option for custom formulas but even if I could get it to work the option for dropdown is disabled under custom formula.

Thanks :)

yogi_Delineate In Cells Of Columns G:I 1 For Driver With Passengers And 0 For Driver With No Passengers

Google Spreadsheet   Post  #2002

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-01-2016

post by: Patrick Whittick:

https://productforums.google.com/forum/#!topic/docs/TZHZaa1jL_c;context-place=forum/docs
Forcing a cell to show a maximum number

Hi,

I'm trying to set up a basic spreadsheet to use with a couple of friends for car-sharing.

What I'm wanting to do is, I think, pretty straight-forward ('though not to my brain!) - where it finds a number (derived from a formula) greater than 1, it just displays a 1.

I've added some notes to my spreadsheet, and would be grateful for any tips!

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

TIA,

Patrick

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