Referencing External Sheets with formula

[philehutchinson]

Hi,
I am doing some evaluation at present to see if I can an automate an excel workbook.

In one workbook I have values that are being populated from another sheet using

=INDIRECT("kpitrend!D"&COLUMN())

(Where kpitrend contains values...)

I have been able to use copy column to achieve what I wanted.

However my issue is on another sheet the following formula is referenced...

=(KPIs!AV4-KPIs!AW4)/KPIs!AW4

There are columns like this with these type of formulas, if in excel if copy and then paste this into the next column excel updates the formulas for these correctly and increases all the formula. I noticed that in another sheet where I do a copy and have a simple formula like (AV4/AW4) the formula is being updated.

If I copy column with this reference formula above, this doesn't happen, so I have tried to code around it..
However when I look at the formula being returned in the component for

For =KPs!AW3

I get returned as a formula [ExternSheet]AW3 so I wrote a routine to advance this 1 column but if I try and update the next column with
[ExternSheet]AX3 - I get a crash?

Is there an easier way to do this? Or if I hacked the [ExternSheet]AW3 back to KPs!AX4 and stored this would it work?

I hope this makes some sense
Phil

[philehutchinson]

I found my solution..

I had two issues...

I was using a dynamic array of TXLSCellType to save me time when checking if the items in the rows were formulas or not, so not doing the same thing over and over..

However it was causing a memory issue, and corrupting a small string function I had where the value I was passing out was coming back as garbage.

So I removed this and just used 1 variable.. wasteful in time - but this routine does not need to fast - just need to do the job

This meant that I could then basically fix the issue.

I wrote a function to split up the returned function like [ExternSheet]AX3 and then using

refstrtocolrow(cellref,col,row) - I turned AX3 back to coll, ref

Changed the column by the increase (so usually 1 as I was going across in columns)
col:=col+increase;

cellref:=colrowtorefstr(col,row);

Rebuilt the cellref and then rebuilt the string back to [ExternSheet]AY3 for example.

I then string replaced [ExternSheet] to the actually referenced workbook to give me KPIs!AY3 and then updated the formula.

And no crash...

XLSReadwrite doesn't seem to understand once it gets the forumula with [ExternSheet] what this means if you pass it back. Far from ideal I guess as if you don't know what the sheet you are working with is referencing you are stuck.

Hope this helps someone else.
Regards
Phil