I have set up a financial statement using .RangeDept. When the report is expanded it works fine except the first row of data is all errors. The problem appears to be related to the fact that their are accounts (b/s) that do not have a department. I am only looking at i/s accounts but still think this may be the problem.

The key cells have the following in them when collapsed and expanded respectively:

Collapsed

A11 .RangeDept
B11 4000:9999-*
C11 has the formula "=TRIM(MID($B11,FIND("-",$B11,1)+2,LENGTH($B11)))" and returns the value "*"
D11 has the formula "=IF(TRIM($C11)=="*","Consolidated",IF(TRIM($C11)=="`",TRIM(DEPARTMENT(1)),PROPER(ADGET("gldept.desc",$C11))))" and returns "Consolidated"

Expanded

Row 11 is hidden

A12 '||** Generated Row **||
B12 4000:9999-
C12 has the formula =TRIM(MID($B12,FIND("-",$B12,1)+2,LENGTH($B12)))" and returns the value "Error!"
D12 has the formula ==IF(TRIM($C12)=="*","Consolidated",IF(TRIM($C12)=="`",TRIM(DEPARTMENT(1)),PROPER(ADGET("gldept.desc",$C12))))" and returns "Error!"

A13 '||** Generated Row **||
B13 4000:9999-100
C13 has the formula =TRIM(MID($B12,FIND("-",$B12,1)+2,LENGTH($B12)))" and returns the value "100!"
D13 has the formula ==IF(TRIM($C12)=="*","Consolidated",IF(TRIM($C12)=="`",TRIM(DEPARTMENT(1)),PROPER(ADGET("gldept.desc",$C12))))" and returns the correct department name

Any thoughts on how to get rid of this first error row?

Hi Ken,

The reason you are getting the error in Cell C11 for the 'blank' department is because the results of the cell in column B has no characters after the - thus null is getting returned for the function (or Error).

Thus you can do a check with an IF statement to see if - is the last character:
=IF(RIGHT($B11,1)!="-",MID($B11,FIND("-",$B11,1)+2,LENGTH($B11)),"")

This gets rid of the errors for both cells. But you'll need to come up with something for column D to print some description when the Dept code is blank:
=IF(TRIM($C11)=="*","Consolidated",IF(TRIM($C11)=="`",TRIM(DEPARTMENT(1)),IF($C11=="","Desc",PROPER(ADGET("gldept.desc",$C11)))))

Of course, replace "Desc" with what you want.

Perfect!

Thanks for you usual excellent help.