Cross Sheet Reference Formula to Dashboard Question

swhitfield

Greetings all,

I am currently trying to build a dashboard that will display resource name (e.g. Bryan Rhoad) along with the following from a sheet named HyBridge_Resource Plan 2021

• Dept = Kronos

• Team Members that have 20+ hours or more available

• Team Members that have 30+ hours or more available

• Team Members that have 40 hours available

I am thinking of using a cross sheet @row formula that will trip the status colors red, green, yellow if the criteria above is met.

This example shows Bryan at 30 hours (between 10 and 30) in the first image @ 5/31 if I alter the project hours by 20 it jumps to 50 with (>30) formatting color code and increases font. The last image is 0 hours where it turns red (<10)

Sheet Name : HyBridge_Resource Plan 2021

The purpose here is to see a “quick/easy” summary of who is available or coming available to staff on upcoming projects looking for the logic as the hours go up and down causing the colors/fonts to change. Ultimately, this data will be used to create a dashboard where managers can see over allocation and under allocation at a glance.

Thank you in advance for your time and I have some sample sheets available if a direct share would be easier to work on ideas.

Genevieve P.

Hi @swhitfield

The way your sheet is set up, with dates being columns (instead of having one date-type of column with the dates going down rows) makes this a little tricky.

You could create a Report which only grabs Parent Rows, showing the Resource Name & specific columns. This will carry through the formatting you set up on your source sheet, and you can display this Report on your Dashboard.

I presume you have a helper column in your sheet which identifies these Parent rows, then colours them Grey, is that correct? If so, this same helper column would be your Filter criteria in the Report, so you only see top-level rows.

However you will need to manually select what columns to include in; the Report won't be able to dynamically add/remove columns based on the date.

Would this work for you?

Cheers,

Genevieve

swhitfield

Thank you Genevieve,

I looked at that route and wasn't keen on the 'manual' part myself which lead me to and out link from the source sheet to my calculation sheet ultimately to the dashboard that updates automatically. See attached screenshot.

Essentially, keeping the 40 hour per week as static and logging the delta of a resource that ultimately gives 'available hours during a week' and visually one can see it over time.

I tinkered with the status color change but it became too busy for a snapshot type view.

=IF([5/24 Available Hours]@row > 30, "Red", IF([5/24 Available Hours]@row > 19, "Gray", IF([5/24 Available Hours]@row > 10, "Yellow", IF([5/24 Available Hours]@row <= 10, "Green"))))

So, I gathered the deltas over time and plotted them via a widget dashboard line chart that easily shows when a resource has 20 hours, 30 hours even 40 hours of availability - see attached screenshot for reference.

Genevieve P.

Hi @swhitfield

Thank you for sharing your solution! I'm glad you were able to produce a visual that works for you.