Linked sheets, cell references help

Hi There

Thanks so much for all your help last time. My question this time relates to adding a column to workbook 1 which is then linked to workbook 2 by formulas and in that workbook there are 2 sheets which are linked together by formulas as well. (Yes this is a terrible way of doing things, not my idea at all).

My problem is that when I add anything or change anything to the layout of workbook 1, I then have to change 300+ cell references to make the sheets all match up again as everything is off by 1 or 2 cells. My questions is, is there a simpler way to do this or are we stuck with "the hard option"?

This is for our weekly sheet so we have to copy this sheet down every week, sort of a rolling sheet if you will so the $$ signs will not work as the references will then be looking at the wrong cells.

Thanks so much for your assistance

Hi

If you have both books open when you make the changes then the formula should re-adjust.

Cheers

Hi there, but if I add a new column there wont be anything for it to add/update?

Hi

Can you upload the layour of the workbooks showing where the problem is?
It would make understanding the issue easier.
Cheers

The simplest way to explain it is that

In Workbook 1-------------------In workbook 2 -------------------------------------------------------------------------------------------
A-------------------------------Sheet 1--------------------------Sheet 2
1------------------------------ =workbook1a1----------------- =a1sheet1
2 ------------------------------ =workbook1a2------------------=a2sheet1

And so on and so on,

So my problem is if I change things (add a cell, remove a cell/row/column) in workbook 1 I have to update all of these links in workbook 2 because they will be out by 1 or 2 rows/cells/columns.
The raw data gets dumped into workbook 1 and then gets fed across to workbook 2.

Any help would be appreciated.

Hi

I think I see some of the issue but it raises some questions without seeing a sample of the workbooks.

1.Are the rolling data sets continguous
2. What is the need to delete/row. Is it mandatory for what reason
3. What is the need to add / delete columns Is it mandatory for what reason
4. When you say delete a cell is that deleting as in no data
5. Does the data set have headings
6. Why is a rolling weekly sheet necessary or could 1 workbook be created for each week/Month
7. What entries are unique ie first entry in Col A, Headings or both
8. What are the implications of leaving a row/col blank


Cheers

Hi there

So this is our sales sheet for each week, so we input data each week. We also add and remove locations as we create and remove them. So when I say delete a cell I mean remove it and shift cells to rearrange the workbook.

Hi
The obvious solution to having to change 300 plus cells is to automate the task using VBA code. The development of suitable code is hindered by not being aware of the layout of the file, the impact to users of changes to the file layout and whether any final presentation can offer a compromise in the final solution. I was hoping I might be able to solve your issue by modifying a previously developed model (saving me a lot of work) but with a limited understanding of your workflow in this instance I am unable to help. There may be other more knowledgeable people on the site that can assist as while the issue is not marked soleved others will review.
Good luck

Hi Foxsealady

how to I attach sheets in this forum?

Sorry I am a bit of a noob.

Hi

Click 'Go Advanced' - right hand corner of reply window.

Scroll down to 'Manage Attachments, Browse to highlight file, Open and Upload. Close Window and submit reply.

Cheers