Code throwing error to formula in sheet while converting rel to abs

[dorabajji]

Hi All,
Below is the formula.

+IF(SUM(W37:W38)<>0,0,IF('account_type_transactions (2)'!O584=0,SUM('account_type_transactions (2)'!O584:O585),IF('account_type_transactions (2)'!O584=1,SUM('account_type_transactions (2)'!Q584:Q585),IF('account_type_transactions (2)'!O584>2000,SUM('account_type_transactions (2)'!Q590:Q591)))))

In this formula, when i try to do

Please Login or Register  to view this content.

It is throwing #value error...
Why is it so?
for nested loop this is throwing..

[Norie]

There's a 255 character limit to the length of the formula you convert using ConverFormula.

Mind you, it's not clear how you are trying to convert the formula - all the references in it are relative as it is.

[dorabajji]

Hi Norie,
In this case , then how i need to do.
I want the references to change into absolute ref in the formula.
I have around 10000 lines of different nested if formulas, i want make the reference to be absolute ref..

[Norie]

If you want to change the references in the formulas to absolute for both row and column then use 1 instead of 4.

To get over the character limit you could either temporarily change the name of the sheet referenced in the formula to a shorter name, do the conversion and change the name back.

Or you could use code to temporarily replace the sheet name in the formula, do the conversion and then restore the sheet name.

The former is probably the easiest way to do it.

[dorabajji]

Hi ,
I changed but still it turns into #value..
Any custom function to turnaround this... or how to temporarily change? i am not getting it? Can you provide the eg in the above one..

[dorabajji]

Hi Norie,
The total formula is this..


IF(SUM(X2252:CF2252)=0,0,IF(CJ2263=0,'account_type_transactions (2)'!D18,IF(CJ2263=1,SUM('account_type_transactions (2)'!D18:E18),IF(CJ2263=2,SUM('account_type_transactions (2)'!D18:F18),IF(CJ2263=3,SUM('account_type_transactions (2)'!D18:G18),IF(CJ2263=4,SUM('account_type_transactions (2)'!D18:H18),IF(CJ2263=5,SUM('account_type_transactions (2)'!D18:I18),IF(CJ2263=6,SUM('account_type_transactions (2)'!D18:J18))))))*EW2263)))

[dorabajji]

And i tried putting dummy sheet names for this above code.. The character came around 70, but still i got #value.

[Norie]

You could probably shorten that formula quite a bit.

=IF(SUM(X2252:CF2252)=0,0,OFFSET('account_type_transactions (2)'!D18,0,0, CJ2263+1)*EW2263)

[dorabajji]

Hi Norie,
Thanks..
this is useful, but for me to change whole workbook with different ranges, it is tedious..
Can there be any alternative custom function to application.convert formula.. and the character limit is only for the sheet names right not for the formula? or this will also be included?

[Norie]

The character limit is for the total no of characters in the formula.

If you had a shorter sheet name in the formula, the current one has 29 characters and is repeated 7 times in the formula which is 203 characters, then the code might work.

[dorabajji]

Yes, but in other sheets, i have 10 characters and is repeated same 7 times. but also it throws error? Thats y i asked

[dorabajji]

Hi Norie,
That dummy sheet and the formula is below
=+IF(SUM(Y38:Y39)<>0,0,IF(sf!Q585=0,SUM(sf!Q585:Q586),IF(sf!Q585=1,SUM(sf!S585:S586),IF(sf!Q585>2000,SUM(sf!S591:S592)))))

For this also i got #value..

[romperstomper]

Convertformula will translate references to other sheets as external references and add the workbook name. If that returned formula ends up longer than the 255 characters, you get an error.

[dorabajji]

Hi,
Yes,but i am looking for a turnaround way..
i tried as norie suggested to change the name.. but also it showed error..
I tried to do with split function.. in that i am facing issue with respect to cell reference..
For eg: the function works fine, if the cell ref are same..
In the above formula for sf sheet i get Q585..
if it changes QR5850 like this , then i am not able to..

I want to know, in the above formula , how to extract cell address in the formula..

the formula is

=+IF(SUM(Y38:Y39)<>0,0,IF(sf!Q585=0,SUM(sf!Q585:Q586),IF(sf!Q585=1,SUM(sf!S585:S586),IF(sf!Q585>2000,SUM(sf!S591:S592)))))

I want to extract Q585 from the formula,
So that i know how many letters and numbers, accordingly i can use left to add abs ref..

[dorabajji]

Hi Rorya sir,
then also it comes to testingg is my name workbook name..
So if it repeats 10 times it comes to 80 and +70...
Comes to 150? within 255 only no..

[dorabajji]

I tried splitting the formula in to array strings then applying convert formula.. but this also had error..
as split was done using "," and i think it didnot complete a formula..

Please Login or Register  to view this content.

[Norie]

This is really rough but might work.

Please Login or Register  to view this content.

[dorabajji]

Hi Norie,
Is it possible to use array in the above as i have a range.. I have modified the above into range, but still i have more than 500 rows, so it is slow..I am not too familiar with array..