Cross-Sheet reference

[Vlemmings_Roel]

Hi,

I just wanted to know if there is a way to accomplish cross-sheet reference. We have a template that has a 'consolidated' first sheet which has formulas that might reference the other subsequent sheets that are dynamically generated. I have read that, FlexCel processes sheets one by one starting from sheet1. So, my question is how can we write a formula on sheet1 that references sheets that are not present at the design time. Is it possible to do?

[adrian]

Hi,

While indeed FlexCel processes the formulas one by one starting from the first, before that there is a pass that creates and names all dynamic sheets, so you can reference them from the report.

For example, I tried writing:

<#formula>=confections!a1

In cell A22 of the first sheet in file Multiple sheet report.template.xls in Multiple sheet report demo, run the demo, and it correctly refers to the confections sheet, which doesn't exist at design time. Note that of course, the name must exist in the final report: If the sheet name doesn't exist once the report is generated, it will contain a #REF! error.

Would this work in your case?

[Vlemmings_Roel]

Hi Adrian,

Thanks for the quick response. Although, this gives me some directions, it is not quite what we want. We don't know if 'confections' would exist in the final report during design. We would like something like =SUM<#Categories.Name>!a1. A formula that can grow. Let me know if you think of any idea.

Thanks.

[adrian]

Yes, of course "confections" was just for testing in that particular demo.

<#formula>=SUM(<#Categories.Name>!A1) would work, but probably not in the way you would like. (it would sum a1 in each sheet)

You would need something like

<#formula>=SUM(<#FirstSheet>:<LastSheet>!A1)

where FistSheet is a report vsriable with the first sheet and lastsheet one with the last.

[Vlemmings_Roel]

Hi Adrian,

Are FirstSheet and LastSheet are FlexCel keywords or the custom variable name?. If it is custom, how would I specify it references first and last sheet? Is there any keyword for that? Sounds like I need to declare the variables in the config sheet.

[adrian]

In this example, those would be 2 report variables that you set in code, before calling report.Run.

For example:

report.SetValue('FirstSheet', 'confetti');

report.SetValue('LastSheet', 'mylastsheet');

Of course, in practice it would be more like:

dataSet.Position := 0;

report.SetValue('FirstSheet', dataset.SheetName);

dataSet.Position := dataSet.RecordCount - 1;

report.SetValue('LastSheet', dataset.SheetName);

But I don't know what datasource you are using, it might also be List[0] and List[List.Count - 1], etc.

What is important is that you set the FirstSheet to be the string with the first sheet you want to sum and lastsheet with the last.

Note that you could also go even more generic and do something like:

report.SetValue('myformula', '=SUM('FirstSheet'!A1:'LastSheet'!A1');

And then in the template write

<#formula><#myformula>

I don't know the specifics on what formula do you exactly want to use, so I can't give you an exact answer, but ideas on how to get them. The important part is that the sheet names are available when you run the report, so you can really use any string with <#formula> tag: The <#formula> is needed because the formula wouldn't be possible to write in Excel when the template doesn't have those sheets, but when you use <#formula> what you need is that the formula is possible to write when running the report (and at that time, the sheet names are available).

You could even use stuff like <#list()> ( http://www.tmssoftware.biz/flexcel/doc/vcl/guides/reports-tag-reference.html#list ) to get a list of the sheets, and do a =Sum(sheet1!A1, Sheet2!a1...) but I would strongly recommend you NOT to do this. Formulas in Excel are limited to 8000 characters, and manually generating a string like the one above can easily break that limit. Besides the fact that it is much more error prone: If someone adds a new sheet it won't be included in the formula. So when possible, it is always better to use =SUM(FirstSheet:LastSheet!firstCell:lastCell) or similar.

[Vlemmings_Roel]

Hi Adrian,

Thanks for the detailed reply. That gave me lot of ideas. But it also gave us the idea that this cannot just be done in the template and it would involve some coding which might be okay for us.

Thank you very much for the help.

[Vlemmings_Roel]

Hi Adrian,

If we want to use this formula inside a band in a template how can we make sure the cell's row number changes?

=SUM('FirstSheet':'LastSheet'!A1');

Eg.Would A1 change as the band grows? like A2, A3 etc. We noticed it doesn't. We did a work around, then it started changing, but it was still not correct. We think it is probably has to with the X ranges and blank rows we have in our template. Do you think we can do this?

[adrian]

Hi,

A formula like this should indeed adapt to A2, A3, A4 etc unless there is something else going on. Can you send me the template you are using with this formula? The other template I have from you doesn't seem to have it.

[Vlemmings_Roel]

Hi Adrian,

Here are the links to both template and the output. Please see the C6 cell of the 'consolidated' sheet (in the template file Linq.MultiSheet.xlsx) to see the formula. In the output file see the corresponding cell. C6 is fine but in the next cell C14, the formula incorrectly references c17 instead of C14. That is the issue.

https://files.fm/u/z84r5w2m#_
https://files.fm/u/zfgg33fr#_

[adrian]

Thanks, I will be looking at them as soon as possible, I expect in a couple of days. Sorry about the delays, but we are a little behind schedule right now with all the features and fixes, and trying to catch up.