Solved

# Find and Offset

Posted on 2012-08-24
522 Views
Hi,

Can i have some formula or code that will loop down Column O and find "Grand Total" and then return the offset (0,3)

Thanks
Seamus
0
Question by:Seamus2626

LVL 43

Assisted Solution

=index(R:R,match("Grand Total",O:O,0),1)
0

LVL 26

Accepted Solution

Hi, Seamus2626.

=OFFSET(\$O\$1,MATCH("Grand Total",O:O,0)-1,3)

Edit: ssaqibh's solution is better as OFFSET is volatile (i.e. it'll be recalculated for every calculation) whereas INDEX is not (i.e. only recalculated when necessary).

Regards,
Brian.
0

Author Closing Comment

Thanks guys
0

LVL 26

Expert Comment

Thanks, Seamus2626.
0

Author Comment

Just trying to link that through a different sheet and getting an error

='Top 20 B Pools - GLA Wealth'!OFFSET(\$O\$1,MATCH("Grand Total",O:O,0)-1,2)

Can you amend the syntax?

Thanks
Seamus
0

LVL 43

Expert Comment

=OFFSET('Top 20 B Pools - GLA Wealth'!\$O\$1,MATCH("Grand Total",'Top 20 B Pools - GLA Wealth'!O:O,0)-1,2)
0

Author Comment

Thankyou ssaqibh
0

## Featured Post

### Suggested Solutions

Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…