Search multiple sheets within a report and then find the average of the result

Average.xlsxHi

I have changed the attachment to use CSV's

I am trying to create a report that I can enter the codes I am looking for and the formula will search the sheets within the report and once the results are found then find the average of those results.


What I am trying to do is search the codes from Sheet 1 under Outlet and ESA(in Blue) in sheet A,B and C. The problem is the ESA code seen in column I of A,B,C is only used when column L is empty in A,B,C.

Once the relevant codes are found I then need to look for the KPI's seen in sheet 1 B4, B6 and B8, they can be found in A,B,C in column R. then the result comes from column S in A,B or C.

Once the result is found for each code I want to find the average of them, with the answer to populate in yellow in sheet 1

I want the formula to be able to handle more or less codes as well as adjust the formula so I can add more sheets(possible D,E,F,G etc)

Hope this makes sense.

Thanks

Average.xlsx

Here's my attempt for just 1 sheet and 1 of the KPIs. If I'm on the right track, I'm sure you can extend my logic.
A couple notes: 1. Do you really need multiple sheets? You could put them on 1 sheet and you could have a column for which "sheet" that record is from. It would make things easier, especially if you'll be needing more sheets in the future.
2. Merged cells are a headache. If possible, avoid them.
3. You have a lot of blanks in your data and it doesn't have headers. This makes it difficult to understand what is being represented.

Please Login or Register  to view this content.

Why did you change it?

Working in just one tab makes all analyzes a lot easier.

I should have solved this (then) with an pivot table.

In my experience, it is much easier to make on "Data" tab and then make other tabs pull whatever info you need from there. Having multiple tabs pull from 1 = easy. Having 1 tab pull from multiple = hard.