Combining formulas INDIRECT & LOOKUP & VLOOKUP

Dear all,

I'm trying to create an overview where the input is split out into smaller sheets. I can create the overview right now but it require a lot of manual work which is time consuming but also introduce a great risk for mistake. The setup is attached but like this:

I have 15 applications, each applications have 20 different measurement points. The measurement points are tracked per date per applicaiton in each sheet. In the overview sheet, I'll create a matrix with 1 column per application and each measurement point are added as rows.

How much of this is possible to add to 1 single formula which I can copy out to all cells?

• Last column
• Look for the right sheet
• Look for the right measurement

I have of course attached an example sheet and highlighted in yellow usage of INDIRECT & LOOKUP, but hope to merge all into 1 formula including VLOOKUP. The other cells B2:F11 are manual copied data from the other sheets. My Excel is in Swedish so cells H2, H3 are the formulas I have used but in English.

Is this possible? Thanks so much for your time and help.
Allan Andersen

Your worksheet Overview is so regular, please refer to the formula

B2 cell formula , Drag down and accross

HTML Code: 

Hi wk9128

Sorry if my example is too regular but it actually shows exactly what I try to achieve - a matrix looking at row1 and column1 and getting the last cell in App1:App5 based on row1/column1 in the Overview sheet. Trying to learn about Excel every day.

Added your code gives almost the result but it doesn't give the last cell from App3 (new example added) as it points at row3 in that specific sheet. Do you have an idea of how to add 'get last cell' into your formula?

Thanks again.
Allan

Hi again wk9128!

Exactly like I was looking for. You made a big difference and seems I need to study the OFFSET formula.

Thanks again, wishing you all the best and a great weekend.
//Allan Andersen