Max rows/cells

I have a sheet that has reached the max number of cells but I have more data to add. It there an easy way to reference more than one sheet to get all the data on one sheet for dashboarding purposes?

I started to create a blank sheet that would count the number of rows based on specific criteria but it doesn't seem to be calculating correctly. The sheet referenced is only the month of August so I wouldn't expect to have a value for anything past the month of August.

=COUNTIF({Call Analytics Phase 2 August Range 1}, CONTAINS([Week#]@row, @cell))


Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Have you looked into creating a row report?

  • blmccue
    blmccue ✭✭
    edited 10/23/23

    @Paul Newcome, I have but I haven't found a nice way to display the data that shows on the row report since I can't really manipulate the column names and that doesn't really help me when I cannot add any more rows to the sheet.

    I have separated them out by month but want one sheet that summarizes it all together.

    Paul, here is a link to the dashboard that I have created https://app.smartsheet.com/dashboards/GGrWMv65w8c7Cwj5Fc6qwjmvcXpqFfwGgJ7Hjvg1. This is only reporting on August since I move September and October to their own sheet. Originally, I had one sheet that had this all included.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would create the separate sheets for each month (or whatever breakdown you need to allow for the volume) and then use a report to join all of the sheets together.


    Are you able to provide a published link or a screenshot?

  • blmccue
    blmccue ✭✭

    I can't publish due to customer information but here is a screenshot without that information. I will have to have a separate sheet for each month to join together on a yearly summary sheet. I've tried the row report and sheet summary report and can't get the data to report like I currently have it.

    Snip it of September

    Snip it of October

    And the combined sheet where I am trying to summarize it all on one sheet, I finally have a count for the total number but trying to count the row based on the "Accident Question" if the Week # is XX is where I cannot get it to work.


  • blmccue
    blmccue ✭✭
    edited 10/23/23

    I just realized the dashboard link I provided was from phase 1. I should have included a link the the second phase dashboard since all this new data is what is populating this dashboard https://app.smartsheet.com/dashboards/6vMwg32GjJR4x8xJxv758JwmwgJ2WGX3XhVhC7p1.

    I haven't been able to update it since week 38 due to max cells.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hey @blmccue

    I agree that a Report would be the best way to summarize your data if it's spread across a number of Sheets. You can use Grouping to Group by the Week Number, then Summary to Count or Sum or create another calculation.

    If we focus just on your formula, you can use a COUNTIFS function to count how many rows meet your criteria of both Week Number and Accident Questions.

    For example, something like:

    =COUNTIFS({Week Column Reference}, [Week#]@row, {Purpose 1 Column}, "Accident Questions")

    Then if you need to do this for 2 sheets, you can use + to add multiple COUNTIFS together:

    =COUNTIFS(sheet 1) + COUNTIFS(sheet 2)

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!