Linking 'last row' to a summary sheet

skylined1

Board Regular
Joined
Mar 13, 2012
Messages
64
Workbook will be shared, and apparently shared workbooks cannot have vba? ugh..

So my problem is this:

I have a workbook with a summary sheet. This summary sheet has a row for each aircraft. and each aircraft has its own sheet. When any status changes to an individual aircraft, we go to the appropriate sheet, input all required info in the next available blank row.

On the summary sheet I would like the last row with data from the aircraft sheets to show up in the specified row.

To summarize: Instead of having to go to each individual aircraft sheet and check the last status (row) I would like to have 1 row per aircraft automatically show the last data from each row on the summary sheet, also I cant use VBA since this will need to be opened and edited by multiple people at a time.

So what are my options??
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Workbook will be shared, and apparently shared workbooks cannot have vba? ugh..

So my problem is this:

I have a workbook with a summary sheet. This summary sheet has a row for each aircraft. and each aircraft has its own sheet. When any status changes to an individual aircraft, we go to the appropriate sheet, input all required info in the next available blank row.

On the summary sheet I would like the last row with data from the aircraft sheets to show up in the specified row.

To summarize: Instead of having to go to each individual aircraft sheet and check the last status (row) I would like to have 1 row per aircraft automatically show the last data from each row on the summary sheet, also I cant use VBA since this will need to be opened and edited by multiple people at a time.

So what are my options??

Care to post a status row as example?
 

skylined1

Board Regular
Joined
Mar 13, 2012
Messages
64
Sure, the summary page can be seen HERE
one of the aircraft status pages can be seen HERE, this is the 'A1208' sheet.

Any time a new row of data is input, I would like that rows data to show in the corresponding row and cells on the summary sheet. Does this help?

 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Sure, the summary page can be seen HERE
one of the aircraft status pages can be seen HERE, this is the 'A1208' sheet.

Any time a new row of data is input, I would like that rows data to show in the corresponding row and cells on the summary sheet. Does this help?


Try...

J2, copy down:

=MATCH(REPT("z",255),INDIRECT("'"&"A"&$A2&"'!A:A"))

B2, copy across and down:

=INDEX(INDIRECT("'"&"A"&$A2&"'!A:I"),$J2,COLUMNS($B2:B2))

Note that using INDIRECT this much can be costly.
 

skylined1

Board Regular
Joined
Mar 13, 2012
Messages
64

ADVERTISEMENT

Thank you for the quick reply, but I'm afraid Im not quite getting it. Could you dumb it down a bit? The match formula should go on which sheet? What about the index formula?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Thank you for the quick reply, but I'm afraid Im not quite getting it. Could you dumb it down a bit? The match formula should go on which sheet? What about the index formula?

Summary. The formulas must go to the Summary sheet, right?
 

skylined1

Board Regular
Joined
Mar 13, 2012
Messages
64

ADVERTISEMENT

What I need to happen is have the last row from the sheet 'A1208' and on that sheet the last data in cell A should always go to the B2 cell on the summary sheet, same with cell b should always go to cell c2, cell c to cell d2. always going from sheet 'A1208' to sheet 'Summary' Does that makes sense? Hey and thanks for the help!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
What I need to happen is have the last row from the sheet 'A1208' and on that sheet the last data in cell A should always go to the B2 cell on the summary sheet, same with cell b should always go to cell c2, cell c to cell d2. always going from sheet 'A1208' to sheet 'Summary' Does that makes sense? Hey and thanks for the help!

You need try what I suggested...

A1208
GAD
FAD5620.2trwaqxad5nad

<colgroup><col style="width: 48pt;" span="9" width="64"> <tbody>
</tbody>

Summary
1208FAD5620.2trwaqxad5nad3

<colgroup><col style="width: 48pt;" span="11" width="64"> <tbody>
</tbody>

A2 houses part of sheet name of interest.

K2:

=MATCH(REPT("z",255),INDIRECT("'"&"A"&$A2&"'!A:A"))

B2, copy across to J2:

=INDEX(INDIRECT("'"&"A"&$A2&"'!A:I"),$K2,COLUMNS($B2:B2))
 

skylined1

Board Regular
Joined
Mar 13, 2012
Messages
64
Ok, I was able to get it to partially work, I can get the data from cell A on A1208 sheet to display data on the summary sheet. however it only shows the data that is in cell A on the A1208 sheet. I cant get it to display the other cell data, even when i copy across. so.. in the formula =INDEX(INDIRECT("'"&"A"&$A2&"'!A:H"),$K2,COLUMNS($B2:B2)). what part can i change to get it to link correctly?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,382
Messages
5,595,853
Members
414,027
Latest member
zippyfrog

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