AutoFill with Reference incrementing BUT able to delete!

[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". 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

 

[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

 

[Gord Dibben]

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