Hello. I have a spreadsheet designed where I am keeping a log of when our vehicles have been serviced. In the summary tab, I have two columns where the mileages are pulled from another sheet that is referenced through an indirect formula. One column pulls the mileage of the last time the vehicle was serviced, and that is working correctly. The second column is attempting to pull the mileage from the next to last time that vehicle was serviced. The second column works okay unless there is no data to pull, meaning all the cells in that column range are blank, or that vehicle was only serviced one time so far, in which case I get a #SPILL! error. Below is the formula I have right now;
=INDEX(INDIRECT("'"&A7&"'!f5:f800"),COUNT(INDIRECT("'"&A7&"'!f5:f800"))-1)
What should the formula look like if there is only one value and therefore no next to last value to pull from? And, is there a way to redirect it to pull the last value of the previous year (a different workbook) if there is only one value in the currently targeted location?
Thank you.
Hi John,
Welcome to our forum! Your question sounds similar to this. Although, it doesn't deal with different workbooks. I would avoid referencing external workbooks at all costs. Quite simply, many functions, including INDIRECT will not evaluate on closed workbooks.
If you get stuck, please come back with a sample Excel file so we can help you more precisely.
Mynda
