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