yogi_Pull Select Data In Multiple Sets From Another Sheet Based On A Specified Criterion

                                          Google Spreadsheet   Post  #1090
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 31, 2013
user Stephen Seattle1 :(http://productforums.google.com/forum/?zx=h0o3wpzxfaq#!category-topic/docs/spreadsheets/o-FoyenUc1Y)
IF formula extracting data from another sheet

I have multiple sheets within one spreadsheet and I am trying to use formulas to extract data from one sheet to another based on the date of a transaction.  I’m stumped in figuring out the formula needed.

My spreadsheet can be found at the following link:

https://docs.google.com/spreadsheet/ccc?key=0AqB-QCD3CXigdDJLZkphMnZYLVJQbFZtdHpER1pSdkE&usp=sharing

On the “Press Orders” Sheet, I am looking to import data from the "Sales Data" sheet to populate the columns based on the date that I enter in cell A4. You will note that I already entered a formula in the other date cells A12, A20, A28, and A36. The rows under each date section should be populated based on a match of the same date located in the “Sales Data” sheet under columns K and M.  For example, for the date 3/2/2013, there should be 2 orders populating just two of the rows in the “Press Orders” sheet under the 3/2/2013 heading (Mila Adamova and Amanda Adams, since they have 3/2 press dates listed in column K in the “Sales Data” sheet).

 So, based on the dates listed in columns K and M in the “Sales Data” sheet, I am then trying to do the same import process for the other sections (dates) on the “Press Orders” sheet (there are 5 date sections starting on rows 4, 12, 20, 28, and 36).

For each "Press Orders" sheet column, I am trying to import the data as follows.  Note all this data is from the same row/transaction in the “Sales Data” sheet:

1. Name – imported from column A in the ‘Sales Data’ sheet

3. Inv # - imported from column C in the ‘Sales Data’ sheet

4. Cleanse Type - imported from column E in the ‘Sales Data’ sheet

5. Pack Details - imported from column F in the ‘Sales Data’ sheet

6. # of Bottles - imported from column H in the ‘Sales Data’ sheet

7. Current Order Comments - imported from column G in the ‘Sales Data’ sheet

8. Shipping Method - imported from column I in the ‘Sales Data’ sheet

9. Shipping Address - imported from column Y in the ‘Sales Data’ sheet

10. Phone - imported from column U in the ‘Sales Data’ sheet

Can someone point me in the right direction?  If this doesn’t make any sense, please let me know and I can try and explain it better.

Thank you!

Stephen

---

I tried the following formula and it didn't work:  =ArrayFormula(IFERROR(QUERY(IF({1,1,1,1,1,1,1,1,0,1};'Sales Data'!A3:J;IF(INT('Sales Data'!K3:K)=A4;'Sales DATA'!L3:L;IF(INT('Sales Data'!M3:M)=A4;'Sales DATA'!N3:N;-1)));ʺselect Col1, Col10, Col3, Col5, Col6, Col8, Col7, Col9,Col24, Col20 where Col8 > -1ʺ)))

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

following is a solution to the problem

yogi_Pull Names From Column A That Do Not Have in Column B Text Pattern Specified In Cell C6

yogi_Create A MasterList of All Active Jobs From All Of The Yearly Lists

                                          Google Spreadsheet   Post  #1088
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 20, 2013
user Kyle Korte :(https://productforums.google.com/forum/#!category-topic/docs/presentations/how-do-i/desktop/1QDc8jNqO5k)
Windows 7 ; Chrome

Ok here it goes.  I have created a job list in a workbook with different sheets for each year the job started in.  I then have a master list that I want to pull in all of the active jobs from each of the year sheets.  I use this master list with an import function to feed other workbooks that need a drop down list of all current jobs (time cards, to do lists, ect).  I am having a problem figuring out how to get information from multiple sheets to popluate the active list.  I am pretty sure Vmerge is the way to go but I cant seem to find the right characters.  The last line I attempted was 

=VMERGE(filter ( '2013'!B3:B; ('2013'!E3:E = "In Progress") ));filter('2012'!B3:B('2012'!E3:E:"In Progress"))

here is the link to a dummy sheet that illustrates what I am going for:

https://docs.google.com/spreadsheet/ccc?key=0Aha7705d6i9BdGlwMFQ5WG1OcnFjUUpyRTlQSHoxQ0E&usp=sharing

Thanks for any help you can provide!

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

following is a solution to the problem

yogi_Compute Stats Using Computed Columns For Data For Gambling Data Via Google Form

                                          Google Spreadsheet   Post  #1087
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 19, 2013
user Jody.burgess question by Alvinator :(http://productforums.google.com/forum/?zx=2tcn4vc7qf84#!mydiscussions/docs/fQP-WD8Ejc0)
Hello there, is it possible for you to review my form input calculator?  I would like as well for my form to auto-create formulas as data flows in.  This is a private Casino losses calculator for a friend of mine who is in need of assistance in that field.  The goal is realization of spending.  Anyways, I've thrown in some arbitrary data for the first 5 rows, the TOP row's calculations are different than the rest (as they don't look to the cells above them for the cumulative totals), but 2 through 19 are all just copied down.  I would like the calculations in rows K through S to be copied throughout as data arrives.

Please see the attached link.  Sharing is limited to those with a link and editing is NOT available, please let me know if you will need it turned on...?
https://docs.google.com/spreadsheet/ccc?key=0AoIWja2HbFqpdDA3aVh1WUZfeE5pS2NiN1MwQ2ZiVmc&usp=sharing

Thank you so much in advance, there should really be a better guide posted out there for this.  I'm new to using forms (this is my first one) and that kind of guide would be substantially useful, perhaps after much more learning I would create one.  Anyways thanks again
----------------------------------------------------------------------------------------------------------------------------------

following is a solution to the problem

yogi_Tally The Number Of Hours By Name And TypeOfWork

                                          Google Spreadsheet   Post  #1086
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 18, 2013
user NDHS :(http://productforums.google.com/forum/?zx=9bydteme3h15#!category-topic/docs/spreadsheets/t6ciYC9bTyE)
Hi, 

How do I adapt the countif formula to look at two criteria instead of just one? Or is it a completely different formula? 

Here is the ss - top sheet is raw data. "tally" sheet looks at the raw data for name and type of hours and returns the total on the tally sheet. 

Thanks so much. 

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

following is a solution to the problem

yogi_Pull Select Data From Sheet Complaints Into Sheet Investigation Per User Specification

                                          Google Spreadsheet   Post  #1085
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 18, 2013
user Inv Downs :(http://productforums.google.com/forum/?zx=9bydteme3h15#!category-topic/docs/spreadsheets/2ad-FzeFS-k)
Hello, I need some help on creating a formula to import the datarange from A2:E2 ( below ) from a master spreadsheet called "Complaints" to another spreadsheet Called "Investigations"  which has the same field names  A2:E2 on this spreadsheet , However I only want the data imported if  "Yes" is entered in the field (Generate Report) which is Column U in the Complaints spreadsheet. 

I have tried many formulas to no avail. 

My table looks like this

          A             B                            C                          D                   E                                               U

1    CC#      ViolAddrs              ViolStrt                Hamlet           SCTM#                               Generate Report    <-----------FEILD NAMES

2   130043     13                     Smith Street             Medford          123.-2-13                                         Yes  

3

4

---

Here is the link to the spreadsheet, as I still can not seem to accomplish this task...ughh.

https://docs.google.com/spreadsheet/ccc?key=0AqRHl5sZa75SdENJOFY4RnNhdVRId0kwMTZqVW12TlE#gid=5

I would like certain row data from spreadsheet complaints to be exported to spreadsheet Investigations when the Generate Report column is set to yes or not null.

The row data that I would like exported over (from spreadsheet complaints) is  within column A, B, C, D, E, L, M, N and that said data would be imported into same columns in spreadsheet investigations (A, B, C, D, E) however L, M, N would go to columns H, I, J as the information is the same. 

Any help would be greatly appreciated.

Thanks so much. 

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

following is a solution to the problem

yogi_Rearrange Data In Table A6:E27 As Specified (as shown in G6:K15)

                                          Google Spreadsheet   Post  #1084
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 18, 2013
user Victor DiGiovanni :(http://productforums.google.com/forum/?zx=9bydteme3h15#!category-topic/docs/spreadsheets/9M3gCdDnp9U)
Query a non-aggregated value
I want to do a simple query, but I can't figure out how to do it.  

I manage a sports league with 18 teams divided into three divisions (A,B,C).  At the end of each season, I append the 18 rows of team data onto the end of a master table.  So currently, there are three seasons of data in this chart totaling 54 rows. The teams move between the three divisions from season to season.  So some sample data would look like this.

Team       Season    Division

Team X    001          A

Team Y    001          B

Team X    002          A

Team Y    002          C 

Team X    003          B

Team Y    003          B

What I'm trying to do is create a query that shows me

Team     001    002    003

Team X   A        A       B

Team Y   B        C       B

I've attempted to create a query with a pivot, but I always get an error that says the pivot data has to be in the form of an aggregation.    I successfully created a similar pivot table where I was able to list out the Points by season exactly the same way I want the Divisions listed out, but of course, the Points were in the form of a sum(Points).  

Is this possible with a query, or is there some other function I should be using?

One reason why I'm using a query is that some of the teams also change from season to season, so the ultimate goal is to create a massive timeline chart showing every season across the top, with a each team getting its segment on the "timeline" showing the seasons they participated.  

---

Here's the sheet.

https://docs.google.com/spreadsheet/ccc?key=0Audv0CSq9hLOdHNQUGNQLUlYWEdpdDdhM0kzUHlqeWc#gid=0

a) Need help with the formula for pivoting out the items in the Season and Division row.  I want Seasons to extend across the top in the form of a Timeline, with the Division a Team was in under that season.  Division name is text, not an aggregatable number so it currently won't let me pivot out those Divisions.

b) sample formula is H3, but in actual usage, it will be in a different sheet referencing the data sheet.

c) My ultimate goal is to be able to take all the data from multiple seasons of results, and pivot or sum everything for one team into a single, comprehensive row.  For some data, such as Divisions, I'll want to pivot those out into individual seasons to show a timeline for that stat.  For some, such as Points, I'll want to have a sum of all the points for all the seasons.  The resulting row will likely contain around a hundred columns or more (and grow as seasons accumulate).  On top of that, I'll want some versions of this query to give me results sorted by certain stats. In the sample, it's sorted by Points.  I know I can probably do that sorting as a filtered or sorted table, but I'll want static pages that are dedicated to specific stats. 

d) Is it possible to pivot out something twice in the same query?  For example, if I'd like to have not just the Divisions pivoted out, but the Points as well.  So there would be two sets of columns of the seasons (001,002,003), but underneath each would be different sets of data.  

e) confused yet?  lol. 

f) thanks in advance! 

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

following is a solution using some helper cells 

yogi_Set Up Formulation For Multi Tier Tax Computation

                                          Google Spreadsheet   Post  #1083
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 17, 2013
user Ryan Roga :(http://productforums.google.com/forum/?zx=9bydteme3h15#!category-topic/docs/spreadsheets/jyolpqUi0ZM)
Case of elaborate IF type statement in Google Docs Sheets

Using Google Docs spreadsheet to do the following:

I'm building a commission calculator that estimates your monthly paycheque. Based on your monthly estimate you may fall into a higher tax bracket. For this reason, to accurately estimate income I need to evaluate the estimated income and apply appropriate tax bracket assessments.

In Alberta, Canada, the tax brackets go as follows (I'm only doing 3 for practicality reasons)

1, Taxable amount = $43,561, Monthly that's equivalent to = $3,630.08, 10% AB tax + 15% CDN tax

2, Taxable amount = $43,562, Monthly that's equivalent to = $3,630.17, 10% AB tax + 22% CDN tax

3, Taxable amount = $47,931, Monthly that's equivalent to = $3,994.25, 10% AB tax + 26% CDN tax

Running totals

If you made up to $3,630.08/mo  = up to $3,630.08 is taxable at 25%

If you made up to $7,260.25/mo  = $3,630.08 taxable at 25%,  and remainder up to $3,630.17 is taxable at 32%

If you made up to $11,254.50/mo = $3,630.08 taxable at 25%,  $3,630.17 taxable at 32%, and remainder up to $3,994.25 is taxable at 36%

So essentially, in sudo code:

if( bracket 1, tax accordingly, else if( bracket 2, tax accordingly, else if( bracket 3, tax accordingly )))

or... if this were code of some kind...

case bracket1: tax1 break;

case bracket2: tax2 break;

case bracket3: tax3 break;

I'm having a hard time with this, just can't get my brain in gear. Any help you can offer would be appreciated.

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

following is the illustration of a little play I had with it ... there are several loose ends because of the incomplete spec from the use

yogi_Create 3 Month Labels In Rows From Months In Column A By Offsetting From Latest Entry In Columns B And C

                                          Google Spreadsheet   Post  #1082
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 15, 2013
user Dave Lalande :(http://productforums.google.com/forum/?zx=lrxr3ogavken#!category-topic/docs/spreadsheets/7dHGnbtrh2E)
Formula help...

I need a formula find the last cell in a given column there is a number and then this cell is equal the concatenate of three other text cells.

So...

d2 = December 2012

d3 = January 2013

d4 = February 2013

d5 =  no text

Then cell X (where formula is) = December 2012, January 2013, February 2013

Here is a sheet that I made to highlight the formula need.  I've tried all kinds of combination using various functions and I can't get it.

https://docs.google.com/spreadsheet/ccc?key=0Ai_2YLvaQba0dEI3MGFHYnRSZjh2OFJhdUQwWlh1Qnc#gid=0

Thanks,

Dave

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

following is a solution to the problem

yogi_Some Anomalies In Use Of CEILING Function Comparing Google Spreadsheet Excel And OpenOffice

                                          Google Spreadsheet   Post  #1081
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 10, 2013
user Shane Dobartz and inspiration from Bas Braams :(http://productforums.google.com/forum/?zx=lp79zfmnqwy4#!category-topic/docs/spreadsheets/GeqGpPPJo9o)

Is there anyway to round up?

Good afternoon I am doing a spreadsheet where I required a certain amount of something for every 5 used. for example if I need 101, than I need 101 / 5 = 20.2, the .2 means I need one more, so I need 21

What I am trying to see is if I can "round up" in my google spreadsheet. I need to round up anything above a whole number, i.e. 20.1 to 21, 20.7 to 21, 20.000000000001 to 21, 20.999999 to 21 etc.

Is there anyway to have the format round up regardless if it "should" round down to the nearest whole number?

Thank you.

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

a simple question about ROUNDUP led to a critical contribution from Bas Braams on use of ROUNDUP and CEILING functions and that led me to have a bit of play and found the anomalies and differences in use of the CEILING function in Google spreadsheet, Excel, and OpenOffice

yogi_Extract in Column A All The Colors That Are Common To Columns From B To K

                                          Google Spreadsheet   Post  #1080
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 10, 2013
user meph2u :(http://productforums.google.com/forum/?zx=tc5oxst1kk1#!category-topic/docs/spreadsheets/N1OnKIx6-lY)
Finding common values in a variable number of columns
https://docs.google.com/spreadsheet/ccc?key=0Atrb4wVf_dsSdDFzQjBHR05KWFlTRUNJTk81TlJaaXc#gid=0

I am looking for a formula for cell A2 (I don't have one or an idea how to do this) that will:

Look at the lists of colors in Columns B- K, and return a list of colors for which there is a corresponding entry in all columns B-K that have data (ignoring those columns that don't have data).   Sometimes only 2 columns will be used....it is variable up to 10.   The list of colors in a column (should it exist) will always start in row 2.

I believe the data in A2-A4 is the answer that would be produced from the sample lists in the spreadsheet

Thanks in advance
---------------------------------------------------------------------------------------------------------------------------
thanks to very fine contribution from our Top Contributor Hyde in the related thread in Google Docs Help Forum, I was inspired to create a single cell solution here in sheet yogi_Sheet2_singleCellFormula

yogi_Compute 3 Month Rolling Average Of Specified Metrics (subject to availability of latest data for 3 consecutive months)

yogi_Compute 3 Month (current month and prior two months) Rolling Average Of Specified Metrics

                                          Google Spreadsheet   Post  #1078
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 10, 2013
user David LaLande :(http://productforums.google.com/forum/?zx=wp0u4iwq5gs6#!category-topic/docs/spreadsheets/pN8t5vketRI)

I need some help with a formula for a 3 month rolling average.  I've tried it a couple different ways but I can't get it right.  Any help would be appreciated.

Here is my sheet.

https://docs.google.com/a/davelalande.com/spreadsheet/ccc?key=0AhCv9Xu_eRnSdG5QcXUwUFNHSnF4b2EwSW5PWDltd3c#gid=6

Thank you,

Dave

---

I have values in the first three rows of column C starting at C2.  I am trying to do a rolling 3 month average of column C.  I'm not getting it done.

=average(offset(C2:C14;if(row(C2)-row($C$1)<2;row($C$1)-row(C2)+1;-3);0;if(row(C2)-row($C$1)<3;row(C2)-row($C$1);3);3))

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

following is a solution to the problem