Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

I suppose your 52 "ordinary" sheets are one per week. This is a design invented again and again. It also causes problems again and again as many requests for help in different forums show.
Since Calc does not offer a function providing access to a sheet based on its position (number), there is a sulution only if you can assure a simple systematic naming of the sheets. The solution will also be ugly as you will need a combination of ADDRESS and INDIRECT to access a single cell, and an additional call to OFFSET for accessing a range.

Suppose your sheets are named exactly in the way "Week_"&nn where nn is a two-digit-number in the range 01 through 52, and you want to reference cell N14 of sheet 'Week_01' in cell A11 of your tax sheet, N14 of 'Week02' in A12 of 'SheetT', and each cell N14 of subsequent sheets in subsequent rows, you may use:
=INDIRECT(ADDRESS(ROW(Week_01.N$14));COLUMN(INDIRECT("n1")); ;1;"Week_"&TEXT(ROW(A11)-ROW(A$11)+1;"00")))
The "obvious simplifications" may work first, but will cause errors as soon as some rows and/or columns are inserted/deleted during ongounig maintenance/enhance,ents of your sheets. Even this monster formula is not truely fool-proof.

Is there a better way? Of course!
If your (supposed) weekly sheets contain entries by the thousands influencing the result in N14 you need to use a database solution. If there are (at most) a few thousand turnovers overall, and high-level security is not aimed at, you may use one single sheet for the primary entries, and select whatever you need in the overviev sheet with the help of standard functions.
To refer to a sequence of sheets of unknown (or arbitrary) names you would need a custom function.

(See also this demo.)

I suppose your 52 "ordinary" sheets are one per week. This is a design invented again and again. It also causes problems again and again as many requests for help in different forums show.
Since Calc does not offer a function providing access to a sheet based on its position (number), there is a sulution only if you can assure a simple systematic naming of the sheets. The solution will also be ugly as you will need a combination of ADDRESS and INDIRECT to access a single cell, and an additional call to OFFSET for accessing a range.

Suppose your sheets are named exactly in the way "Week_"&nn where nn is a two-digit-number in the range 01 through 52, and you want to reference cell N14 of sheet 'Week_01' in cell A11 of your tax sheet, N14 of 'Week02' 'Week_02' in A12 of 'SheetT', and each cell N14 of subsequent sheets in subsequent rows, you may use:
=INDIRECT(ADDRESS(ROW(Week_01.N$14));COLUMN(INDIRECT("n1")); ;1;"Week_"&TEXT(ROW(A11)-ROW(A$11)+1;"00")))
The "obvious simplifications" may work first, but will cause errors as soon as some rows and/or columns are inserted/deleted during ongounig maintenance/enhance,ents of your sheets. Even this monster formula is not truely fool-proof.

Is there a better way? Of course!
If your (supposed) weekly sheets contain entries by the thousands influencing the result in N14 you need to use a database solution. If there are (at most) a few thousand turnovers overall, and high-level security is not aimed at, you may use one single sheet for the primary entries, and select whatever you need in the overviev sheet with the help of standard functions.
To refer to a sequence of sheets of unknown (or arbitrary) names you would need a custom function.

(See also this demo.)

I suppose your 52 "ordinary" sheets are one per week. This is a design invented again and again. It also causes problems again and again as many requests for help in different forums show.
Since Calc does not offer a function providing access to a sheet based on its position (number), there is a sulution only if you can assure a simple systematic naming of the sheets. The solution will also be ugly as you will need a combination of ADDRESS and INDIRECT to access a single cell, and an additional call to OFFSET for accessing a range.

Suppose your sheets are named exactly in the way "Week_"&nn where nn is a two-digit-number in the range 01 through 52, and you want to reference cell N14 of sheet 'Week_01' in cell A11 of your tax sheet, N14 of 'Week_02' in A12 of 'SheetT', and each cell N14 of subsequent sheets in subsequent rows, you may use:
=INDIRECT(ADDRESS(ROW(Week_01.N$14));COLUMN(INDIRECT("n1")); ;1;"Week_"&TEXT(ROW(A11)-ROW(A$11)+1;"00")))
The "obvious simplifications" may work first, but will cause errors as soon as some rows and/or columns are inserted/deleted during ongounig maintenance/enhance,ents of your sheets. Even this monster formula is not truely fool-proof.

Is there a better way? Of course!
If your (supposed) weekly sheets contain entries by the thousands influencing the result in N14 you need to use a database solution. If there are (at most) a few thousand turnovers overall, and high-level security is not aimed at, you may use one single sheet for the primary entries, and select whatever you need in the overviev sheet with the help of standard functions.
To refer to a sequence of sheets of unknown (or arbitrary) names you would need a custom function.

Only if you do not need values from the sheets one per referencing cell, but only a sum or an average of the values from all the cells N14 of a contiguous sequence of sheets, you may change the design omitting the one-cell-at-a-time formulae, and use the SUM or AVERAGE function with a so called cuboid as parameter. (There are a few more functions accepting cuboids.)

(See also this demo.)

I suppose your 52 "ordinary" sheets are one per week. This is a design invented again and again. It also causes problems again and again as many requests for help in different forums show.
Since Calc does not offer a function providing access to a sheet based on its position (number), there is a sulution only if you can assure a simple systematic naming of the sheets. The solution will also be ugly as you will need a combination of ADDRESS and INDIRECT to access a single cell, and an additional call to OFFSET for accessing a range.

Suppose your sheets are named exactly in the way "Week_"&nn where nn is a two-digit-number in the range 01 through 52, and you want to reference cell N14 of sheet 'Week_01' in cell A11 of your tax sheet, N14 of 'Week_02' in A12 of 'SheetT', and each cell N14 of subsequent sheets in subsequent rows, you may use:
=INDIRECT(ADDRESS(ROW(Week_01.N$14));COLUMN(INDIRECT("n1")); ;1;"Week_"&TEXT(ROW(A11)-ROW(A$11)+1;"00")))
The "obvious simplifications" may work first, but will cause errors as soon as some rows and/or columns are inserted/deleted during ongounig maintenance/enhance,ents of your sheets. Even this monster formula is not truely fool-proof.

Is there a better way? Of course!
If your (supposed) weekly sheets contain entries by the thousands influencing the result in N14 you need to use a database solution. If there are (at most) a few thousand turnovers overall, and high-level security is not aimed at, you may use one single sheet for the primary entries, and select whatever you need in the overviev sheet with the help of standard functions.
To refer to a sequence of sheets of unknown (or arbitrary) names you would need a custom function.

Only if you do not need values from the sheets one per referencing cell, but only a sum or an average of the values from all the cells N14 of a contiguous sequence of sheets, you may change the design omitting the one-cell-at-a-time formulae, and use the SUM or AVERAGE function with a so called cuboid as parameter. (There are a few more functions accepting cuboids.)

(See also this demo.) without a "macro" or this one including the custom function mentioned abovge..)