HOW TO MAKE INCREMENTAL FORMULA

[KalengoPhiri]

I have a list on the first sheet with details, I have the second sheet with some blank spaces which are to be filled by the details on the first row of the first sheet by use of simple equals equations.
How can I make it so that I can generate another sheet which will automatically get information from the second row of the first sheet and so on? Please help

 

[6StringJazzer]

You want to have several sheets, and each sheet references data from a different row of the first sheet?

The details depend on how your sheets are indexed. In this solution, assume that the situation is exactly as your described: The first sheet has the master data, the second sheet refers to the first row of the master sheet, the third sheet refers to the second row, and so forth.

This formula is for column A. You can copy and paste it to the right.

=INDEX(Sheet1!A:A,SHEET(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99))-1)

 

[KalengoPhiri]

Thank you so much, I've managed to create the incremental solution I was after. I was a little confused with the indexing initially but after some reading I managed to work it out.

 

[KalengoPhiri]

I've managed to link the cells but when I change the master data it is not updating the the second sheet and the rest that follow, here's how my equation looks like after changing a few things.

=INDEX('[Sheet1]Truck List'!R3:R100,SHEET(MID(CELL("filename",F1),FIND("]",CELL("filename",F1))+1,99))-1)

perhaps it is because I do not understand what this means:

SHEET(MID(CELL("filename",F1),FIND("]",CELL("filename",F1))+1,99))-1)

Please help

 

[6StringJazzer]

The function CELL("filename",F1) returns the full filename and sheet name of the referenced cell. The result looks like this:
C:\Users\Jeff\Excel Forum\[sourceBook1.xlsx]Sheet3

The MID function around it isolates the sheet name, which is all the characters that appear after the "]".

The SHEET function takes a sheet name and returns the tab index for that sheet.

The "-1" adjusts for the fact that the second sheet should refer to the first row.

Your syntax is incorrect here:
=INDEX('[Sheet1]Truck List'!R3:R100,SHEET(MID(CELL("filename",F1),FIND("]",CELL("filename",F1))+1,99))-1)

Brackets are used to enclose an external Excel file, but Sheet1 is not an Excel file name. What is the name of the sheet that has your data?

 

[KalengoPhiri]

'Trucklist' is the name of the sheet with data

 

[6StringJazzer]

Then I think you want

=INDEX('Truck List'!R3:R100,SHEET(MID(CELL("filename",F1),FIND("]",CELL("filename",F1))+1,99))-1)

 

[KalengoPhiri]

Then I think you want

=INDEX('Truck List'!R3:R100,SHEET(MID(CELL("filename",F1),FIND("]",CELL("filename",F1))+1,99))-1)

Yes precisely, thank you very much