truncating formula 1

[binaryfingers]

Hi

I have a formula that is being truncated by the cell for display.

Is there a limit to the number or characters that a cell can display?

Is there a way of being able to read the whole formula without it being truncated?
Thanks,

 

[sim133]

You can right click on it and Format field Common Tab check can grow box

 

[SkipVought]

Hi,

Thanks for sharing ALL the significant data in your post. Unfortunately, my crystal ball is in the shop. So could you please share with us:

Is the FORMULA truncating or is the returned VALUE truncating?

What is the FORMULA that you are referring to?

What is the data in the referenced cell(s)

What is the data that is being truncated?

How is the data truncated?

Skip,

_{[red]Be Advised![/red] A man who jumps from a bridge in Paris, is…
INSANE!}

 

[binaryfingers]

Skip

Sorry!

The formula is truncating in the formula bar.

This is what I can read...but I know it is missing some info. Also it is replacing some characters with alternative symbols.

I know the formula in its entirity is good as the spreadsheet works...I just want to copy it from one work book to another and change a couple of lookup references.

Hope this is enough info for you to be able to share your wisdom..

Thanks,

=IF(ISERROR(VLOOKUP($A8,INDIRECT("'[" & BZ$5 &".xls]" & $AQ$6 &LEFT($AV$4,1) &"'!"& $AY$2),MATCH($AQ$2,INDIRECT("'[" & BZ$5 &".xls]" & $AQ$6&LEFT($AV$4,1) &"'!"& $AX$2),0),FALSE))=TRUE,0,VLOOKUP($A8,INDIRECT("'[" & BZ$5 &".xls]" & $AQ$6&LEFT($AV$4,1) &"'!"& $AY$2),MATCH($AQ$2,INDIRECT("'[" & BZ$5 &".xls]" & $AQ$6&LEFT($AV$4,1) &"'!"& $AX$2),0),FALSE))+IF(ISERROR(VLOOKUP($A8,INDIRECT("'[" & BZ$5 &".xls]" & $AQ$6 &LEFT($AV$4,1) &"'!"& $AY$2),MATCH($AV$2,INDIRECT("'[" & BZ$5 &".xls]" & $AQ$6&LEFT($AV$4,1) &"'!"& $AX$2),0),FALSE))=TRUE,0,VLOOKUP($A8,INDIRECT("'[" & BZ$5 &".xls]" & $AQ$6&LEFT($AV$4,1) &"'!"& $AY$2),MATCH($AV$2,INDIRECT("'[" & BZ$5 &".xls]" & $AQ$6&LEFT($AV$4,1) &"'!"& $AX$2),0),FALSE))!ISERROR(VLOOKUP($A8,INDIRECT("'[" & BZ$5 &".xls]" & $AQ$6 &LEFT($AV$4,1) &"'!"& $AY$2),MATCH($AW$2,INDIRECT("'[" & BZ$5 &".xls]" & $AQ$6&LEFT($AV$4,1) &"'!"& $AX$2),0),FALSE))=TRUE 0 $A8 INDIRECT("'[" & BZ$5 &".xls]" & $AQ$6&LEFT($AV$4,1) &"'!"& $AY$2)

 

[AnotherHiggins]

Can you not view the enitre formula in the formula bar?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[binaryfingers]

higgins

unfortunately what is displayed above is what is coming straight out of the formula bar..

 

[sim133]

Sorry, I am in wrong forum..

 

[GlennUK]

You have references to columns after column Z in your formula ... if you delete some columns so that your references to columns AQ AV AX AY and BZ become single letters, you'll get to see more of the formula ( hopefully the entire formula ). You could then redesign it so that it is not so long .... for example by having another cell instead of BZ5 that includes the characters "'[" & ".xls]" ready for use within the INDIRECT.

Cheers, Glenn.

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

 

[binaryfingers]

Thanks for the advice Glenn
Will look to implement your advice.

 

[byundt]

The formula must be 1024 characters or less in length. You may be able to eliminate a few more characters by using ISNA instead of ISERROR when testing VLOOKUP or MATCH functions.

When using an IF function, you do not need the =TRUE part of your statement. The following two statements are equivalent:
=IF(ISERROR(some expression)=TRUE, value 1, value2)
=IF(ISERROR(some expression), value 1, value2)

I think you are doing something like this:
=IF(ISERROR(some complicated expression), 0, some complicated expression)
If so, consider using Conditional Formatting to hide the resulting error value rather than putting a 0 in the cell. This will cut the required formula length in half:
=some complicated expression

The Conditional Formatting for that cell (let's say it is cell A1) would then use "Formula Is"
=ISERROR(A1)
and then choose a white font color so the error value can't be seen.

Brad

 

[binaryfingers]

Thanks byundt for the help with the if and iserror tuncations.