Thursday, December 31, 2015

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
------------------------------------------------------------------------------------

TimestampSelectJob NumberCustomerPart NumberPart DescriptionQuantity removed from InventorySell PriceDate RemovedReason for parts removalJob NumberCustomer NamePart NumberPart DescriptionQuantityBuy Price (Each)Buy Price (Total)VendorLocationDate Received
12/29/2015 9:45:13Inventory RecievedFC5-8829-000Drum Cleaning Blade10R2S512/23/2015
12/29/2015 9:52:55Inventory Removal1512Sir SpeedyFC5-8829-000Drum Cleaning Blade412/28/2015Customer Approved Estimate


Tuesday, December 29, 2015

yogi_From Data In Column F Pull The First Word And Then The Word Between Second and Third '-' Characcters

Google Spreadsheet   Post  #2000
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-29-2015
post by  Gregory Han:
https://productforums.google.com/forum/#!topic/docs/pyL3jHPpLtM;context-place=forum/docs
Concatenate and filter
Hi, I imported a csv into sheet 1 and wanted to efficiently sum up the values for specific names. I had first taken column A and column B and concatenated them into column r using: 

from there in column S i want to filter using select words from column r such as:

livingston united states fitdaily oz rejuvolyte
livingston united states fitdaily oprah rejuvolyte
livingston united states fitfaily oz rejuvanage

into:

livingston rejuvolyte
livingston rejuvanage


what formula can i use in column S
any help would be welcome, thanks!

to be more specific i have included the sheet:
-------------------------------------------------------------------------------------------------------------------------



yogi_Conditionally Format Entries In Column A That Have Been Used More Than Twice In Phrases In Column D

Google Spreadsheet   Post  #1999
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-29-2015
post by  Joel Mitt:
https://productforums.google.com/forum/#!topic/docs/YpsQ09DX4F4;context-place=forum/docs

Highlight a cell if it contains a word from another cell

 Formula should highlight in column "A" the cell with the word I used already in any cell in column D.

Note that column have multiple words in each cell.

Can I explain it better? Who knows? :) If yes, let me know,

Thanks and Best
Joel Mitt
----------------------------------------------------------------------------------------------------------------------------

Monday, December 28, 2015

yogi_Compute Row By Row Running Balance Given Columns Of Debits And Credits In A3:B3 With Starting balance In Cell A2

Google Spreadsheet   Post  #1998
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-28-2015
post by  Tippy P:
https://productforums.google.com/forum/#!topic/docs/_ruk1Caxyt0;context-place=forum/docs

Add Formula to Query()

I have a dataset that looks like this:
debitcredit
34.45
32
12.04
33.07
13.65
0.92
19.6
0.92
23.35
7.32
16.68
25.19
9.47
264
13.59
380.29
13.25
14.85
26.48
14.16
1466.36
35.46

I would like to add a running balance column in a Query() function (OR other solution)
debitcreditbalance
34.4534.45
322.45
12.04-9.59
33.07-42.66
13.65-56.31
0.92-57.23
19.6-76.83
0.92-77.75
23.35-101.1
7.32-108.42
16.68-125.1
25.19-150.29
9.47-159.76
264-423.76
13.59-437.35
380.29-57.06
13.25-70.31
14.85-85.16
26.48-111.64
14.16-125.8
1466.361340.56
35.461305.1

The current Query() function looks like this:
=query(A2:B24,"select A, B", -1)

How do I add the running balance column?
=query(A2:B24,"select A, B, C1-A2+B2", -1)

AND/OR include a Function in the Query() Function results?
=query(A2:B24,"select A, B 'SUM(C1,-A2,B2)'", -1)

Is any of this possible within the Query() Function AND/OR other solution?

Thanks,
 -----------------------------------------------------------------------------------------