Indirect and Sum

[ORoxo]

Guys, for fun, I'm trying to learn something new.

I was wondering if it would be possible to have a report like the one on the picture. In columns, you would have the vendor's name and in rows, the KPI associated.

The information would be pulled from the sheets which would have the vendor's name. In other words, in this case, I would have three sheets named Diogo, Fernando and João each with the KPIs mentioned, Sales, Profit and Player.

So far, I have been able to retrieve the information from the other sheets by using the following formula:

=SUM(INDIRECT(F$7&"!"&$E8))

However, this requires me to create range names. I was wondering if there's an alternative that works with formulas alone.

Thanks!

 

[ORoxo]

No problem. For completeness, as I've recently been reminded in a different thread, offset and indirect are both "volatile" functions. This means that they are recalculated whenever anything anywhere on the spreadsheet is changed - which can noticably impact on recalculation times if there are a lot of them on a very large spreadsheet. But provided that you're not looking at too many KPI's / individuals, it shouldn't be a problem.

For the purpose of my example it works! Thanks