Is there a formula that can be used in FX to display each department in its own column for comparative reporting?

=ADGET("GLDATA.Y",$B12&"-"&I$5)

Will return the year to date value for a account/dept pair.

Where B12 is the .Range and I5 is the department number in the column header.

How should the department in I5 be displayed? I had 'DEPT{100}' in it but it doesn't work.

Simply as follows:
'1022

Ok. But if I do that and I have the consolidate button on to consolidate, I get 'error' in my amount fields.

Miriam:

Look at the statement "GL!SmartSheet101.sam" in the GL sample data. And especially look at the sheet labelled "Basic 4: Departmental Comparative". It's slightly more complex because it uses a range of Departments and the RNGMERGE function but the concept is the same. Note the way that the Account number in column B is defined.

Thanks Bruce, that helped a lot!