With almost universal availability of Internet and the availability of reliable on-line productivity tools, such as Google Docs, individuals as well as companies are switching over to Cloud Computing. In this blog I will post items of interest to my colleagues/patrons/clients.

I have a list of priorities, names and formulas. I want to be able to vlookup for a certain formula from a list of formulas. Vlookuping for values or names is straightforward, but that's not what I want.

That is, when I change priorities, I want the formula assosiated with each priority to change as well.

I should describe a little better the subject, but it would be awful... >.<

In my office I have a big workbook with several different sheets and, for better structure, there are one workbook for each month.

There are a few auto-increments in these workbooks, which means the first value must be the last value, from last month incremented by one.

Once I need to duplicate the "Template Workbook" every month before start using it, I decided to add a group of cells from which I can do some sort of configurations:

+-----------------------+

| Last Records |

| (from Last Month) |

+-----------------------+

| Book ID | Last Record |

+---------+-------------+

| A15 | 1.234 |

+---------+-------------+

| B28 | 5.678 |

+---------+-------------+

| C05 | 9.012 |

+-----------------------+

For future reference, let's name this range as Q1:R3.

In the previous version of this "Template Workbook", it was an easy task because each Book ID had its own sheet.

But I was wasting my working time by opening between all the sheets all the day. And then, I decided to add all records in a single sheet and when I need some specific data, I just have to query them, just like a database:

+---------+-------------+

| Book ID | Next Record |

+---------+-------------+

| A15 | 1.235 |

+---------+-------------+

| B28 | 5.679 |

+---------+-------------+

| A15 | 1.236 |

+---------+-------------+

| B28 | 5.680 |

+---------+-------------+

| C05 | 9.013 |

+---------+-------------+

For future reference, let's name this range as A1:B5

And now the real problem about the auto-incrementing, to be added in Column B:

I need to check if the value entered in Column A is the first occurrence of that value in entire Column.

If so, the value to be added in Column B will be what I have defined in R1 PLUS 1, because it refers to the first record, of that book in that month.

If it's NOT the first occurrence, I need to find the penultimate occurence of that Book ID in Column A, grab the value of its Column B and then increment it.

Applying this idea to table above, it would be:

+---------+-------------+----------+---------+

| Book ID | Next Record | First? | Use |

+---------+-------------+----------+---------+

| A15 | 1.235 | YES | R1 + 1 |

+---------+-------------+--------------------+

| B28 | 5.679 | YES | R2 + 1 |

+---------+-------------+--------------------+

| A15 | 1.236 | NO | B1 + 1 |

+---------+-------------+--------------------+

| B28 | 5.680 | NO | B2 + 1 |

+---------+-------------+--------------------+

| C05 | 9.013 | YES | R3 + 1 |

+---------+-------------+--------------------+

I sincerely hope you can understand, because it was very difficult to me to explain.

I have a need that is a little special. Would it be possible to tweak the above formula such that it would give the persons age in months IF the person was less than 2 years old? I am director of a children's ministry at our church and this would be awesome. I would like it to display with an "m" or "mos" after the value if possible, e.g. 18m or

Yogi Anand, D.Eng, P.E.Google Spreadsheet Post #713 Aug 24, 2012www.energyefficientbuild.com.user squash said: (http://productforums.google.com/forum/?zx=frqi4n9v2tm8#!category-topic/docs/spreadsheets/ZbFTQNCipwA)Automatically adjusting a formula that uses a dateMy spreadsheet is for tracking sales.On Sheet 1, I'm tallying salesColumn A is the Date I'm open for businessThe rest of the columns are the different items I'm selling, tallying each of the itemsOn Sheet 2, I'm calculating monthly totalsColumn A has the various items, and the months are in Columns B-M I'm using this formula =SUM(filter('Sheet 1'!C2:C,'Sheet 1'!A2:A>=date(2012,1,1),'Sheet 1'!A2:A<=date(2012,1,31)))to automatically calculate the number of units (tallied in Column C) I've sold of a particular item (in January 2012, in this instance).The next item's numbers are in Column D, etc etcMy question is, is there an easy way to adjust this formula for the different months and different items? I don't know an easy way to "find and replace" pieces of a formula. Dragging the formula down doesn't make the correct change to it for the different items, and more importantly, I can't figure out any way to change the dates for the different months besides going in and doing it manually. I thought I might be able to put the portion of the formula that deals with the date'Sheet 1'!A2:A>=date(2012,1,1),'Sheet 1'!A2:A<=date(2012,1,31)into its own cell, and then jut reference that cell for all the formulas for a given month, but it returns errors.Anyone have any clever ideas here? Your help is much appreciated. Thank you in advance.------------------------------------------------------------------------------------following is a solution to the problem

1) Use a script to detect possible same names. Perhaps using a script that detects if a certain percentage of letters are the same would work?

2) Then, I want to be presented with these possible matches and be able to decide if I want to change them to all have the same listing e.g. change all of the "John A. Smith" entries to "John Adam Smith" or to leave them the same if they are different names.

Yogi Anand, D.Eng, P.E.Google Spreadsheet Post #707 Aug 21, 2012www.energyefficientbuild.com.use igge said: (http://productforums.google.com/forum/?zx=ahxxx0f1ye6h#!category-topic/docs/spreadsheets/tEljLsZs0gA)Comparing amount of cells in order to determine a resultHello. I've tried many different approaches to this problem, but I can't seem to find a solution.Player A and B play 3 sets in a game. The scores of those sets are represented in columns B3 through D4 like this: A B C D1 Game12 Set: 1 2 33 Player A: 5 2 14 Player B: 2 3 15 Winner: tie On row 5, in a merged B-D cell, I want to calculate a winner based upon how many sets they won. If someone won 2 sets they win the game, and also if they won only 1 but the 2 other sets were tied. Likewise, in case of a tie (1 win each and 1 tied game or 3 tied games) the result should be "tie".Like I said I've tried to do this in several ways using COUNTIF or ArrayFormula, but I just can't seem to get it to work. Using the line below I'm able to display the winner of each set, but I still need to combine this data to determine an over-all winner:=ArrayFormula(IF((B3:D3)>(B4:D4), "Player A", IF(ArrayFormula((B3:D3)<(B4:D4)), "Player B", "tie")))Any ideas?Thanks!-----------------------------------------------------------------------------------------following is a solution to the problem in Sheet2, I have provided the solution to a bit generalized problem in finding the winner from a specified number of games played regardless of the total number of games played.