Hello

I'm using Adagio Ledger 8.0D 061013 with the .RowFormat and FormatHidden to try and make the Bank row on a Balance Sheet jump up and down the sheet according to the balance being an Asset or Liability. Instead it's making me jump up and down... I've done this successfully a couple times before but here are my current problems:

1. Since the sheet has multiple columns the possibility exists that the row could have a Debit balance in some columns and a credit balance in others. While I have "successfully" handled this before I wonder if it's just that I've been lucky that the data never encountered a situation where the column data was actually mixed (asset & liability). Can this "mixed" row be handled properly ? Any sample data with specs that can show the correct formulas to handle this ?

2. The spec is hiding some rows and I just can't seem to get them unhidden no matter what I do and I've tried everything. Everything except what works. So go ahead and embarass me by telling me how simple this is. Among other things, I've tried removing the .RowFormat and Recalculating, Selecting across the rows and RightClick|Unhide, Selecting entire sheet and Format|Rows|Unhide - No luck can't get back the rows to troubleshoot them.

Help

Pete

Hi Pete,

For the first question, what is the condition that you want to show for showing the bank account? If it is simply the debit/credit condition of the bank account for a specific column, it doesn't matter whether any of the other column values are:
=IF($D6=0,"FormatHidden","") goes in the RowFormat column.
=ADDEBIT and =ADCREDIT is used for outputting the bank amount.
If using a SmartSheet, you should separate the bank account out from the other accounts and be on a separate row.
A sample spec GL!BankLOC shows formulas.

If you want to have different hiding results of row based on 'mixed results', then you need to consider what conditions force a row to be hidden. Perhaps all columns need to be credits (or debits) in order to be hidden. Remember, you don't want the bank account to be showing in both or neither of Assets/Liabilities.

When doing formulas with FormatHidden, it is best to not have .RowFormat in the top corner until you are sure things are working. You can tell if a row will be hidden by seeing 'FormatHidden' in the right-most column.
------
As for your second question, the scenario is likely that a row has been interpreted as having zero height, rather than being 'hidden'. The likely solution is to highlight the rows above and below the missing row, then re-size any of the visible rows. This will force a resize of all rows to match, including the missing ones.
-------
There are a couple of FormatHidden issues not hiding/showing correctly that should be resolved in the next release of Ledger. These changes have already made it into the FX80G release.

Hi Softrak,

Adjusting the row heights did the trick - thanks.

Regarding the "floating" Bank row, I do not understand why you thought/think that "it doesn't matter what any of the other column values are" and the sample spec noted does not use multiple columns so the formulas there weren't much help in that regard.

OK so here's the scenario:

> Col.D Current period is June 2007, Balance is CR $1,000
> Col.E Budget Current Period June 2007, Bal is DR $ 900
> Col.G Prior Year, June 2006 the balance was DR $ 700

Acknowledging that Budget is an unusual column on a Balance sheet, my feeling is that
> all 3 columns of the Bank should only show in one section (row)
> and that section should be in the Current Liabilities
> I also believe that the spec. should be able to handle any
combination of the above, continuing to show the single Bank
row in the section that corresponds to the Current Period
Balance
> Whichever section the Bank Row appears in, then it should
be "hidden" in the other section.
> And yes, the figures must add up and not be duplicated :-)

I tried accomplishing this using the formulas you noted from the sample spec above and bearing in mind that you said the "other" column values did not matter - I just could not get it to work.

So I went back to my method:

Liabilities Section:
Col.D: =ADCRONLY("GLDATA.BAL",$B40)
Col.E: =IF($D40==0,0,-ADGET("GLDATA.BM",$B40))
Col.G: =IF($D40==0,0,-ADGET("GLDATA.BAL_PY",$B40))
Col.I: =IF($D40==0,IF($E40==0,IF($G40==0,"FormatHidden",""),""),"")

Asset section similar but with =ADDEBIT and the embedded =ADGET commands would not have the "-" sign in front.

Seems to work, the only drawback being that it would appear that one looses the "Drill Down" ability if the AD??? function is embedded within other functions.

If there's a way to accomplish this and still get full drill down capability, please let me know.

Thanks for bearing with all that and I hope that someone will find it useful - Peter

Hi Peter,

Since you want the position of the row to be dependant on the current balance in the bank account, and not what it's balance was last year, or the budget figure, then the ".FormatHidden" command should reference column D only. Then, just present the other colums with their correct sign (debit or credit) as appropriate.

Hi Andrew,

I agree that the ".FormatHidden" only needs to reference column D. After having some troubles with the "hiding/unhiding" of the row behaving erratically, I just left the redundant references to the other columns from when I was troubleshooting.

"Then, just present the other colums with their correct sign (debit or credit) as appropriate." Sorry, but this is the part that I can't seem to get right.

