Extracting last row data in col A from another worksheet based on list

[KayWill]

Hello,

I'm hoping someone can assist. I have run into another issue. I have a workbook that has several sheets. The sheets are created based on a named range. What I am trying to do is with the main worksheet ("Project Tracker"), I am trying to enter a formula in the Due by date. If the name matches one of the excel tabs (worksheets), it will look in the last row of the matching tab and place the date.

Example:

Main Sheet Project Tracker
Col A Col B Col C Col D Col E
John 214212 Dallas TX

(Formula, = if Col A matches worksheet, then in Col "A", last row, insert here)

Jane 213628 Houston TX
Martha 244382 Memphis TX

Now the formula I've used work's great but as you will see , it is row specific:

=INDIRECT("'"&[@PROJECT]&"'!" & "A20")

I have tried using various "last row" codes but keep getting an invalid cell reference error. For example, I tried using,

=INDIRECT("'"&[@PROJECT]&"'!" &INDEX(A:A,MATCH(REPT("z",255),A:A)))

I'm stumped so any assistance or guidance will be greatly appreciated.

 

[konew1]

Hello,

I have tried using various "last row" codes but keep getting an invalid cell reference error. For example, I tried using,

=INDIRECT("'"&[@PROJECT]&"'!" &INDEX(A:A,MATCH(REPT("z",255),A:A)))

.

I suggest you recheck this using formula evaluation. It looks like it will be returning the row number from the summary sheet, not the source sheet. I think you need another Indirect inside the index.
Also, the existing indirect call omits the column letter

 

[KayWill]

I suggest you recheck this using formula evaluation. It looks like it will be returning the row number from the summary sheet, not the source sheet. I think you need another Indirect inside the index.
Also, the existing indirect call omits the column letter

I've tried using the formula evaluation and can not figure out what I'm missing. I will try secondary Index to see if that does the trick. But, in the first example, it is doing exactly what I need it to do but I was trying to edit that formula in the 2nd example to search for the last row with data (whether text or numeric) and it's not working for me.

I'll continue to post updates, thanks for your reply.

 

[konew1]

You are getting the contents, not the row number and column letter. Your first example has hard-coded "A20". I understand you want column A but to get the last data row.

=INDIRECT("'"&[@PROJECT]&"'!"&"A"&MATCH(REPT("z",255),A:A))
or simplified if it is always column A
=INDIRECT("'"&[@PROJECT]&"'!A"&MATCH(REPT("z",255),A:A))

 

[KayWill]

SOLVED: Extracting last row data in col A from another worksheet based on list

THANK YOU, THANK YOU, THANK YOU konew1! Your simplified worked perfectly!!!

Sorry for my delay but caught in other planning meetings. THANK YOU.