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

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

--
Regards,
Tom Ogilvy



"Stratful" wrote:

> 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

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

"Tom Ogilvy" wrote:

> Write a vba routine that builds your formulas. run it when you need to build
> or extend the formulas.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Stratful" wrote:
>
> > 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