Is there a better way than "indirect"

My file has 90+ tabs and the MasterList uses "indirect" several times per each row for 90+ rows, is there a better way to do this?

Row Example
B10

Please Login or Register  to view this content.

C10

Please Login or Register  to view this content.

D10

Please Login or Register  to view this content.

E10

Please Login or Register  to view this content.

F10

Please Login or Register  to view this content.

J10

Please Login or Register  to view this content.

Personally, I would have probably three sheets. The Master sheet would have the static information for ALL vehicles, much as now. Then ONE Transaction sheet for ALL vehicles. You'd need to add a key field column at the beginning of each record with the Vehicle ID number. Finally, the third sheet would be an Enquiry sheet. You could have a drop down list with a unique list of the vehicle IDs (from the Master list). You could populate the static information from the Master list using VLOOKUP or INDEX/MATCH. And, as you are using Office 365, you could collate the service and maintenance records from the Transaction list using FILTER and SORT, possibly UNIQUE.

There would be no need for dozens, if not hundreds of (similarly structured) sheets which are, inevitably, difficult to maintain and gather data from.

With all the transaction data in one sheet, you could sort and filter it, and you could use Pivot Tables and Charts to analyse the records.

You could also add extra columns to flag specific types of transaction (which would be easier to analyse than free format descriptions. A little more work but useful if you need to see which vehicles have had specific actions.

All food for thought.

One alternative is to use VBA to build each formula directly every time a new row is added, but I think the solution you have is better for the amount of data you have. VBA would make sense only if the calculation performance of what you have is too slow. Why are you looking for a different way?

This code would go into the module for Sheet1 (Master List) [tested in your file]

Please Login or Register  to view this content.

By the way the truck numbers in column A do not require an apostrophe.

Adding VBA introduces another, possibly, unnecessary learning curve. Users will need to enable macros for the code to work. Could be a whole world of pain for inexperienced users.

I agree, which is why I thought he already had the best solution. I would go to VBA only if the current solution is unusable for some reason.

OK thanks, just asking.