I'm managing about 200 customers on two spreadsheets. On the source sheet,
there are 4 lines for each customer. One the current sheet, there is one
line per customer. I am referencing three cells per customer from the source
sheet to the current. I want to delete a customer from the middle of each
report, but I want the cell references to change accordingly. Here's an
example of the formula I'm using on the current sheet (to pull from the
source sheet):
='[REPORT.xls]NOV 05'!$L$378
Where L378 is a balance that changes every month. And four lines down from
that is the next cell being referenced in the next line. When the four lines
that encompass the cell L378 are deleted, the others move up. I also delete
that customer from the current sheet. But the cells do not change
accordingly. Is there a way to make that happen automatically, without
having to manually go into each cell and change them?
I want to know how to do the reverse of that, too, by entering new customers
into the middle of the sheets and having the cells change accordingly.
Any clues?