Use a text value as part of a formula 2

[binaryfingers]

Hi

Is there a way of being able to use a text value as part of a formula

I have the following

VLOOKUP($B308,INDIRECT("'["&TEXT(C$7,"MMMM YY")&".xls]"&$B$307&$A$294&"'!"&'Master data'!$F$4),MATCH($A$293,INDIRECT("'["&TEXT(C$7,"MMMM YY")&".xls]"&$B$307&$A$294&"'!"&'Master data'!$E$4),0),FALSE)

That I would like to have in a cell as a text description.

I would then like to reference this text as the first part of a formula eg =A1, if the text above was in cell A1

The main reason is that I need to put in several versions of the above and I am now running out of formula space.

This is the current formula, that I need to add another similar clause too, hence why I would like to refer to different cells for each 'part' of the formula

=VLOOKUP($B308,INDIRECT("'["&TEXT(D$7,"MMMM YY")&".xls]"&$B$307&$A$294&"'!"&'Master data'!$F$4),MATCH($A$293,INDIRECT("'["&TEXT(D$7,"MMMM YY")&".xls]"&$B$307&$A$294&"'!"&'Master data'!$E$4),0),FALSE)+IF(ISERROR(VLOOKUP($B308,INDIRECT("'["&TEXT(D$7,"MMMM YY")&".xls]"&$B$307&$A$294&"'!"&'Master data'!$F$4),MATCH($A$292,INDIRECT("'["&TEXT(D$7,"MMMM YY")&".xls]"&$B$307&$A$294&"'!"&'Master data'!$E$4),0),FALSE)),0,VLOOKUP($B308,INDIRECT("'["&TEXT(D$7,"MMMM YY")&".xls]"&$B$307&$A$294&"'!"&'Master data'!$F$4),MATCH($A$292,INDIRECT("'["&TEXT(D$7,"MMMM YY")&".xls]"&$B$307&$A$294&"'!"&'Master data'!$E$4),0),FALSE))-VLOOKUP($B308,INDIRECT("'["&TEXT(D$7,"MMMM YY")&".xls]"&$B$307&$A$294&"'!"&'Master data'!$F$4),MATCH($A$307,INDIRECT("'["&TEXT(D$7,"MMMM YY")&".xls]"&$B$307&$A$294&"'!"&'Master data'!$E$4),0),FALSE)

Hope this makes sense.
Thanks,

 

[GlennUK]

Why don't you just redesign your formula to be more efficient? You have the string

"'["&TEXT(D$7,"MMMM YY")&".xls]"&$B$307&$A$294&"'!"&'Master data'!$F$4

referenced 4 times within that formula. Put it in a cell and refer to it instead.

The same goes for:

"'["&TEXT(D$7,"MMMM YY")&".xls]"&$B$307&$A$294&"'!"&'Master data'!$E$4

which is also referenced 4 times.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[binaryfingers]

Hi Glenn

Thanks for the response...that was what I was looking for..although I think I must be referring to it incorrectly...as I get a REF error.

Could you help point me in the right direction..

Thanks,

 

[GlennUK]

Quote:

I think I must be referring to it incorrectly...as I get a REF error.

I would help you if you'd posted the formula as it now is. It would help is you also gave the results in the referenced cells too.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[binaryfingers]

Hi Glenn

Please find below the coding that is currently being used.

=VLOOKUP($B308,INDIRECT("'["&TEXT(C$7,"MMMM YY")&".xls]"&$B$307&$A$294&"'!"&'Master data'!$F$4),MATCH($A$293,INDIRECT("'["&TEXT(C$7,"MMMM YY")&".xls]"&$B$307&$A$294&"'!"&'Master data'!$E$4),0),FALSE)

I have tried to take your recommendation and cut the following section out and placed it into a cell by itself

INDIRECT("'["&TEXT(C$7,"MMMM YY")&".xls]"&$B$307&$A$294&"'!"&'Master data'!$F$4)

exactly like that, eg no = sign in front.

I then want to try and 'splice' that back into the formula for it to perform correctly..

I tried all sorts of ways, including the following

=VLOOKUP($B308,$C$307,MATCH($A$293,INDIRECT("'["&TEXT(C$7,"MMMM YY")&".xls]"&$B$307&$A$294&"'!"&'Master data'!$E$4),0),FALSE)

I also tried it with a & sign in front of the $c$307

I did also try different lengths of the text eg without the indirect and leaving that in the formula etc.

It just provides a REF error

its probably something really simple that Im just missing..

appreciate your further advice.

 

[GlennUK]

That is not my recommendation. Read my post again. Put the strings I mentioned into cells, and not just any part of the formula you feel like.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[binaryfingers]

Sorry Glenn

I did try that as well...but the first " seems to get lost when putting it in the cell.

I have now just copied it directly from above and placed into cell c308. The first " is still there

I then use the following

=VLOOKUP($B308,INDIRECT(C308),MATCH($A$293,INDIRECT("'["&TEXT(C$7,"MMMM YY")&".xls]"&$B$307&$A$294&"'!"&'Master data'!$E$4),0),FALSE)

It still applies a REF error.

Im sorry if I am still missing something simple...

 

[GlennUK]

It is a calculated string ... you have to have an = before it to get it to calculate.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[binaryfingers]

Glenn

Got it now, Thanks!

I have given you a star, as I think your patience with me deserves it!

 

[GlennUK]

My pleasure. Your formula should be about 500 characters shorter now.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.