Creating a new sheet, which is a subset of existing sheets


#1

Hey guys, I am trying to build a base that allows my investing firm to manage our pipeline.

This is what I have:

  • Sheet which has Companies (under “Name”)
  • One of the cells for each Company is “Existing Investors”–this links to another sheet (Investors) where the "Name"s are investors
  • The investors are categorized into different buckets in another column – one of those categories is “Direct Competitor”

Given this information, I would like to have a different sheet where all the companies are companies which have an investor that is a Direct Competitor. Then I’d like to add more columns for additional analysis we would do that we don’t care for in the broader group of companies

Make sense? All suggestions are welcome


#2

Hi Amit! Reading through your description of what you’d like to accomplish, it seems like the best solution for you is to use different views to look at your companies table—one view with all of the companies that hides unnecessary fields, and one view that uses filters to show only the companies with direct competitors investing and shows all fields. In database software, rather than using separate sheets/tables to look at subsections of your data, you usually use views, which show just a specified slice of the entire dataset. (This is how Salesforce works, for example.) This eliminates redundancies in the data that might come from making many different tables.

Rather than creating a separate table to show only the companies with a “direct competitor”-tagged investor, we’d recommend creating a new view within your existing companies table. Here’s the steps you’ll need to take:

  1. Use a Lookup field to pull the investor categories into the “Companies” table.

  2. In the Companies table, now, we’ll create a view called “Funded by direct competitors” that filters to show only the Companies which have a “Looked-up” investor category containing the phrase “Direct Competitor”.
  3. We can then add fields to the companies table, and hide them from the other views in that table so they only show up when looking at “Funded by direct competitors”.

In general, using a single table with multiple views and hidden columns to achieve this sort of workflow, as opposed to multiple tables with duplicated data between them, is cleaner and more “correct” in database-land (as opposed to spreadsheets)—and is something that you’d also do in other database products like Salesforce. Hope this helps!