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 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




"Greg Little" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 Removed)

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.

On Fri, 19 Aug 2005 11:28:30 +0530, "Mangesh Yadav"
<(E-Mail Removed)> wrote:

>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
>
>
>
>
>"Greg Little" <(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>> 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 Removed)
>

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





"Greg Little" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> On Fri, 19 Aug 2005 11:28:30 +0530, "Mangesh Yadav"
> <(E-Mail Removed)> wrote:
>
> >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
> >
> >
> >
> >
> >"Greg Little" <(E-Mail Removed)> wrote in message
> >news:(E-Mail Removed)...
> >> 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 Removed)
> >
>

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


On Sat, 20 Aug 2005 09:36:55 +0530, "Mangesh Yadav"
<(E-Mail Removed)> wrote:

>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
>
>
>
>
>
>"Greg Little" <(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>> 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.
>>
>> On Fri, 19 Aug 2005 11:28:30 +0530, "Mangesh Yadav"
>> <(E-Mail Removed)> wrote:
>>
>> >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
>> >
>> >
>> >
>> >
>> >"Greg Little" <(E-Mail Removed)> wrote in message
>> >news:(E-Mail Removed)...
>> >> 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 Removed)
>> >
>>
>

Always nice to know it helped. thanks for the feedback.

Mangesh



"Greg Little" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
> On Sat, 20 Aug 2005 09:36:55 +0530, "Mangesh Yadav"
> <(E-Mail Removed)> wrote:
>
> >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
> >
> >
> >
> >
> >
> >"Greg Little" <(E-Mail Removed)> wrote in message
> >news:(E-Mail Removed)...
> >> 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.
> >>
> >> On Fri, 19 Aug 2005 11:28:30 +0530, "Mangesh Yadav"
> >> <(E-Mail Removed)> wrote:
> >>
> >> >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
> >> >
> >> >
> >> >
> >> >
> >> >"Greg Little" <(E-Mail Removed)> wrote in message
> >> >news:(E-Mail Removed)...
> >> >> 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 Removed)
> >> >
> >>
> >
>