1

I have been tasked to create a spreadsheet for weekly salary calculations of every company worker. I want to create a spreadsheet with one sheet for every week number of 2022 and name it accordingly. Since I'd have to create 52 sheets named "WN 01" to "WN52" I was looking for a shorter way to do this.

But what's even more time consuming is using data of all these sheets. What I want is a table like this:

A B C
= 'WN01' !$A$1 = 'WN01' !$B$1 = 'WN01' !$C$1
= 'WN02' !$A$1 = 'WN02' !$B$1 = 'WN02' !$C$1
= 'WN03' !$A$1 = 'WN03' !$B$1 = 'WN03' !$C$1
= 'WN04' !$A$1 = 'WN04' !$B$1 = 'WN04' !$C$1
... ... ...
= 'WN52' !$A$1 = 'WN52' !$B$1 = 'WN52' !$C$1

Is there a way to dynamically/automatically change the used sheet name? Is there a script or add-on I could use that basically adds +1 to the sheet name used in every row? I tried using a macro to do this, but that wasn't very successfull.

I hope you can help me with my problem!

Improve this question
2
  • There are "tricks" you could use to do this. However, I always find myself cringing inside when I read that people are planning to have a sheet per week and then try to compile 52 sheets into a master. I know that feels right. But it's backward (99.5% of the time). Rather, all data for all weeks should be normalized such that it can be entered in rows within one sheet only; and then (probably) one additional sheet can be used to select data for any given week or period and display/manipulate it into a report, and one additional sheet can be set up as a master for YTD (if needed).
    – Erik Tyler
    Dec 13, 2021 at 3:52
  • If you are set on getting A1:C52 filled as shown in your post, report back and I'll explain how to do it. It will take a few simple steps that I'd need to explain. If that is the case, please confirm that you want 52 rows by 3 columns of this pattern. If not, be specific about how many columns should follow this pattern.
    – Erik Tyler
    Dec 13, 2021 at 3:53

1 Answer 1

Reset to default
1

You want to report data from weekly sheets (names "WN01"-"WN52"). This answer takes your request at face value.

There are many possible solutions to this question. Please consider this as just one approach.

  • Use this formula in Cell A1: =indirect("wn0"&row()&"!$"&REGEXEXTRACT(ADDRESS(row(), Column()),"[A-Z]+")&"$"&column())
  • Copy the formula across as many columns (and down as many rows) as required.

Logic
The formula is constructed from several functional elements

  • indirect - returns the value from the string value of a cell reference.

So the following elements create a dynamic string value of the respective addresses

  • wn0 - the three character prefix of every sheet
  • row() - since the data begins in Cell A1, row() will return a value that creates a unique sheet name on every row.
  • &"!$"& - Adds a sheet suffix and the opening "$" sign for the absolute cell reference.
  • REGEXEXTRACT(ADDRESS(row(), Column()),"[A-Z]+") - returns the column letter for the current column (Convert column index into corresponding column letter on StackOverflow)
  • &"$"&- Adds the closing "$" sign for the absolute cell reference.
  • column() - This creates a value equal to the row number required in each column.

SAMPLE REPORT

report

WEEKS- WN01-WN03

Week1 Week2 Week3

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.