getting value from other sheet with name defined by cell-content?

Hello all,

I am looking for a way to get data from the last row in a certain column on another sheet, where the sheet is defined based on a cell-content.
I was looking at LOOKUP and INDIRECT which principally do what I need, however when I need to "point" to a different sheet then things fall apart.

Situation:
- Workbook with three sheets:
a) Sheet1: "Overview"
b) Sheet2: "1_Moadll"
In Column B I have values, last one e.g. in row 5, e.g. content = "XYZ"
c) Sheet3: "5_John"
In Column B I have values, last one e.g. in row 8, e.g. content = "ABC"

- Sheet "Overview" has a the number and the name listed, e.g.
A1: 1, B1: Moadll
A2: 5, B2: John

- In Sheet "Overview" I need to get the values
C1: get content "XYZ" from Sheet "1_Moadll" and cell B5
C2: get content "ABC" from Sheet "5_John" and cell B8

What I tried up to now:
a) Overview, Cells C1: =INDIRECT(A1 & "_" & B1 & "!B5")
=> gets "XYZ", however this is static

b) Sheet "1_Moadll", cell A1: =LOOKUP(2,1/(B:B<>""),ROW(B:B))
=> gets the row-number of the last entry, here e.g. 5

c) Combine a) and b)
- LOOKUP on each sheet in a certain cell
- INDIRECT to that cell on "OVERVIEW"
- LOOKUP using the content of that result
C1: =INDIRECT(A1 & "_" & B1 & "!A1") ' A1 contains the value of the last row
D1: = INDIRECT(A1 & "_" & B1 &"!B"&C1) ' pulls the right value

d) incorporate LOOKUP into INDIRECT:
"Overview", Cell E1: =INDIRECT(A1 & "_" & B1 &"!B"&LOOKUP(2,1/('1_Moadll'!B:B<>""),ROW(' & A1 & "_" & B1 & '!B:B)))
=> this also gets the right value, however requires the name of the sheet "1_Moadll" as a string in the lookup-call. Building that up inside the function does not point to the sheet anymore:
=INDIRECT(A1 & "_" & B1 &"!B"&LOOKUP(2,1/("'" & A1 & "_" & B1 &"'!" &B:B<>""),ROW(' & A1 & "_" & B1 & '!B:B)))

Any ideas on how to get that with appropriate functions.

I am capable of VBA and using that getting the values would be a piece of cake. However here I am constrained to work without VBA.

Thanks a lot already in advance!

Moadll

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hello,

just uploaded at test-workbook with the various combinations that I tried.
Column J is the most dynamic version, however this one unfortunately does not yield a "proper" result.

Cheers

Moadll