AutoFill with Reference incrementing BUT able to delete!

Good morning folks! I hope I have a simple one for you but I am stumped!

On sheet #1 (called: Kub Kar List) I have my sponsors listed down Col "F"
and their slogan in Col "H". On another sheet (SLOGAN) I would like to be
able to list the sponsor slogan (='Kub Kar List'!$H3) also incrementing
downwards.

My problem is if I delete a sponsor from sheet#1 (highlight row and delete)
I get a #REF error because that line was deleted and my calc on sheet#2
changes! I would like my reference NOT to change no matter whats done on the
first sheet!

What am I missing? I have searched this database for something similar but
am having no luck.

Thanks in advance for all your help!
Regards;
Duane

Good morning folks! I hope I have a simple one for you but I am stumped!

On sheet #1 (called: Kub Kar List) I have my sponsors listed down Col "F"
and their slogan in Col "H", and if they paid (Y or N) in Col "A". On
another sheet (SLOGAN) I would like to be
able to list the sponsor slogan (='Kub Kar List'!$H2) also incrementing
downwards.

My problem is if I delete a sponsor from sheet#1 (highlight row and delete)
I get a #REF error because that line was deleted and my calc on sheet#2
changes! I would like my reference NOT to change no matter whats done on
the
first sheet! So, I did: =INDEX('Kub Kar List'!H:H,H2) and FILLED and it
works!

BUT:
in COL "A" on SLOGAN sheet I have:
=If('Kub Kar List'!$B2="Y","Yes","No") to show who has paid
in COL "B" on SLOGAN sheet I have:
=If('Kub Kar List'!$B2="Y",'Kub Kar List'!$F2,"NIL") and if not paid NIL

What am I missing? I have searched this database for something similar but
am having no luck.

Thanks in advance for all your help!
Regards;
Duane

In A1 of SLOGAN sheet.

=IF(INDIRECT("'Kub Kar List'"&"!$B" & ROW(2:2))="Y","Yes","No")

Play with INDIRECT and ROW() for your other formulas.

INDIRECT will survive the deletion of rows in Kub Kar List sheet.


Gord Dibben MS Excel MVP

On Thu, 11 Feb 2010 08:00:01 -0800, Duane
wrote:

Good morning folks! I hope I have a simple one for you but I am stumped!

On sheet #1 (called: Kub Kar List) I have my sponsors listed down Col "F"
and their slogan in Col "H", and if they paid (Y or N) in Col "A". On
another sheet (SLOGAN) I would like to be
able to list the sponsor slogan (='Kub Kar List'!$H2) also incrementing
downwards.

My problem is if I delete a sponsor from sheet#1 (highlight row and delete)
I get a #REF error because that line was deleted and my calc on sheet#2
changes! I would like my reference NOT to change no matter whats done on
the
first sheet! So, I did: =INDEX('Kub Kar List'!H:H,H2) and FILLED and it
works!

BUT:
in COL "A" on SLOGAN sheet I have:
=If('Kub Kar List'!$B2="Y","Yes","No") to show who has paid
in COL "B" on SLOGAN sheet I have:
=If('Kub Kar List'!$B2="Y",'Kub Kar List'!$F2,"NIL") and if not paid NIL

What am I missing? I have searched this database for something similar but
am having no luck.

Thanks in advance for all your help!
Regards;
Duane