Skip to main content

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

  • July 1, 2016
  • 3 replies
  • 79 views
A

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

    3 replies

    K
    Forum|alt.badge.img+5
    • Katherine_Duh
    • Inspiring
    • 332 replies
    • July 1, 2016

    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!

    H
    • Henry_Ferlauto
    • New Participant
    • 1 reply
    • February 1, 2021

    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!


    Hi @Katherine_Duh, A semi-related design question with regard to subsets.

    I’m building a CRM database. Some of the companies have attributes that are unique only to them. So when the company is a certain type, that will beget numerous attribute fields. These fields would be completely irrelevant to companies that are not of this type.

    Think: List of companies, but a the type is “restaurant” and if so, there might be links for their menu and other things that a non-resturant business would not have. This is not the specfic example I am working with, but is something relatable to everyone. The number of attributes for this specific subset of companies will be numerous.

    As I discovered through trial and more importantly error, I cannot have a primary field be linked to another primary field (Company Name).

    Any design advice would be greatly appreciated.

    Thank you.

    L
    Forum|alt.badge.img+1
    • Liu_Chen
    • New Participant
    • 1 reply
    • November 15, 2022

    I’d like to bump @Henry_Ferlauto 's question and this thread generally! I am facing a similar situation. I have a subset of data with lots of associated-only-with-that-subset data. It feels like having all of those associated Fields in the main Table ends up clogging up the Records outside of that subset.


    Terms & ConditionsAccessibility statement