Excel 2007 : Getting data from one sheet to a dynamic sheet

Other people need to be able to access the spreadsheets. But thanks for the idea.

Thanks for the info; however, the data needs to be in the workbook in order to keep things organized and centralized (it's a work thing).

No worries; I appreciate it.

Sometimes we're just kind of limited in what we can do. If I had my way, I'd just write a program to do all of this stuff that I need done.

Had an idea.

Is it possible to change the reference of a range of cells within a formula?

For example (using the attached files above): if the columns reference a certain range on another sheet (in this case, one week) based on the date in the first column of the Data sheet, can I change the reference to a different range when the date changes?

So, with a date of 1/3/11, the range is 'Calendar!$A$10:$O$28'. When the date changes to 1/11/11, the range changes to 'Calendar!$A$30:$O$45'.

Is there a way to do this without going through every time and changing every reference by hand?

I think that's pretty close to what I want, although I don't really understand the 'OFFSET' function. Trying to figure out how it'll work when I change the date.

Thanks!

Eureka!

After messing around with several different things, finally found something that works.

@Marvin: thanks for the links as that helped me think about things differently with the 'OFFSET' function.

For those that might be interested, here's what I did:

Please Login or Register  to view this content.

I use the 'MATCH' to determine what row the date shows up on and then use 'OFFSET' to get the data from the calendar. The '+Calendar!Q11' is a number just to add to the 'MATCH' to increase the row count.

Ran it through several different tests and it works just fine.

Thanks again!

Oh, no problem; thought I'd let people know in case anyone ever ran into a similar situation.

I'd edit the thread title as 'Solved', but can't seem to find how to do it. Probably one of those things that's right in front of me. If anyone wants to mark it as solved, that would be cool.

Thanks!

Had to click 'Go Advanced' to find it.