Solved: Find text, return near by text based on an offset(more efficient way?)

[piercedgeek]

I'm searching for a string, and returning the values under where that string is found. The string is changed often using a Validation dropdown. I was trying to use index/match/vlookup/hlookup and/or offset but couldn't find a way to return what I needed, so I made a Function to do it for me.
But there's a problem with the Function; it runs a little on the slow side on my work computer (something like 5-10 seconds... as soon as I got home and ran it, went down to 1 second heh...). But since this project will be used on my work system, and on other systems at work (which are even worse than what they give me!), I'm still interested in finding a more efficient way to get the job done.
Any ideas or suggestions would be most welcomed.

A note on the amount of time it takes to process, I'm using it in 336 cells.

Here's the function:


Function FindAndOffset(Find_String As String, Find_Sheet As String, _
Offset_Row As Long, Offset_Column As Long)
'Function created by Chris H. on 3/19/2007

'it runs a little bit faster when I just search the range:
FoundRow = Worksheets(Find_Sheet).Range("C57:CO57").Find(Find_String, _
LookIn:=xlValues, LookAt:=xlWhole).Row
FoundCol = Worksheets(Find_Sheet).Range("C57:CO57").Find(Find_String, _
LookIn:=xlValues, LookAt:=xlWhole).Column

'code with no range, might be useful for some people, or other situations:
'FoundRow = Worksheets(Find_Sheet).Cells.Find(Find_String, _
LookIn:=xlValues, LookAt:=xlWhole).Row
'FoundCol = Worksheets(Find_Sheet).Cells.Find(Find_String, _
LookIn:=xlValues, LookAt:=xlWhole).Column

FindAndOffset = Worksheets(Find_Sheet).Cells(FoundRow + Offset_Row, _
FoundCol + Offset_Column).Value

End Function


Sample:
=FindAndOffset(F1,"8 Week outlook",3,0)
will find the text of cell F1 on tab "8 Week outlook", and return whatever is in the same column, 3 rows down.

I've also attached a sample workbook with sensitive data removed.


Thank you,
Chris

Edited 20-Mar-07 by geekgirlau. Reason: insert line breaks

[mdmackillop]

How about

Function FindAndOffset(Find_String As String, Find_Sheet As String, Offset_Row As Long, Offset_Column As Long)
FindAndOffset = Worksheets(Find_Sheet).Range("C57:CO57").Find(Find_String, _
LookIn:=xlValues, LookAt:=xlWhole).Offset(Offset_Row, Offset_Column).Value
End Function

[piercedgeek]

I duplicated the formulas 15x, so now it takes a noticeable amount of time to load on this system, then switched between your version and mine, not really much of a change (though I know yours is technically more efficient, not sure why I did it my way, feeling kinda 'duh' now hehe). I'll try it when I get to work tomorrow still.

I was thinking about making an event trigger when the validation cell is changed, that will search for the text, then copy/paste the data to the main page...
But a set of formulas already in excel might be the best way, I dunno.

(edit, spelling)

[mdmackillop]

An alternative method. The yellow cell contains an offset value, the range below has a simple array formula offset by that value.

[geekgirlau]

Another option (without array formulas or vba) using index and match:

[piercedgeek]

That would be perfect if not for the weeks averaged together, it won't play well with that :(

[mdmackillop]

How about Post #4?

[piercedgeek]

Sorry, I missed that reply when I hit refresh. Just checked it out, appears to be working quite well here.
I'll give it a go at work tomorrow morning and update, but I have a feeling it'll be great!

[mdmackillop]

The "helper" cell can be incorporated into the array formula as
{=OFFSET('8 Week outlook'!C60:I107,,MATCH(F1,'8 Week outlook'!57:57,0)-3)}

[piercedgeek]

Works great! thanks again!