How to create "views" by hiding cells (reports, filters, formulae?)

Hi folks,

I am trying to create a master calendar that can track critical event dates for a project across a timeline. The total number of events that we want to align with dates for all projects is about 15, though that doesn't really matter to my question.

I have created columns where each column is a month and year (eg. "Mar '21", Apr '21, etc) for 2021, 2022, and 2023 and the column types are multiselect with the values being the event types that I want to lay into the appropriate months/year columns (eg. "Deal", "App", "Award", etc).

None of this has been tricky BUT The rub is this:

We have several teams who will be looking at this timeline and I would like to display only the events relevant to that team; effectively creating "views" of events relevant to specific audiences. However, while I am able to filter or report on, say, "show me all columns where the cell = "Deal", and it does return rows where the logic is true and "Deal" is on the column, I want to hide other events along the timeline.

I don't think there's a way to do this, but I figured I would ask the community and see if any of you clever folks have a different approach to this.

Thanks in advance for your thoughts on this!

(please see attached pic for a view into how the sheet is structured)

Thanks!

-Chris

master dates.PNG

Find more posts tagged with

Accepted answers

All comments

unknown

This content has been removed.

Genevieve P.

Hi @h1pst3r88

I understand why you're getting stuck with a Report, since it will show the rest of the rows if only one of the columns meet your criteria. I have an idea, but it may be a bit too convoluted for your process, depending on what you're looking to do!

Instead of a Report, you could create one sheet per status, so a sheet for all the DEAL cells.

The way I'd do this is to create a copy of the current sheet, clear out the contents in the cells, then input one formula per-column (yes, creating a column formula!).

It would be like this:

=IF(INDEX({Jan '21}, MATCH([Project Name]@row, {Project Name})) = "Deal", "Deal", "")

This is just for the DEAL sheet, and just for the column Jan '21. The formula uses index/match to check the cell in the other sheet and only returns "Deal" if that's what the other sheet says, otherwise it will be a blank cell.

So, here's my MASTER sheet:

Screen Shot 2020-12-21 at 8.24.06 AM.png

And here is the Deal sheet, with 3 Column Formulas:

Screen Shot 2020-12-21 at 8.24.15 AM.png

You would need to update the column reference for each formula, so the Feb '21 formula would be as follows:

=IF(INDEX({Feb '21}, MATCH([Project Name]@row, {Project Name})) = "Deal", "Deal", "")

Then once you have this sheet fully set up for Deal, you could create a copy and adjust all of your formulas to search for a different category:

=IF(INDEX({Jan '21}, MATCH([Project Name]@row, {Project Name})) = "App", "App", "")

There is a limit of 100 cross-sheet references per sheet, but it sounds like you'd only have 37 columns (3yrs + project column) to use, so that should be fine. Your Project Name column would also need to have unique names per-row, but it looks like that won't be an issue based on your screen capture.

Do you think this would work?

Cheers!

Genevieve

unknown

This content has been removed.

Genevieve P.

Hi @h1pst3r88

Thank you for clarifying, you're right, then this wouldn't work for your purposes. You would need to have 15 separate sheets, one for each, and you wouldn't be able to edit from these sheets. If you edited the cell, the formula would break and it wouldn't adjust the data in the source sheet.

Based on your set-up, there isn't a way to accomplish exactly what you're looking for. A Report would be the closest because you could edit directly from the Report and update the source sheet, but as you mentioned earlier, it wouldn't hide any of the other data if one of your columns had a matching criteria.

At this point, I would maybe even just set up 15 filters on the sheet to help display the associated rows. This may be the fastest/easiest way to work with it, especially since you so nicely set up your Conditional Formatting to make it easy to see each type of data.

Cheers,

Genevieve

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.