Forum Discussion
References to sheets on formulas based on the relative order of those sheets to the current sheet
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.
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.