Referencing more than 1 sheet on a metrics summary sheet

deb_63_hydracor

I need to create graphs for a dashboard that reference data from a US sheet and from a Europe sheet. The data for one graph changes when I try to reference another sheet. Is there a work around for this?

Thanks!

Samuel Mueller

@deb_63_hydracor You would need to create a separate sheet that cross references to both country sheets and collects the data you need with formulas. Then in your dashboard you would reference the newly created sheet instead.

deb_63_hydracor

I understand what you're saying, but I can't seem to reference more than one sheet, so currently can only reference the US or Europe, but not both.

Samuel Mueller

@deb_63_hydracor You can't do that in the dashboard. You have to do it in a separate smartsheet.

deb_63_hydracor

I AM trying to do it on a smartsheet.

Samuel Mueller

@deb_63_hydracor Gotcha okay. So do you know how to make a cross sheet reference?

In your metric sheet you will make some cross sheet references to each Country sheet, US and Europe separately, and then you will have to use formulas to bring in the data you need from each sheet.

it depends on the type of data you are trying to bring in, can you provide an example?

But something like =sumifs({US data to Sum}, {US Criteria}, "US Data")

then in the next cell =sumifs({Europe data to Sum}, {Europe Criteria}, "Europe Data")

deb_63_hydracor

Yes, I can make a cross-sheet reference. I'm trying to bring the same data in from both the US and Europe sheets. Like this, but I will need to add "Country" to the formula.

=COUNTIFS({Tier}, "Experiencia Lite", {Renewal Month}, 1, {Renewal Year}, 2023)

Once I have the formulas all set for the US sheet, when I try to create a new formula to pull the same data from the Europe sheet, the reference changes the formulas for the US (NorAm) column.

It's difficult to explain so hopefully you understand what I mean. Any ideas?

Thanks!

Samuel Mueller

@deb_63_hydracor for the NorAm column you need to create cross sheet references for all the NorAm sheet data and for the UK column you need to create all the cross sheet references for the UK sheet sheet data.

So your cross references would be more like: {TierUS}, {Renewal MonthUS}, {Renewal YearUS}, then you would have the same in addition for UK referencing to the UK sheet.. {TierUK}, {Renewal MonthUK}, {Renewal YearUK}.

Then your formula in the NorAm column would be

=COUNTIFS({TierUS}, "Experiencia Lite", {Renewal MonthUS}, 1, {Renewal YearUS}, 2023)

and your formula for the UK column would be

=COUNTIFS({TierUK}, "Experiencia Lite", {Renewal MonthUK}, 1, {Renewal YearUK}, 2023)

Does that make sense? In this example you will have 6 cross sheet references, 3 for US, 3 for UK

deb_63_hydracor

I finally figured it out. Thanks so much for your help!