Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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", 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AutoFill with Reference incrementing BUT able to delete!
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I Incrementing Cell Reference Values by 7? | Excel Discussion (Misc queries) | |||
Rang reference incrementing by more than one on formula drag | Excel Worksheet Functions | |||
Incrementing cell reference | Excel Discussion (Misc queries) | |||
incrementing formula reference by 7 | Excel Discussion (Misc queries) | |||
Incrementing rows (or columns) to a reference in another worksheet | Excel Worksheet Functions |