I have tried by:
> using ADDEBIT/ADCRONLY accross all Asset/Liability columns
PROBLEM: Bank row may need to display in both Sections

> using the ADDEBIT/ADCRONLY in the Current Period column only with the standard ADGET and -ADGET in the other columns depending on the Asset/Liability section
PROBLEM: Balances arrive in both sections so even if one hides the row the totals are wrong.

From the guru comments thus far, I know that I'm most likely missing something quite straightforward, so ...

Andrew I'm going to zip the GL data and email it to you for a look. It's a good dataset for testing because as you flick through the periods many of the "mixed" scenarios come into play.

Thanks, Peter

Hi Peter,

Did you remember to send me the dataset? It hasn't arrived.

To deal with the problem of the incorrect total, just make the =SUM() part of an =IF() statemnt that excludes the row containing the bank balance if it is hidden. So, for the total you need something like:


or, if you'd prefer, place an =IF() condition on the adjacent colums containing the prior year and budget figures so that they return 0 if the bank column has 0. For example:



That should work.

(I'm still willing to take a look at the data though.)

Hi Andrew,

The data was taking it's time crossing the Caribbean Sea.

Your second option is the one I am currently using in the example above so I guess I was on the right track after all.

I think I'll look at using your first option though, as this would permit drill down on the details for all 3 columns whereas the Financial Reporter does not seem to allow drill down in situations where the ADGET() functions are embedded within an IF() function.

Thanks for all the help, Peter

OK Guys,

I tried the option of using the IF() function in the Total row and leaving the ADDEBIT/ADCREDIT without the IF() so as to keep the drill down ability but this does not handle all conditions.

The only way I'm getting all conditions handled is to use the IF()in the Budget and Prior Year columns so I'll stick with that.

BTW - For anyone trying this, in the examples in my above posts, I should have said ADCREDIT not ADCRONLY. So to sum up, this is what's working best:

Liabilities Section:
Col.D: =ADCREDIT("GLDATA.BAL",$B40)
Col.E: =IF($D40==0,0,-ADGET("GLDATA.BM",$B40))
Col.G: =IF($D40==0,0,-ADGET("GLDATA.BAL_PY",$B40))
Col.I: =IF($D40==0,IF($E40==0,IF($G40==0,"FormatHidden",""),""),"")

Asset section similar but with =ADDEBIT and the embedded =ADGET commands would not have the "-" sign in front.

Seems to work, the only drawback being that it would appear that one looses the "Drill Down" ability if the AD??? function is embedded within other functions.

Thanks again, Peter

Hi Peter,

What are you drilling down on? There's nothing to drill down to for budget and prior year amounts anyway.

Hi Andrew,

From a consolidated total to the first levels that show the individual accounts and departments and I believe it goes to the indididual periods for a specific account as well - but no, not to a transaction level.

It was more of an observation than the drill down did not work in cells with that type of formulae and that it could possibly be a drawback in some instances than the fact that it was a drawback in that particular instance.

Thanks, Pete

Hi Andrew and Peter - Another thing to be aware of is that if the line containing the ADDEBIT or CREDIT has several accounts grouped, and within that group there is both a debit and credit balance, the formula will not separate the debits and credits. I was wondering why the balance sheet didn't balance until I did a drill down and the first level gave me both the debit and credit balances. The way to get around this is to have the accounts on separate lines.

Lars

Hi Lars,

Are you sure you are using ADDEBIT and ADCREDIT ? I have not found the results you are describing when using those functions. What I have found is that ALL accounts in the range are totalled regardless of Debits or Credits and the resulting net total, Debit or Credit, is what the function acts on.

On the other hand, the ADDRONLY and ADCRONLY can lead to the results you described. Those functions total ONLY the Debits or Credits in the account range.

Take for example an account range with the following balances: $1,000+ and $600-. ADDEBIT would return $400+ whereas ADCREDIT would return $0 because the net total of the accounts is a Debit. However, on the same range ADDRONLY would return $1,000+ and ADCRONLY would return $600-

Don't know if we could be getting different results due to different versions of Ledger but the above is what I've found with 8.0D (061013).

Pete

BTW Softrak Sample Spec creators - The Sample Spec GL!BankLOC uses ADDRONLY in the Assets and ADCREDIT in the Liabilities. The ADDRONLY works fine in this spec as only a single account is referenced. However, it might be better to use ADDEBIT as this handles both single and multiple account references. When I first started doing that type of spec I ran into the problem Lars indicated since I had copied the formula from the Sample Spec and did not even realise that an ADDEBIT existed and was distinctly different from an ADDRONLY. Might be a help to other lazy (don't read manuals) souls like myself from making the same error :-)

Hi Peter,

Your understanding of the differences between ADDEBIT and ADDRONLY, and ADCREDIT and ADCRONLY is corrent.

Yes, we could change the sample spec. Just one of those things.