Can this be done with a formula??

[chris9277]

Hi,

I have a worksheet called : Trunking Activity Monitor Master

Now what I would like to do is within cell C25 is put some sort of formula which will look at a date in C26 within the same sheet. It would then look for a folder located

R:\BBD\BB\sites\track and trace\trend

Then look within worksheet:

T&T Summary 2011(v2007)

Tab:

Gate load weekly league

Then find the matching date within column C if this date matches it would then look at the 14 rows below it in column B for the word Wigan once found it would return the value next to it in column C.

I have a strange feeling that this will require a macro instead & if so does anyone knowthe code for this as I know very little about VBA.

Thank you for any help received.

 

[Joseph.Marro]

Chris,

This task would be far more simple in VBA and less prone to errors. The source file must be open for this formula to operate. so this solution will need to be a two part process.

First you can use a hyperlink to open the file (No VBA). In the same worksheet paste the following fomula in a blank cell:

=HYPERLINK("R:\BBD\BB\sites\track and trace\trend\T&T Summary 2011(v2007).xlsx","Open Source File")

If the source file is not open, click the hyperlink to open it.

Second, with the source file open, paste the following formula into cell C25:

=INDEX(INDIRECT("'[T&T Summary 2011(v2007).xlsx]Gate load weekly league'!"&ADDRESS(MATCH(C26,'[T&T Summary 2011(v2007).xlsx]Gate load weekly league'!$C:$C,0),2)&":"&ADDRESS(MATCH(C26,'[T&T Summary 2011(v2007).xlsx]Gate load weekly league'!$C:$C,0)+14,3)),MATCH("wigan",INDIRECT("'[T&T Summary 2011(v2007).xlsx]Gate load weekly league'!"&ADDRESS(MATCH(C26,'[T&T Summary 2011(v2007).xlsx]Gate load weekly league'!$C:$C,0),2)&":"&ADDRESS(MATCH(C26,'[T&T Summary 2011(v2007).xlsx]Gate load weekly league'!$C:$C,0)+14,2)),0),2)

Please Note: If the source file is ever closed and the worksheet is refreshed, the formula will produce a #Ref error. To correct this, click the hyperlink to reopen the file and hit F9 to refresh the worksheet.

Thank you,

Joseph Marro

 

[chris9277]

Joseph thank you very much for taking the time to do this for me. very much appreciated & works like a charm. Again Thank you.