yogi_Populate Weekly Calendar(s) With Project Entries By Subject And Date

                                          Google Spreadsheet   Post  #907

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

I present this solution in response to a question from Mustafa Uskupulu

yogi_Compute Sum of Cells With Cell References To Another Sheet

                                          Google Spreadsheet   Post  #906

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

user robashroy said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/SUsQaceYYVU)
sum with reference to cell in another cell 
This is a cell I want to copy between cells

=sum('12.01D'!$L$2:$L$8)

The 12.01D refers to the sheet in the current book.

How can I replace that with a cell reference so I can just enter the 12.01D into the 1st cell of the line and then the other 40 cells to the right will reference the correct sheet?

---

https://docs.google.com/spreadsheet/ccc?key=0Aj1T44S8KSz3dHZ1NHRaX18tTVJSSFU1eDQ1eC1FWkE

H3 is =ArrayFormula(sum(indirect($A$3&"!$M$2:$Q$8")))

I want it to be =ArrayFormula(sum(indirect($A$3&"B3:C3")))Where B3 is cell M2 and C3 is Q8 so it uses the data in A3 (Sheet), B3(cell) and C3(cell) to perform the sum.

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

following is a solution to the problem

yogi_Remove All Diacritics From Letters Of Text In Column A

                                          Google Spreadsheet   Post  #905

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

user Grengounours said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/GLMVebWitDA)
Removing all diacritics

Translate message to English  

Hi all,
I would like to know if there is a function or script that can remove diacritics in a cell.
Meaning transforming "Bogosavljević Bošković" in "Bogosavljevic Boskovic".
G.D.

----------------------------------------------------------------------------
I am not aware of any function that would remove the diacritics ... so
I used the SUBSTITUTE function to replace letters with diacritics to those without diacritics and the REGEXREPLACE function to delete other non-alaphabets for a bit more generalized situation ... as presented in the following

yogi_WorkAround For Conditional Formatting To Include More Than One Condition Per Rule

                                          Google Spreadsheet   Post  #904

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

user Nude_Lewd_Man said:(                                       http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/q3HlZgtRWps)
Conditional formatting, how can I include more than one condition per rule..?
Hi,

I've got a spreadie where I've replicated one page off to demonstrate:
https://docs.google.com/spreadsheet/pub?key=0AmsJVtMcG2QwdGhIN3lTWWRZQmZzVVMtdFJLVk9ZWHc&output=html

Basically, we have a number of sites/depots that are dotted around the world, and I would like to use conditional formatting to change the text colour to make it easier to see which region that depot is in. Unfortunately, we have so many depots that I can't just add each one to use a rule for itself (tried that, only got EMEA, Canadia and the first of the APAC depots done...then ran out of rules) which doesn't really help as much as I'd like...

I then tried to use multiple entries/conditions to one rule (as in '"London" or "Amsterdam" or "Dresden" or...') but that then borked the whole rule - presumably as it was looking for all the depot names in the correct order, each with the quote marks and all in one cell..

I've taken a quick snip to try to show the example I've currently got running, which can be seen here:
http://www.Nude-Lewd-Man.co.uk/Misc_Pics/_TEMP_.JPG

Anyone have any ideas as to how I can get this sorted..?

Please advise.


Kind regards,

N_L_M

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

As of now (Dec-02-2012) Google spreadsheet does not support Conditional Formatting to include more than one condition per rule ... so I propose the following workaround

yogi_Compute From Sheet Named Data Difference Between Current Week And Prior Week Values -- Current Week Will Always Be Column B With Previous Weeks Pushed To Right

                                          Google Spreadsheet   Post  #903

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

user TerraAnt said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/q3HlZgtRWps)
How to keep formula from changing? 
I have a spreadsheet with two sheets - one for keeping my data, the other for calculating and displaying this data.

The data sheet has each column containing information from a single week. Each week a new column is added (column B is inserted, with old data getting pushed to the right).

The formula sheet is supposed to go into the data sheet and pull up information from column B (current week's data) and from column C (previous week's data), do bunch of stuff to that data and display the results.

It all works fine, until I insert a new column B in my data sheet. That's when all my formulas change and start reading from columns C and D (that's where old data has been pushed to).
Here's my formula:

=IF(ISBLANK(Data!C2),"- - -",Data!B2-Data!C2)

I need this thing to always look at columns B and C, instead of following my old data to it's new location.
--------------------------------------------------------------------------------------
following is a solution to the problem

yogi_Divide Numbers In Column A By Numbers In Column B And Present Results In Specified Fractionals

                                          Google Spreadsheet   Post  #902

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

user Steve Deibert said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/DyFnfOPzVok)
Dividing always creates integers?
It seems that no matter what I try, my division always gives me integer values but I need decimal places. From all I've found it doesn't seem I can divide and get fractions... some please tell me I'm wrong. (5/2) != 2

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

following is a solution to the problem

yogi_Sort Rows Of Sheet1 by Number of Columns In Rows In Descending Order

                                          Google Spreadsheet   Post  #901

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

user Drew Clifton said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/BNnorW3xKSw)

Sorting rows in number of contained columns? 

Hi, I have a whole lot of rows, and they all have at least one more column.

Keeping the columns related to the same row, I'd like to sort things so that the rows with the most columns are at the top, descending to the least (only one more column).

Thanks :)

Firefox 17, Mac OS X ML

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

following is a solution to the problem