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

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

yogi_Update Inventory Based On Qty Removed and Qty Received From 'Form Responses 1' Sheet

Google Spreadsheet   Post  #2001

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-31-2015

post by: nischal prasad:

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

i am creating google spreadsheet for inventory

Hello 

I need help with inventory of all my copier/printer parts

i have a form designed in google forms that gives me response data in a spread sheet 

now my problem is this

the form has 2 sections 

1. Inventory received 

2. Inventory removed (for service calls etc)

when we receive inventory we select the Inventory received section and  the form takes us to that area where we enter al the details of the parts received

and when we remove parts for a service call we go to the Inventory removed section and take out parts and fill out the details 

so the spread sheet generated is like this below

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

Timestamp

Select

Job Number

Customer

Part Number

Part Description

Quantity removed from Inventory

Sell Price

Date Removed

Reason for parts removal

Job Number

Customer Name

Part Number

Part Description

Quantity

Buy Price (Each)

Buy Price (Total)

Vendor

Location

Date Received

12/29/2015 9:45:13

Inventory Recieved

FC5-8829-000

Drum Cleaning Blade

10

R2S5

12/23/2015

12/29/2015 9:52:55

Inventory Removal

1512

Sir Speedy

FC5-8829-000

Drum Cleaning Blade

4

12/28/2015

Customer Approved Estimate