complicated query match formula!!

[jpowell79]

Hi guys,

I have quite a complicated formula I need some help with...any help is greatly appreciated!

ok, where do I start!!

right, I have a worksheet called "Details" and in this worksheet I have a column (column H) which is full of data, starting from cell H5. Each cell in column H, each has a unique idenitfier, which is the last 8 digits of each cell.

These 8 digit numbers represent unique worksheets, which start with "Sheet" and then follow with the 8 digit number.

So for example,

Cell H5 may contain the following:

Monday 17th - Packard Bell: 21083209

Therefore there will be a worksheet called "Sheet21083209"

If you bear this in mind for a minute, I'll explain the other part.

Ok, now the cell to the right of H5 (i.e. I5) will contain a group of words, followed by a space then a colon ) and then a number.

For example, cell I5 might contain the following:

version 6.72 : 29023

Ok, what I need to do is find the word/s that come before the colon, in the correct worksheet adjacent to I5 (i.e H5) which will be somewhere in column A of the correct worksheet.

So for example, if cell I5 contained "version 6.72 : 29023", then somewhere in column A of worksheet "Sheet21083209" there will be "version 6.72"

ok, so once we establish exactly which cell that "version 6.72" is in in worksheet "Sheet21083209", I need the value found in the adjacent column D.

So if "version 6.72" was found in cell A21 in worksheet "Sheet21093209" then essentially, I want the value of cell D21

The final value would then be shown in cell A5 of the "Details" worksheet. Ideally this will be a formula I can copy all the way down column A. so A5 relates to H5/I5 and A6 relates to H6/I6 and A7 relates to H7/I7

hopefully that makes sense??

I'll do one more example.

Lets say cell H6 of the "Details" worksheet contains "Wednesday 29th - Sony: 21123339" and cell I6 contains "type 2.44 : 29023", then in worksheet "Sheet21123339", I want to look up "type 2.44" in column A, and find the value in the adjacent column D


I know, its a tough one!!!


I'd be forever grateful to the genius who can crack this!

thanks guys!!

 

[Domenic]

Try...

=VLOOKUP(TRIM(LEFT(I5,FIND(":",I5)-1)),INDIRECT("'Sheet"&RIGHT(H5,8)&"'!A2:D100"),4,0)

Adjust the range, A2:D100, accordingly.

Hope this helps!

 

[jpowell79]

what can I say.... GENIUS!!!

thanks again

 

[jpowell79]

This formula works very well, but the only thing is its very CPU intensive!

Am I right in thinking this is CPU intensive due to the INDIRECT and VLOOKUP elements of the formula??

Is there any way this formula could be re-written so that it's less intensive??

Thanks

 

[jpowell79]

anyone??

 

[jpowell79]

I guess this one can't be improved then?

 

[Domenic]

INDIRECT is a volatile function, which prolongs re-calculation times. How about changing the calculation mode to 'Manual'? Then calculate (F9 on Widows, COMMAND+= on Mac) as needed.

 

[jpowell79]

Hi Domenic,

Unfortunately this isn't really an option for me (long story!) as this needs to work without any user intervention.

Could it be changed in any way to a non-volatile formula?