Copy structures between worksheets in Excel

Hi,

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

='1'!C14

='2'!C14

='3'!C14

etc.

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

Thank you,

Mathieu

Hi Mathieu,

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

=JCStructure(Sheet2!A2)

Viktor

Viktor,

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:

=JCStructure('Sheet2'!C8)

=JCStructure(Sheet'2'!C9)

=JCStructure('Sheet2'!C10)

etc.

Whereas what I would like is the following:

=JCStructure('Sheet2'!C8)

=JCStructure('Sheet3'!C8)

=JCStructure('Sheet4'!C8)

Any ideas?

Thanks,

Mathieu

Hi Mathieu,

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

=JCStructure(Sheet2!$A$5)

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

 

Viktor

 

 

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

=JCStructure(INDIRECT(""&ROW(B2)&"!$C$8"))

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

Nice, thank you for sharing you idea, solution.

Viktor