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!

 

[Trevor_S]

Will the KPIs always be in a consistent place? If so, you could put the cell references direct into the formula.
Alternatively, if they're always in a consistent row or column alongside the name of the KPI, you could build the indirect into a lookup formula.
Otherwise, if the positioning is completely random, a named range is best.

 

[ORoxo]

This is just something I made up, Trevor. As I wanted to do it as harder as possible let's assume they're always in a consistent row or column alongside the name of the KPI.

I could, indeed, build the indirect into a lookup formula, but I'm having trouble with that.

Any suggestion?

 

[Trevor_S]

Ok, say KPI name and value are somewhere in column A and B of each sheet. Try this in F8:
=VLOOKUP($E8,INDIRECT(F$7&"!A:B"),2,FALSE)

 

[ORoxo]

That would work if sale was a single number. But let's assume sale is a column at each vendor's sheet and has multiple values and at this main sheet I want the sum of it


I does sound way easier just to create a tag, I know...

 

[Trevor_S]

Assuming the KPI name is in the first row, would this work:
=SUM(OFFSET(INDIRECT(F$7&"!A:A"),0,MATCH($E8,INDIRECT(F$7&"!1:1"),0)-1))
Effectively its looking for the KPI name as a header, and summing the column.

 

[ORoxo]

Assuming the KPI name is in the first row, would this work:
=SUM(OFFSET(INDIRECT(F$7&"!A:A"),0,MATCH($E8,INDIRECT(F$7&"!1:1"),0)-1))
Effectively its looking for the KPI name as a header, and summing the column.

Wow! This works! I'm extremely surprised, honestly

Can you explain me the logic, please?

Thanks, man!

 

[Trevor_S]

The match looks for the word Sale in the top row of the sheet for the individual. Match formulae have three arguments, the third being zero to indicate an exact match - a bit like the false in a VLOOKUP. It returns the column number of the first cell in the top row that matches.

Offset is used to refer to a range that is a given number of rows/columns away from the original range. So, using the indirect, I've created a range that is column A of the individual's sheet. I'm then offsetting that by the number of columns in the bit of formula explained above, less one of course as we're already in the first column.

With the range defined, just need to put it in a sum().

Hope that helps!

 

[ORoxo]

The match looks for the word Sale in the top row of the sheet for the individual. Match formulae have three arguments, the third being zero to indicate an exact match - a bit like the false in a VLOOKUP. It returns the column number of the first cell in the top row that matches.

Offset is used to refer to a range that is a given number of rows/columns away from the original range. So, using the indirect, I've created a range that is column A of the individual's sheet. I'm then offsetting that by the number of columns in the bit of formula explained above, less one of course as we're already in the first column.

With the range defined, just need to put it in a sum().

Hope that helps!

Honestly, I didn't even remember to try offset to refer to a range - I mean, I never even thought offset could be used in a whole column - but that's actually a great idea!

It is clear now, thanks once again!

 

[Trevor_S]

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.