Dashboard Graph - Most effective way to cross reference multiple sheets on one graph

jahmeanne

Hi all,

I have a team that wants one primary graph that will include the following information:

• Number of peaks / troughs
• Functional Team
• Date / Month

Basically the graph will extend out to May of 2024 and show the number of peak / troughs by functional team per month. The problem I'm running into is that this team utilizes three different sheets (Global, US, International) for this data.

My original plan was to create a sheet and use the following formula to get the number peaks/troughs by functional team for each month/year leading up to May 2024:

=COUNTIFS({GA - US Master Launch Plan PEAKS}, 1, {GA - US Master Launch Plan Functional Teams}, "Business Intelligence", {GA - US Master Launch Plan Range 1}, IFERROR(MONTH(@cell), 0) = 1, {GA - US Master Launch Plan Range 1}, IFERROR(YEAR(@cell), 0) = 2023)

Which ended up with a messy, but functional sheet that includes data of all three sheets (broken up by US, Global, and International (not seen in screenshot):

I was then going to create another sheet and use the below equation to combine the values of International, US, and Global (for example, US/Global/International "Business Intelligence" peaks and troughs in December 2022). The problem I've run into is that I can only cross reference 100 times. This barely scratches the surface of what I need to do. Is there another way for me to effectively combine all of this data? It looks like the sheet summary limit only goes up to 200. Far too limited for what I need to do.

Mike TV

@jahmeanne

Create a report from the three sheets and then base your graph off the report. You may have to rename some columns so values that are the same but called different things on the sheets are changed to be the same.