Copy structures between worksheets in Excel

User 465350dfe6

20-11-2013 17:53:17


I would like to know if it is possible to copy structures between worksheets in Excel using a formula.

For example on my first 10 worksheets I have a structure in cell C14.

On worksheet 11 I would like to have those 10 structures appear in the first column just by linking to the original worksheets. I tried using this formula





but nothing shows up. Is there a way to do this.

Thank you,


ChemAxon bd13b5bd77

20-11-2013 18:19:43

Hi Mathieu,

interesting problem, I think it does not work but there is workaround for that:



User 465350dfe6

20-11-2013 18:38:58


Thanks for your answer which works great. Unfortunately it does not completely solve my problem.

Indeed, if I put that formula in the first cell and then drag down to auto-increment, I get the following:





Whereas what I would like is the following:




Any ideas?



ChemAxon bd13b5bd77

20-11-2013 18:44:56

Hi Mathieu,

if you do NOT want to get the cells incremented you need to put $ around the cell identifier:


but I think it is over the JChem scope it is an Excel area.
AFAIK Excel does not support autoincrement for the sheets, so it is not JC4XL limitation.

$A$5 - obvioulsy if you increment the cells horizontally or vertically you are able to vary it with the $s.

$A - fixes the columns
$5 - fixes the rows





User 465350dfe6

21-11-2013 07:55:24

I finally found a solution, so if anybody is interested here is what worked for me:


Then dragging down will increase the worksheet but not the cell.

ChemAxon bd13b5bd77

21-11-2013 10:33:57

Nice, thank you for sharing you idea, solution.