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

KayWill

New Member
Joined
Jan 22, 2015
Messages
36
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:
Code:
=INDIRECT("'"&[@PROJECT]&"'!" & "A20")

I have tried using various "last row" codes but keep getting an invalid cell reference error. For example, I tried using,
Code:
=INDIRECT("'"&[@PROJECT]&"'!" &INDEX(A:A,MATCH(REPT("z",255),A:A)))

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

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

konew1

Well-known Member
Joined
Oct 17, 2007
Messages
2,287
Hello,

I have tried using various "last row" codes but keep getting an invalid cell reference error. For example, I tried using,
Code:
=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

New Member
Joined
Jan 22, 2015
Messages
36
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

Well-known Member
Joined
Oct 17, 2007
Messages
2,287
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

New Member
Joined
Jan 22, 2015
Messages
36
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,477
Messages
5,596,384
Members
414,063
Latest member
N_Bates

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top