Linking 'last row' to a summary sheet

[skylined1]

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??

 

[Aladin Akyurek]

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]

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]

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]

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]

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]

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]

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
FAD	56	2	0.2	tr	waq	xad	5	nad

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

Summary

1208	FAD	56	2	0.2	tr	waq	xad	5	nad	3

<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]

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?

 

[skylined1]

Got it working. thanks for the help!!!