#11 Changes to Excel Template Absolute References

[Rob Kinman]

Hi Larry

Could you please assist with the following situation?

My csv file contains Report Parameters in rows 3 to 6, and Data from row 12 onwards.

My template file has 2 tabs, the first for the Data and the second for the Parameters.

However, I would like some of the Parameters to be headings in the data tab, so in the template I put in an absolute link to a cell on the Parameters tab (e.g. =Parameters!$A$6)

I then run in the Data using the following command

python csv2odf -s 12 -t 1 -S 3 --comment="Review Analysis" report_30.csv ReportTemplate_30.xlsx report_30_inter.xlsx

When I check report_30_inter.xlsx, the absolute reference remains unchanged (i.e. =Parameters!$A$6).

I then run in the Parameters using the following command

python csv2odf -s 3 -e 9 -t 2 -S 1 --comment="Review Analysis" report_30.csv report_30_inter.xlsx report_30.xlsx

When I check report_30.xlsx, the absolute reference has now changed (i.e. =Parameters!$A$12), which appears to be in relation to the number of parameter rows imported, and therefore does not show the correct field in the Data tab header. I've tried a number of combinations, and this appears to be fairly consistent.

Is there a reason for this? If the logic is neccessary in certain instances, could it be ignored if absolute referencing is used?

Regards

Rob

[Larry Jordan]

Rob,

The program treats the absolute address the same as a regular address.

What if you used the INDEX function? For example, if you pointed the function to the point where the parameters are inserted, like INDEX(Parameters!A2, 6), csv2odf will expand A2 into an array, like A2:A10, and the function will return the 6th item from the array.

Let me know if the index function works. If not, I will think about programming an exception for absolute address. I havn't had a use case for it before.

Thanks,
Larry

[Rob Kinman]

Hi Larry

The INDEX function works for my purposes (Although your code increments the end cell of the array, the start cell remains fixed, so the relative positioning of each parameter in the array remains correct).

Many thanks.

If you were looking to enhance the product in future releases, I would make the following suggestions...
1. Do not increment cell references when absolute references are specified.
2. When updating cell references, only update those on the current tab/worksheet.

Once again, thanks both for an extremely useful product, and also the prompt support.

Regards

Rob