Finding the last non blank row from another sheet and doing a vlookup on that row

[CBoukal]

I have searched and can't find a decent way to get this done.

I have a Workbook with 18 "sheets and a recap sheet. Each sheet will have a different number of rows between A9 and N60. On my recap sheet I want to be able to find what the last row with data in Column A and then be able to do a vlookup on that row to display the data in Column 2,3,4, exc. I know how to do the vlookup's fine but I don't know how to specify to select the last row. The data will always be different and the number of rows will never be the same either.

I tried the below formula because everyone complains that if you use True is returns the last row but that didn't work.
=VLOOKUP(A2,Sheet_2!$A$9:Sheet_2!$N$60,2,TRUE)

Is there a way to do this without VB, I don't understand VB at all and that would get way over my head really quickly.

Thanks for any help in advance

[BadlySpelledBuoy]

And chance of a sample workbook with some manually input desired outcomes?
FAR easier to provide a solution if we have that at least.

[CBoukal]

http://www.ChadBoukal.com/files/help.xlsx

The "recap" sheet is where I am trying to get the data. I want to fill "Final Area" "Final City" "Final State" from "Truck_1" and "Truck_2" sheets. Each truck has a different number of entries from A9 down. I want the data on "recap" do be the final row with data from each sheet. There was a ton more sheets but I deleted them for the sake of keeping it clean.

Thanks again.

[bebo021999]

Make sure names in column A sheet recap match exactly with name of other sheets.
In C2:
=LOOKUP(2,1/(--(INDIRECT(A2&"!A1:A1000")<>"")),INDIRECT(A2&"!B1:B1000"))
D2:
=LOOKUP(2,1/(--(INDIRECT(A2&"!A1:A1000")<>"")),INDIRECT(A2&"!C1:C1000"))
E2:
=LOOKUP(2,1/(--(INDIRECT(A2&"!A1:A1000")<>"")),INDIRECT(A2&"!D1:D1000"))
Drag down.

[CBoukal]

Thank you so much for your help!!!! I would have never figured that out and I don't understand how that works but now I know how it's to do done so I can research the Lookup and Indirect functions. Can I ask, you put A2& to use what was typed in that cell which I didn't know you could do. Is that able to be used other places? For example I have a sheet where I type =Truck_1!A9 where "Truck_1" is the sheet name. I have to copy this sheet about 17 more times and I was wondering since "truck_1" is typed in A1 of those sheets already is there a way to use that A2& truck you used? I tried doing =A1&!A9 but it said the formula was invalid.

Thanks so much again!

[bebo021999]

Assuming A2 contains sheetname Truck_1, A3:Truck_2, A4:Truck_3 and so on.
If in B2 we type: =A2&"!A1" => displays "Truck_1!A1", it is a text but reference.
INDIRECT(Text) is to tell Excel that Text is reference.
INDIRECT(A2&"!A1")="xyz" if cell A1 of sheet Truck_1 contains "xyz"
Copy INDIRECT(A2&"!A1") down we have INDIRECT(A3&"!A1")=Truck_2!A1 reference automatically.
So, INDIRECT helps to get sheetname in A column to avoid re-typing in each row.

Back to your wookbook, you have 18 sub-sheets, then you have list of sheetname from A2 to A19 which is Truck_1, Truck_2,..., Truck_18, then drag the formula in B2 to B19.

Hope it is clear for you.

[CBoukal]

You are awesome thanks so much for your help!!!!!