Adding Rows Returns Ref

[pauldaddyadams]

Hi,

I found this spreadsheet on another post which Steve R created I believe. Original Post http://www.excelforum.com/excel-misc...checklist.html

I need the spreadsheet tweaking slightly but despite my efforts I can not get it to work.

When I enter additional rows on the tab ‘maintenance schedule’ it returns a reference on the ‘upcoming’ tab. I have attached an example called Maintenance Schedule Checklist 3 Rows Added.

Any Suggestions?

Paul

[martindwilson]

for every row you add at top of maint schedule
"),ROW()-3)-1),"") you must - away another 1 in this bit.

so add one row becomes
"),ROW()-3)-2),"")
add two rows
"),ROW()-3)-3),"") and so on
also there were references in both formulas which were not absolute which i corrected

[pauldaddyadams]

Hi,

Thanks for the help. I have added three rows to the upcoming tab also and I have the same problem but I can not amend the formula to get it to work despite your explanation.

Also – I have a couple of error codes:

On the upcoming tab I have a vlookup formula which returns an error code #N/A, Is there any way I can get it to return a blank result rather than an error?

On the Maintenance Schedule tab if I do not have an entry in Column B Cells H:Q return the error #DIV/O – Can this be corrected?

I appreciate any advice/help anyone can give.

I have attached the modified spreadsheet

[martindwilson]

you have to adjust rhe row reference again (the - figure makes the row value start at one berfore its dragged down)
in both formulas row()-3 appears twice change them to reflect your data now begins in row 7 so they become row()-6
>=ROW()-6,INDEX('Maintenance schedule'! and ,""),ROW()-6)-4),"")
as for div 0 tad more awkward youd have to use if is error making the formulas in sheet maint sched look like this

=IF(ISERROR(SMALL(IF(G6+7*B6*(ROW(INDIRECT("$1:$"&ROUND(((TODAY()-G6)/(7*B6))*2,0)+10)))>=TODAY(),G6+7*B6*(ROW(INDIRECT("$1:$"&ROUND(((TODAY()-G6)/(7*B6))*2,0)+10))),""),COLUMN()-7)),"",SMALL(IF(G6+7*B6*(ROW(INDIRECT("$1:$"&ROUND(((TODAY()-G6)/(7*B6))*2,0)+10)))>=TODAY(),G6+7*B6*(ROW(INDIRECT("$1:$"&ROUND(((TODAY()-G6)/(7*B6))*2,0)+10))),""),COLUMN()-7))

[pauldaddyadams]

That worked thaks, it did not at first - its because I wasnt hitting shift, Ctrl and Enter.

Thank you.