Tuesday, January 16, 2018

yogi_Calculation Cell Referencing Across Multiple Tabs In A Spreadsheet

Google Spreadsheet   Post  #2345

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-16-2018
Calculation referencing multiple cells and across tabs
I have a spreadsheet with multiple tabs for inputs: Money we have received (Budget), Money we have spent (Actual Spent), and Balance (where I want to calculate).  All data is tied to a client name in column A.

What I want to do is be able to have a roll-up sheet (Balance) with a formula that will reference the input in Column A (client name) and in Row 1 (month/year) and calculate the difference between those values in the other tabs.  This would be used in cell H2 and copied down the line.

For example the sample client I have entered has a budget for each month in the Budget tab.
Actual spends are entered in the next tab.
in Balance, I want to calculate the difference between Budget and Actual as it relates to the client in column A, and if possible, reference Row 1 so that it will just look up those values so I don't have to mess with it down the road as long as the naming structure is consistent into 2018, 2019, and so on as we add dates down the line.

Here is the sample data I've entered into a sheet to share: 

Any help is appreciated!  I've been mapping out how to make this work and keep running into a wall.


Sunday, January 7, 2018

yogi_Split A String In Cell C2 To Pull Items In Master List (Cells D2:D)

Google Spreadsheet   Post  #2344

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-07-2018
Filtering an array where elements may occur within each other as substrings
I am trying to take an input string and return a set of substrings that are found within it. The issue is that my master substrings are very similar, and some contain the contents of others.
Here is a sample data set:
- ABC News
- CBC News
- MTV News

If I input "ABC and MTV News," I would like the return value to be "ABC" and "MTV News," NOT including MTV (despite the fact that it is technically a substring). I would also be able to input "ABC and ABC News" and have both returned correctly. Ideally I'd like to do it in a way that isn't limited to this set of data (so an if statement like "if MTV is found AND MTV News is found then..." would not be suitable). My intention is to use the filtered data for vlookup purposes, so hardcoding the data differently isn't really an option either. Another user suggested using regexextract and regexmatch and changing the master data, but I'm wondering if it may be possible to utilize them without having to do that.

Most critically, the input string is not restricted to a certain format. All of the following should be valid and return the same thing:

"ABC News and MTV"
"ABC News, MTV"
"I like watching TV. My favourite news channel is ABC News. I also like MTV."

All of the above should return "ABC News" and "MTV" as found strings, and exclude "ABC," as its only appearance is within an existing element. However, the following should also work:

"ABC, ABC News, and MTV"
"ABC News, ABC, MTV"
"I like watching ABC, especially ABC News. I also like MTV."

All of the above should return "ABC," "ABC News," and "MTV," with no exclusion of "ABC" because it was found independent of its appearance within "ABC News."

Sample sheet:

Input: B1
Master data: D and E
Vlookup sample: B13

Saturday, January 6, 2018

yogi_For List Of Cases And Reviewers In D2:D Assign Reviewer To Cases Evenly

Google Spreadsheet   Post  #2343

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-06-2018

Assigning Challenge

Hi I have a challenge. 

In this sheet I have a list of cases in column A. I also have a list of reviewers names to the right. I want a formula in cell B2 that will read the reviewers list and assign a reviewer evenly to all the data by adding the reviewer's name into the B2:B fields. The result in this case would be Joe, Sandy, Mike, Joe, Sandy, Mike, etc going down from B2.

Also, as a twist, I want the formula to be able to do this regardless of the number of names in the reviewers list. I may add or subtract in the future.

What do you say?? :)


Friday, January 5, 2018

yogi_Match Column Names In Z1:AO1 With E1:Y1 And Pull Data From Matched Columns

Google Spreadsheet   Post  #2342

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-05-2018
Sheet w/input from Form combine like column names into single columns
Could you please offer a solution or ideas to solve this problem? 

 I have a daily sign in form to track attendance  for a community center which has daily classes Mon - Sat. Each day has a different list of classes, but sometimes a different day has the same class as a previous day. So for example:
* Mon has C1, C2, C3
* Tue has C4, C5, C1 & C2
==== In above example C1 and C2 are repeated on Tue but C4 and C5 are unique to TUE but may repeat later in the week.

FORM logic is as follows:
* Select Day (Mon, Tue, Wed, Thu, Fri, Sat)
---- Jump to section of form named for  day selected
---- Data posted to SHEET

Since I have multiple question sections, the data is put into separate column names for each section of the form, sometimes with duplicate event names. Each row only has entries in the columns related to it's section in the Google Form. All other cells are blank for the other sections. 

I'd like to create another set of columns in the sheet, with the names of each class as the first cell in the column, then have a formula following that parses all the cells to the left which contain form class data, and copies the cell contents if it's column name matches the column name of the cell containing the formula. So for example, Cell Z2 looks across cells E2:Y2 (answers begin in column E in this example)  and only copies the contents of a NON-EMPTY cell that matches the formula column name.  There will only be one cell with data in that row matching the formula column name.

This is similar to the problem statement expressed in the link below, except my sections are different but can contain some of the same column (field) names:

Any ideas? Thanks in advance for the help!

(Copies of example of the Form and Sheet are below, if needed.)

yogi_Compute The Date Subsequent To Which Cumulative Sun In Column G Is 6 or More And At Least One Cell In Column H Contains 'HLD'

Google Spreadsheet   Post  #2341

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-05-2018
Return a cell when an ongoing sum exceeds a value

The link to the spreadsheet:

I need help with a formula that will give me the most recent date when column G meets or exceeds 6. It also must check to make sure that within those dates, column H includes "HLD".

For the given data, the formula should return the date 7/12/2017.

I have tried using a query function, and an array formula, but I can't get it to work to add up to 6. Any help with this is much appreciated!

- Jesse

Wednesday, January 3, 2018

yogi_Conditionally Format Cells D:O If Value ThereIn Is Less Than Value In Column A Provided Column A Is Numeric

Google Spreadsheet   Post  #2340

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-03-2018

Conditional Formatting of rows if less than a specific cell


This question must have been asked and answered somewhere but I've spent nearly an answer trying to figure it out by myself and from questions/answers and still cannot get it, even though it must be so simple. Any help at all is greatly appreciated.

I have a cash flow sheet which I'd like to apply a simple bit of Conditional Formatting to. I would like the text of cells in two rows to turn red if they are less than the value in cell E80. I would have thought that a simple "Format cells if... Less than... =E80" would work but, alas, not so. As you can see from the screenshot, the behaviour is quite random.

I've prepared an example spreadsheet demonstrating the issue here:

How can I get the behaviour I want?

Thanks in advance.

Tuesday, January 2, 2018

yogi_Count Number Of Entities Based On Specified Criteria From Data In Other Sheets

Google Spreadsheet   Post  #2339

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-02-2018

Problems with COUNTIF creating data collection sheet

Hello.  I manage a business where clients rent different "Tier" locker(s) based on their needs and am trying to start collecting data based on other sheets from a workbook.

The "Clients" tab is normally a complete list of every client, contact info, and other data.
The "Lockers" tab is a list of every locker that auto-fills based on data from the Clients tab.  This is used to populate the data tab.
The "Data" tab is new, but the first thing I want it to collect is general capacity information.
I need help with the Data tab.  I have all the basic formulas in, but need a function that can fill in the "leased" column.  This would be a count of every cell in column C (Lockers Tab Last Name) that isn't yellow or red (doesn't end with a "*"), but based on the corresponding Tier in column D (i.e. "1st)

I was told using an asterisk may be a problem because it is used as a wildcard.  If so, please let me know what I would need to change to make something work.


Thank you ahead of time!