Deleting Blocks of cells causes #ref! error

  • Thread starter Thread starter Greg Little
  • Start date Start date
G

Greg Little

Hello all-
I have a large workbook , rather heavily automated with VBA. It is
basically a time card for multiple people for a week. Each person is a block of
cells 12 rows by 15 columns and these blocks repeat down the form for each
person, as many as 200 or so. I want to be able to delete one person at a time,
namely a block of cells but I run into Excels #ref! error. Since many of the
cells look up to their twin in the person above, due to repeating rates of pay,
dates, etc... and when those cells no longer exist, I get the error, all the way
down to the end from wherever I delete the block. Any way to get around it? As
is, I can't delete a person from the form .Thanks in advance.

Greg Little
(e-mail address removed)
 
You get that error when a cell which is being referred to in a formula is
deleted. To avoid this, you should use the master value in a cell somewhere
outside these blocks and hten refer to them in each block.

Mangesh
 
Thanks for the reply. I do use "master" cells in quite a few other places, in
fact I use an entire sheet for nothing else. Unfortunately, I am stuck using
relative references to preceding cells in quite a few places,in order to alow
changes "on the fly" as the sheet is filled in. I understand the error, was
hoping that someone might have a sneaky way around it. Beginning to believe I
need to re-think it as a database.
 
Just an idea you could use in your case by changing certain things, but the
basic formula is something like this:

Enter in A1: 1
In A2: =INDIRECT(ADDRESS(ROW()-1,COLUMN(),4))+1
Drag this down.

So you have 1, 2, 3, and so on, down the column. Now delete any row in
between, and you don't get the ref# error.


Mangesh
 
Mangesh, I can't thank you enough. With a very minor alteration to the formula,
it works like a champ- and since I copy a single template form for the
individual people, I only have to change about 20 or so cells. The darn thing
works beautifully. See, I knew there was someone out there sneakier than I.
Thanks again. I now have the magic bullet.

Greg Little
 
Back
Top