#SPILL! error help

[JohnEJo]

Hello. I have a spreadsheet that we are using to track vehicle maintenance services. In one column, it lists the mileage from the last service done on that vehicle, which is in another spreadsheet of the same workbook. In another column, I have it listing the service mileage of the previous service for that same vehicle. It works good so far, unless there was no previous service for that year on the vehicle to date, at which time it give the #SPILL! error. My understanding is that an If formula will not help with this type of error, so I need your help. If there was no previous mileage, ideally I would like it to pull the last serviced mileage from the previous year, a different workbook, if any, but if not at least leave the cell blank. Here is the formula I am using. BTW, I am utilizing the indirect function to point it the tab with the same name as what is listed in cell A7. Also, I am using Excel 365 if that helps.

=IFERROR(INDEX(INDIRECT("'"&A7&"'!f5:f800"),COUNT(INDIRECT("'"&A7&"'!f5:f800"))-1),"")

Thanks again for any help with this. I greatly appreciate it.

 

[Alex Blakenburg]

I can't see your data to determine what the -1 is doing but the spill is caused by the Row Number in the Index statement returning a zero.
This equates to "All Rows" which returns a range and hence spills.
PS: Not found is producing a negative Row Number so this is handling that as well and you shouldn't need the IfError

Try this:-

=IF(COUNT(INDIRECT("'"&A7&"'!f5:f800"))-1>0,
         INDEX(INDIRECT("'"&A7&"'!f5:f800"),COUNT(INDIRECT("'"&A7&"'!f5:f800"))-1),
         "")

Since you have 365 you can use this, if its clearer

=LET(RowRef,COUNT(INDIRECT("'"&A7&"'!f5:f800"))-1,
    IF(RowRef>0,
         INDEX(INDIRECT("'"&A7&"'!f5:f800"),RowRef),
    ""))

 

[JohnEJo]

I can't see your data to determine what the -1 is doing but the spill is caused by the Row Number in the Index statement returning a zero.
This equates to "All Rows" which returns a range and hence spills.
PS: Not found is producing a negative Row Number so this is handling that as well and you shouldn't need the IfError

Try this:-

=IF(COUNT(INDIRECT("'"&A7&"'!f5:f800"))-1>0,
         INDEX(INDIRECT("'"&A7&"'!f5:f800"),COUNT(INDIRECT("'"&A7&"'!f5:f800"))-1),
         "")

Since you have 365 you can use this, if its clearer

=LET(RowRef,COUNT(INDIRECT("'"&A7&"'!f5:f800"))-1,
    IF(RowRef>0,
         INDEX(INDIRECT("'"&A7&"'!f5:f800"),RowRef),
    ""))

Thank you very much. It works great!

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.