Cell Referencing/Calculations

[Guest]

Dear All,

I wish to perform a calculation between two excel sheets that contain masses
of data. One sheet has links to the other to perform the calculations, at the
beginning of each month more data is entered, hear arrises my problem.

Entering the data is fine but when i want to update the data in the second
sheet I have to go into each formula and update the cell references.

this is because one sheet runs from left to right whilst the other runs from
top to bottom therefore I cannot drag and copy the formules on sheet two.

I have been advised to use the OFFSET function which works to an extent but
the data in sheet 2 can not be viewed without having sheet 1 open. As sheet
2 is sent to clients by itself this isn`t an option!

Please email if i haven`t made sense!

Many thanks in advance

Paul

 

[Guest]

Write a vba routine that builds your formulas. run it when you need to build
or extend the formulas.

 

[Guest]

Hi Tom,

I haven`t yet delved into the realms of visual basic, would you be able to
point me in the right direction?

Many thanks

Paul