References to sheets on formulas based on the relative order of those sheets to the current sheet

[Carlos Trigueiro]

Hi

I have a file where I add daily sheets with the activity data the day before.

I need to include formulas on each sheet with the comparison between the activity on that day and the activity 7 days before (which is on the previous 7th sheet, regardless of the name of that sheet). How can I introduce the sheet references in the formulas so that Excel can see that they are relative and not absolute references?

In other words, which allows me to obtain values that are on the 7th sheet prior to the current sheet, without having to manually update the formulas with the name of that previous 7th sheet.

I think it would have to be sheet references based on their relative order in relation to the current sheet, and not on the absolute order of sheets in the file.

[#2]

@Carlos Trigueiro 

I think this is probably a good opportunity to use the INDIRECT function to compile the name of that spreadsheet (assuming that there is a number reflecting the date, or the day of the week, or something like that).

Here's an on-line reference that can instruct you on how to use INDIRECT:

https://exceljet.net/excel-functions/excel-indirect-function

I make use of that in a different application where I construct sheet references based on stock symbols and secondary optional features.....it's a powerful and flexible tool.

All of that said, depending on how extensive the data are on each daily sheet, I'd also suggest you could achieve your comparisons even more easily by dispensing with daily sheets and just using a single data table with dates/times as distinguishing data on each row of the table. A dashboard sheet, for summarizing and viewing any given day could be created, with comparisons to the day before, the week before, the month before....    Excel works well to extract specific data from a single table; when we design our workbooks with distinct sheets--i.e., when we begin with separated data--we actually interfere with Excel's ability to help analyze and summarize.

[#3]

@mathetes 

Thank you for your reply.

I'm already trying to understand how does it works the Indirect function, which I've never used yet. Perhaps it will be the first time.

Each daily sheet has 1 or more tables, with more than 2500 cells each table, so it wouldn't be pratical to put all those tables in the same sheet..

I'll continue trying to find a good solution.

Thank you.

[#4]

@Carlos Trigueiro 

You wrote: Each daily sheet has 1 or more tables, with more than 2500 cells each table, so it wouldn't be pratical to put all those tables in the same sheet..

It was never my thought that you'd just put all those tables on one sheet. The idea was to re-think altogether how you collect the data. Right now you're trying to deal with a situation where you're having to connect so as to compare data from last Friday's sheet with this Friday's sheet. You're trying, in other words, to put together data that you've (arbitrarily) taken apart for some reason, perhaps, I'm going to guess, "That's how we've always done it."

I'm further going to assume that the table(s) on last Friday's sheet are arrayed the same as the table(s) on this Friday's sheet. [If they're not, the comparisons will require more than INDIRECT.]  Assuming that assumption is correct, were you to simply put all the rows of those tables together, just adding another column to represent the day/time that corresponds to the data.....you'd not have to put them together using INDIRECT at all... you'd just specify which dates data you wanted to compare. Excel is really good at taking a single database and developing summary analyses and comparisons of days, times, months, years, whatever.

It's not at all uncommon for people to take a process that began on paper back pre-computers, when, yes, it made sense to use a new sheet of paper for each new day. But replicating that process with the computer used mainly to speed up the calculations but otherwise work with the same procedural framework--separate sheet for each day--is failing to take advantage of Excel's abilities to work with a single database.

I don't know that this is an accurate account of how you got to where you are; but I am suggesting that it's probably not necessary (nor even, ultimately, efficient) to keep separate sheets for each day's data. What it does require is re-thinking how the data are collected and making sure it can be sliced and diced as desired. But let the computer do the slicing and dicing...don't slice it by days yourselves; you're just making the work harder.

All of that rant aside, if you insist on keeping the status quo, INDIRECT should be a big help.