Cell calculations/referencing between workbooks

[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

 

[Bernie Deitrick]

Paul,

There are a couple of approaches you can take. The easiest would be this: copy the columns with the
data, that your formulas are linking to, and then, without changing the selection, use "insert
copied cells" from the right-click menu on the column headers. This will move the
formula-referenced cells to the right, and insert a copy of your old data. Then overwrite the data
in the referenced cells that have been moved to the right, and your formulas will reference the
correct cells, since Excel will update all of the formula references.

HTH,
Bernie
MS Excel MVP