I have several spreadsheets in Adagio Fx and am trying to have one of the sheets summarize the accounts which are on various other sheets within the report (summarizing three companies into a consolidated report (Cannot import and use just one report, as the companies do not have similar account structures, and two require foreign exchange revaluations).

I am using the syntax =querysheet("sheet name","cell ref")which should return the number that is in that worksheet at that cell reference, from what I understand, but I keep getting numbers that bear no relationship to the numbers that are in that cell. The worksheet I am trying to reference is a roll-up, and I am wondering if that is having an effect on the cell reference, however the number I am receiving is not any one of the numbers of the accounts when I do a roll down on the data?

My reseller does not know the answer to this problem either

Hi Carol,

You have found the reason for not referring directly to a cell with the QuerySheet function, as when the SmartSheet is collapsed or expanded, the cell you want to display on a different sheet is now in a different place.

What you need to do is 'name' the cell that you want to appear in the QuerySheet formula, instead of using a cell reference. To do this, go to the sheet and cell that you want to appear on a different sheet. Right-click on that cell and choose 'Create Named Range'. On the screen that appears, replace 'NewName' with a meaningful name for that cell, such as Balance or SalesRevenue or ExpensesTotal or whatever. Don't have spaces in the name. Click the Add button to add it to the list, then click Done to save everthing and close.

Then in the QuerySheet formula on the other sheet, use this name instead of a cell reference. So your formula might look like: =QuerySheet("Company A Income Statement","TotalRevenue")

Note that you cannot create a name for cells that appear on Generated rows. This is because Generated rows are deleted when a sheet is collapsed. This only works for cells on rows that are not generated. Should you require values from generated rows (detail rows on expanding) on another sheet, you cannot use the QuerySheet process for doing this, and will need to create your financial statement in a different way. If this is the case, you may be better served contacting your Adagio consultant for assistance.

Thanks, I can see why it isn't working. It's really too bad this program doesn't have more Excel functionality for referencing cells - ie moving a referenced cell changes the reference on the the Query page. I know I could export this to Excel with the formatting etc., but that is not a viable option in this instance where I am consolidating several companies in different currencies.

Hi Carol,

There's no reason that this shouldn't work. After you name the cell, what is causing the problem?

The problem is the sheer number of cells that I have to name. I need to consolidate three companies and three full sets of working papers, and every "total" line for the roll-down items that are grouped requires a unique name.

If all you are interested in is the totals, then why not have a sheet that just contains the necessary totals without using the ".Range" command so the rows don't expand and collapse?