Link formula from different sheets, 50 times

[AK83]

Hi all knowledgeable people.
I have a workbook. It has 52 regular worksheets.(SheetX)
I also have one whorksheet in the book that is a tax table(SheetT). I’ve found a VLOOKUP function to calculate a tax value in that sheet.
I’m (unsuccessfully) trying to link a value from SheetT back to SheetX.
I can manually enter the value to work the tax figure out, but this is open to error by user input. I want to try to automate it.

So, SheetX will have a value at cell N14. I need to link value that to SheetT.A5 .

At SheetT.B5 will be a tax value that I now want to link back to SheetX.N15 .

I can easily link this up for just the one sheet, but I have no idea how to do so for the other 51 worksheets, all at the same time.

ps. I’m also unsuccessful at simply linking the formula at SheetT.B5 directly into SheetX.N15 for some reason.

TIA,

Arthur.

[Lupp]

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…)

[AK83]

Thanks Lupp.

Each weekly sheet is set(and forget), no changes would be made.
Entries that affect the result at N14 are only 7(ie daily).

I’m a sub contractor, and simply keep track of my pays, which helps me settle issues when I don’t get paid correctly(common).

Some mates like using my spreadsheet, so I gave them copies too.
Basically, other than adding daily hours into the set columns, which then works out pays, no other alterations are made to those weekly sheets.

Never thought to reference each of the individual N14 cells into various cells in the tax lookup table. Will give that a go.

Thanks.

[Lupp]

I still don’t feel to understand the functionality (usefulness) of the weekly sheets, but I am a bit curious now. Would you mind to upload a sample document like those you are distributing to your mates? You can do this editing your question or your answer by using the paperclip tool.

[AK83]

Can’t find a paperclip tool.
But to answer the comment of usefulness of weekly sheets.
that’s how the start/end time for the spreadsheet is entered. It’s how to check if a mistake has been made, when a mistake has been made an edit can be effected.
Work entails working from week to week, so I work the spreadsheet on a weekly basis.
Each of the sheets are named for the last Sunday in every week, how my pay is formatted(ie. week ending 25/12.
Can’t add any more answers, nor edit my previous ones.