Looking for some suggestions to a reporting challenge I am facing

Kelly Gabel

My team currently uses a vertically formatted sheet as a Project Management Schedule. Using Gantt charts, we track tasks and milestones in rows vs columns.

Of course, now, I'm being asked to report on that vertical data in a horizontally formatted report and I'm struggling to figure out the best way to do that in an automated report. I have experience with formulas and have access to Data Mesh, but I can't figure out how to set that up in a way that it can pull data from the same date columns but specific rows.

An additional wrinkle is that this data lives across, currently 8 separate sheets because there is so much data that we keep hitting the sheet data limits.

I am open to any and all suggestions here!!

Thanks!

Paul Newcome

Are you able to provide some screenshots for reference?

Kelly Gabel

Hi @Paul Newcome ! Absolutely! So this is the format the data currently lives in. As an example, I need to be able to pull only the highlighted dates and put them into specific columns on a report. I don't want a report with all of those rows, though. I want all of the dates at the parent level where the Template - Normal is.

Does that make any sense? This is harder to explain in writing than I expected LOL

Andrée Starå

Hi @Kelly Gabel

I hope you're well and safe!

I've sometimes used the Sheet Summary section to collect dates and then show them together in a row using a Sheet Summary Report.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Kelly Gabel

@Andrée Starå Hope you are well also!

I like the idea of that, but I'm concerned about the sheer volume of data I have to work with. I'm talking about hundreds of dates per sheet. This sounds like a very manual option if I'm understanding it correctly is that true?

Andrée Starå

@Kelly Gabel

Yes, except dealing with post-Covid. (no fun)

Yes, hundreds of dates would be tedious.

Another way would be to cell-link or use INDEX/MATCH to collect/connect the dates to another sheet. Still tedious but can be simplified by adding numbers beside each date or something similar.

(you could cell-link, INDEX/MATCH all dates to the other sheet and make further arrangements/selections there)

Make sense?

Would that help/work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Kelly Gabel

@Andrée Starå so sorry you were sick! Hope you recover quickly!

So I do use INDEX/MATCH elsewhere already but not for a purpose like. I'm not sure if I understand how I would use it here. Can you show me an example?

Cell-link was my first idea, but without the use of datamesh, that's also super tedious and feels far more breakable...

Paul Newcome

Try this...

Insert an auto-number column (called "Auto" in this example) with no special formatting.

Insert a text/number column (called "Row" in this example) with the following column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Finally we can insert another text/number column (called "Number" in this example) with this column formula:

=IF(COUNT(ANCESTORS([Project/Task/Issue/Risk Name]@row = 1)), Row@row)

Then we can move over to your other sheet where you want to move this into a horizontal format. Assuming this sheet looks something like this:

You can use this formula in [Date 1] and dragfill it over:

=INDEX({Date Column}, MATCH(SMALL({Number}, COUNTIFS($[Primary Column]@row:[Primary Column]@row, OR(@cell = "", @cell <> ""))), {Number}, 0))

I would have this second sheet set up per project and then create a report that pulls in all of these second sheets.

You can also adjust the numbers in the "Number" column as needed to make them manual entry if you know it will always be those specific rows in that specific order.

Kelly Gabel

Ok...I think I'm following and I'm going to give this a try LOL I can't do the sheets per project because we are already at 500 projects and likely to be in the thousands next year LOL but I will split them up as much as possible.

I'm sure I'll be back with questions! Thanks Paul!

Kelly Gabel

@Paul Newcome hey, so this formula is coming back Unparseable for me... =IF(COUNT(ANCESTORS([Project/Task/Issue/Risk Name]@row = 1)), Row@row) Can you help me understand better the purpose of this column/formula in this use case so I can troubleshoot?

Paul Newcome

@Kelly Gabel My apologies. The parenthesis got me...

=IF(COUNT(ANCESTORS([Project/Task/Issue/Risk Name]@row)) = 1, Row@row)

Kelly Gabel

@Paul Newcome No worries! Ok, I have done a test set up of the added columns where the source data is and that seems to be working as expected now.

On the horizontal report sheet, I'm struggling with that formula. Can you break down the pieces of the formula for me to help me better understand what the references need to be?

Paul Newcome

Certainly...

=INDEX({Date Column}, MATCH(SMALL({Number}, COUNTIFS($[Primary Column]@row:[Primary Column]@row, OR(@cell = "", @cell <> ""))), {Number}, 0))

COUNTIFS($[Primary Column]@row:[Primary Column]@row, OR(@cell = "", @cell <> ""))

As you dragfill this over the bold portion should update so that it is one more column to the right. SO when you dragfill it over to the [Date 2] column it should be $[Primary Column]@row:[Date 1]@row. This counts how many columns to the right we are.

This count tells us which number to pull for the SMALL function.

SMALL({Number}, #)

As we move from left to right it should increment by 1. So the leftmost column will be the smallest number. The second date column would have the second smallest number, so on and so forth.

The number generated by the SMALL function will give us something to MATCH on in the Number column of the source sheet.

MATCH(SMALL({Number}, #), {Number}, 0)

When a MATCH function is evaluating a single column, it will output the row number.

We use the MATCH function to output the row number for the INDEX function.

INDEX({Date Column}, row_number_genreated_by_match)

What exactly are the struggles you are running into? Are you getting an error or is it outputting a wrong date?

Kelly Gabel

Honestly...this formula is melting my brain a little LOL

I feel like I understand the overall functionality, but I'm confused about some of the references, which might have more to do with my source data formatting than anything else...but can you tell me, based on my screenshot earlier, which column would be referenced as the INDEX({Date Column}? I think that's throwing me from the get.

Also, in this portion: COUNTIFS($[Primary Column]@row:[Primary Column]@row, OR(@cell = "", @cell <> ""))

is the part I bolded in addition to the column ranges, or is that meant as an alternate way to select the range?

Paul Newcome

The {Date Column} reference is the column that houses the dates you want to pull in. You mentioned "I want all of the dates at the parent level where the Template - Normal is." Whatever column houses those dates is what you would reference.

COUNTIFS($[Primary Column]@row:[Primary Column]@row, OR(@cell = "", @cell <> ""))

The bold portion is the criteria for the range. This says to count from the primary column to the column immediately to the left of the formula (assuming your target sheet is structured how I have in my screenshot). Using this particular criteria ensures that it will always increment by 1 regardless of whether any of the previous cells are filled in or blank.

COUNTIFS(range, criteria)

Kelly Gabel

UPDATE: I learned that a new colleague of mine is great with SS formulas so he is helping me with this! I'll report back how it goes!