Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Find and Offset

Posted on 2012-08-24
Medium Priority
573 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
• 3
• 2
• 2

LVL 43

Assisted Solution

Saqib Husain, Syed earned 1000 total points
ID: 38328663
=index(R:R,match("Grand Total",O:O,0),1)
0

LVL 26

Accepted Solution

redmondb earned 1000 total points
ID: 38328665
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

ID: 38328674
Thanks guys
0

LVL 26

Expert Comment

ID: 38328688
Thanks, Seamus2626.
0

Author Comment

ID: 38328697
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

ID: 38328713
=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

ID: 38328719
Thankyou ssaqibh
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
###### Suggested Courses
Course of the Month12 days, 11 hours left to enroll